Write in Excel

[Anu]

Hi,

Thanks for prompt reply.................

Actually i have 84459 data and also it is not fixed.
Depending on my query in the site it produces result.........
So please correct in my code itself because if i use my code then the QTP runs fast.

Regards,
Anu

[venkatbatchu]

Hi Anu,

No issue with what count ithas like 844559

It will keep on fetching the records from web table to data table.

Browser("xxxxxxxxxxxx').Page("xxxx").WebTable("xxxxxxxxx").GetRoproperty("rows") will fetch 844559 so you no need to worry how much count the only concern is if you more no of record then it will take more time to fetch all records to excel

And thats the reason i have given somme logic to click "Next" link to fetch all the data.


So first please try and let me know if you face any issues.

Regards,
Venkat.Batchu

[Anu]

Hi,

Your code is bit confising for me.
Does it write the data to excel?

Regards,
Anu

[venkatbatchu]

Ofcourse, Why do you worry about the code lets start implementing in this way.

let me know for further clarification.


Regards,
Venkat.Batchu

[Anu]

Hi,

Below is the modified code...........................

Code:

Dim xlSheet,xlBook, xlApp,a,CustomerName,Address,datatable
Const ForAppending = 8
const TristateTrue=-1
file_location = "C:\Test.xlsx"
'Dim iCol : iCol = 0

'----------'For Wirting in  Excel Sheet'

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("C:\Test.xlsx")
Set xlSheet = xlBook.WorkSheets("Sheet1")
'To add the column names "Name","Address" for the data table.
Datatable.AddParameter("CustonerName"),Global Sheet)
Datatable.AddParameter("Address"),Global Sheet)
'To fetch the row count from the web table
intrcount=Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").GetRoProperty("eows")
For temp=1 to intrcount-1 step 1
Datatable.setcurrentrow(temp)
strcustname=Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").GetCellData(temp,"Name")
straddress=Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").GetCellData(temp,"Address")
strcustname=Datatable.Value("Name")
straddress=Datatable.Value("Address")
'To click the Next page
If ((temp%10)=0) Then
Browser("Browser").Page("Page").WebTable("DataTable").WebTable("Table").Link("Next").Click
Browser("Browser").Page("Page").Sync
Wait 2
End If
Next
Wait 3
Datatable.Export "C:\Test.xlsx"
xlApp.DisplayAlerts = False
xlBook.Save
'xlBook.Close
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

But it is throwing a syntax error while executing this "Datatable.AddParameter("CustonerName"),Global Sheet)"

Regards,
Anu

[venkatbatchu]

Hi Anu,

You dont need to create any objects like xlApp, xlBook,xlSheet.

Code:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("C:\Test.xlsx")
Set xlSheet = xlBook.WorkSheets("Sheet1")

Just delete all the objects related.


Just execute it a i provided and debug this.


Please let me know for further clarification.

Regards,
Venkat.Batchu

[Anu]

Hi,

As per your previous reply, i had removed the excel object but still sytax error is coming..............
Please find the attached screen shot.

[venkatbatchu]

Hi,
Use the syntax as like below

Code:

Datatable.AddParameter("CustomerName","")
Datatable.AddParametr("Address","")

and observe whether these columns added to Datatable or not...
Then it will work.

Regards,
Venkat.Batchu

[Anu]

Hi,

I have done the modification as per your previous mail but still it is giving error.

Could you please tell me in which datatable the data should be added?
Quite Confusing

[venkatbatchu]

Hi Anu,

You are facing small errors as you were posting i.e it was telling remove the paranthesis.You should have try this befre posting.

Code:

Datatable.AddParameter "Name",""
Datatable.AddParameter "Address",""

With this you can see the two coloumns of "Name" and "Address" in Global Sheet.

Please try and let me know if you need any clarification on this.


Regards,
Venkat.Batchu