Write in Excel

[Anu]

Hi Saket,

I have removed the read only preoperty even though it is creating problem.
On navigating to the next link it shows the below error.
Please help me
M eargly waiting for your reply.............

Regards,
Anu

[Saket]

add property 'DisplayAlerts' as false for excel

---

Code:

xlApp.DisplayAlerts = False xlBook.Save

[Anu]

Hi Saket,

I had tried the code given by you but it is not working in my code.
Same above two errors are coming whose screen shot i attached previously in my reply.

Regards,
Anu

[Saket]

did you try using 'saveas' for 'save'? does it also not work?

---

I just tried it with both the options, it works perfect. It should work at your side as well. I am out of options now, except one thing that I suspect is you are running this script everytime the new link clicked bcoz I dont see any kind of loop there to go through all the links and fetch data. In that case if you dont quit from excel you may receive the read only error. let me know how it is there.

[Anu]

Hi Saket,

On using savesas, it is also showin the error message.

Yes u r absolutely true. I am running the script evertime the new link is clicked.

Please find the screen shot of both the scenarios mentioned here.
First for 'saveas'.
Second 'my web page on which i am using QTP'

Regards,
Anu

[Saket]

quit from excel on every run
xlApp.quit
and set Nothing for all object created

[Anu]

Hi Saket,

Now it is working

Thanks a lot for yours co-operation.

Regards,
Anu

[Saket]

Ah Finally , great to know that its working.

[Anu]

Hi Saket,

Again i am facing problem in writting data to excel.The code is mentioned below :

Code:

Dim xlSheet,xlBook, xlApp,a,CustomerName,Address,datatable Const ForAppending = 8 const TristateTrue=-1 file_location = "C:\Test.xlsx" Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.WorkBooks.Open("C:\Test.xlsx") Set xlSheet = xlBook.WorkSheets("Sheet1") Set oLinks = Description.Create oLinks("micclass").Value = "Link" oLinks("html tag").Value = "A" Set AllLinks = Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").ChildObjects(oLinks) nLinks =AllLinks.count For i = 1 to nLinks CountOfRows=Browser("Browser").Page("Page").WebTable("DataTable").RowCount-1 nRow = xlSheet.UsedRange.Rows.Count For j = 1 To CountOfRows + 1 CustomerName=Browser("Browser").Page("Page").WebTable("DataTable").GetCellData(i,2) Address=Browser("Browser").Page("Page").WebTable("DataTable").GetCellData(i,3) nRow = nRow + 1 xlSheet.Rows(nRow).Columns(1).Value =CustomerName xlSheet.Rows(nRow).Columns(2).Value =Address Next oLinks("text").Value = i Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").Link(oLinks).Click Next xlApp.DisplayAlerts = False xlBook.Save 'xlBook.Close xlApp.Quit Set xlApp = Nothing Set xlBook = Nothing Set xlSheet = Nothing

Will be thankful for yours valueable respose...............

Regards,
Anu

[venkatbatchu]

Hi,

Instead of using Excel obects you could go with data tables.
Lets assume that 1000 records are available on web page.

'if all the rows are displaying in sngle page (all the records) (Ex: 1000 records in single page)

Code:

'To add the column names "Name","Address" for the data table. Datatable.AddParameter("Name","") Datatable.AddParameter("Address","") 'To fetch the row count from the web table intrcount=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetRoProperty("eows") For temp=1 to intrcount-1 step 1 Datatable.setcurrentrow(temp) strcustname=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetCellData(temp,"Name") straddress=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetCellData(temp,"Address") strcustname=Datatable.Value("Name") straddress=Datatable.Value("Address") Next Wait 3 Datatable.Export "File path"

If 1000 records are displaying in 50 pages i.e 20 records in one page (20 *50)

Code:

'To add the column names "Name","Address" for the data table. Datatable.AddParameter("Name","") Datatable.AddParameter("Address","") 'To fetch the row count from the web table intrcount=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetRoProperty("eows") For temp=1 to intrcount-1 step 1 Datatable.setcurrentrow(temp) strcustname=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetCellData(temp,"Name") straddress=Browser("xxxxx").Page("xxx").WebTable("xxxxx").GetCellData(temp,"Address") strcustname=Datatable.Value("Name") straddress=Datatable.Value("Address") 'To click the Next page If ((temp%20)=0) Then Browser("xxxxxxxx").Page("xxxxxxxx").Weblink("Next").Click Browser("xxxxxxxxxxx").Page("xxxxxxxxxxxx").Sync Wait 2 End If Next Wait 3 Datatable.Export "File path"

With the above code we could export the data which is available in Web table simply we can say data export from web table to data table and finally exporting to required path.

Please let me know for further clarification.

Thanks and Reagrds,
Venkat.Batchu