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