Write in Excel

[Anu]

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

[Saket]

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.

[basanth27]

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...

[Anu]

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
------------------------------------------------------------------------

[Saket]

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?

[Anu]

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................
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

[Saket]

I am so sorry, surprised how I missed this , 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

[Anu]

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

[Anu]

Hi Saket,

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

Regards,
Anu

[Saket]

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