Micro Focus QTP (UFT) Forums
Write in Excel - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Others (https://www.learnqtp.com/forums/Forum-UFT-QTP-Others)
+--- Thread: Write in Excel (/Thread-Write-in-Excel)

Pages: 1 2 3 4 5

Write in Excel - Anu - 05-24-2010

Hi,

I have a query regarding writing data in Excel.

I was able to write the data in excel but when i navigate to other web page then it over writes the previous data.

Please tell me the way so that i can write the data in excel without overwriting the previous data.

Hope it explains my problem...........

Regards,
Anu

RE: Write in Excel - Saket - 05-24-2010

Hi Anu,
would you like to elaborate the query, exactly what you need to write into excel and paste your code whatever you have tried so far.
It will help us to understand better.

RE: Write in Excel - basanth27 - 05-24-2010

Two ways to handle this,

1. Either use newer rows while navigating to the next webpage
2. Use a different sheet.

Whichever should suit your need...

RE: Write in Excel - Anu - 05-25-2010

Hi Saket,

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

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

'———-’For Wirting in Excel Sheet’
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.WorkBooks.Open("F:\Test.xls")
Set xlSheet = xlBook.WorkSheets("Sheet1")

CountOfRows=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").RowCount

iCol = iCol + 1

For i = 1 To CountOfRows
    CustomerName=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,3)
    Address=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,4)
    xlSheet.Rows(i).Columns(iCol).Value =CustomerName
    xlSheet.Rows(i).Columns(iCol + 1).Value =Address
    iNewcolumn=xlSheet.UsedRange.Columns.Count + 1
Next
'xlBook.Save
'xlBook.Close
'xlApp.Quit

Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("LinkTable").Link("2").Click

Here i have parameterized the Link(“2″) that is in the object repository.
When i run the script then it automatically goes to the next link and write data to excel sheet by overwritting the previous data.
My problem is that-”I want data should not overwrite instead it should get append same as happen if we write data in txt”

Hope this explains my problem…………………

Regards,
Anu
------------------------------------------------------------------------

RE: Write in Excel - Saket - 05-25-2010

You should use usedrange to append the data in your xls
1. identify the used range in the sheet
2. start adding value from the very next row.

it woult be like below
Code:
nRow = xlSheet.UsedRange
For i = 1 To CountOfRows
CustomerName=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,3)
Address=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,4)
nRow = nRow + 1
xlSheet.Rows(nRow).Columns(iCol).Value =CustomerName
xlSheet.Rows(nRow).Columns(iCol + 1).Value =Address
iNewcolumn=xlSheet.UsedRange.Columns.Count + 1

Next
hope this helps
btw, what exactly you do with the this statement
Code:
iNewcolumn=xlSheet.UsedRange.Columns.Count + 1

do you execute this everytime a new link clicked?

RE: Write in Excel - Anu - 05-25-2010

Hi,

Some one has told me to use "iNewcolumn=xlSheet.UsedRange.Columns.Count + 1"
this for adding the data in the new cloumn of same excel but it does not work from my side.

Thanks For urs prompt reply................Smile
Regards,
Anu
Hi Saket,

I have tried the same code given by you..........
It is not adding the value to the next row.
The code given by you is below:

Code:
nRow = xlSheet.UsedRange
For i = 1 To CountOfRows
CustomerName=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,3)
Address=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,4)
nRow = nRow + 1
xlSheet.Rows(nRow).Columns(iCol).Value =CustomerName
xlSheet.Rows(nRow).Columns(iCol + 1).Value =Address
iNewcolumn=xlSheet.UsedRange.Columns.Count + 1

Next

Waiting for response.........
Regards,
Anu
Hi Saket,

Your question : Do you execute this everytime a new link clicked?

Answer: Please elaborate it.........

Regards,
Anu

RE: Write in Excel - Saket - 05-25-2010

I am so sorry, surprised how I missed this Smile , here is the corrected code
Code:
nRow = xlSheet.UsedRange.Rows.Count
For i = 1 To CountOfRows
CustomerName=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,3)
Address=Browser("Meghalaya Directory Search").Page("Meghalaya Directory Search_3").WebTable("DataTable").WebTable("Table").GetCellData(i,4)
nRow = nRow + 1
xlSheet.Rows(nRow).Columns(iCol).Value =CustomerName
xlSheet.Rows(nRow).Columns(iCol + 1).Value =Address
iNewcolumn=xlSheet.UsedRange.Columns.Count + 1
Next
xlBook.Save
Make sure you put statement for saving the workbook

RE: Write in Excel - Anu - 05-25-2010

Hi Saket,

Thnks

Regards,
Anu
Hi Saket,

This code is working but at the same time it is showing the error which is attached herewith.

Please tell me how to handle this error

Regards,
Anu

RE: Write in Excel - Anu - 05-25-2010

Hi Saket,

Could you please look into this query on urgent basis.
Hope you understand.
Thanks in advanceSmile

Regards,
Anu

RE: Write in Excel - Saket - 05-25-2010

check if your xls is checked read only. make it read/write, you will be able to use it.