Cannot save webtable contents to excel file in qtp

Question

My code fails to save data to an existing excel file. I can see from local window that it is copying data from a webtable to an excel sheet but can't save the details.Could you please correct if I am missing anything wrong here

   path = "D:\Demo\TestData\Shopping_Cart.xls"

set xl= CreateObject("excel.application")
xl.workbooks.open(path)
set nsheet=xl.sheets.item(1)

Set BrwsrCheckOut= Browser("name:=Checkout","title:=Checkout - Internet Explorer").page("title:=Checkout","name:=.*")
Set DesPrdChcKOut = Description.Create
DesPrdChcKOut("html tag").value = "TABLE"
DesPrdChcKOut("column names").value = "Product Name;Model;Quantity;Price;Total"

For IteratorRow = 1 To 2

'BrwsrCheckOut.WebTable(DesPrdChcKOut).RowCount Step 1
        For IteratorCol = 1 To 3

        'BrwsrCheckOut.WebTable(DesPrdChcKOut).ColumnCount(1) Step 1

    val = BrwsrCheckOut.WebTable(DesPrdChcKOut).GetCellData(IteratorRow, IteratorCol)

    Next

Next
'xl.Activeworkbook.saveAs "D:\Demo\TestData\Shopping_Cart.xls"
xl.Activeworkbook.save
nsheet.SaveAs("D:\Demo\TestData\Shopping_Cart.xls")
xl.ActiveWorkbook.Close



Set xl = nothing
Set nsheet = nothing 

Answer 1

I think it is because you are using the ActiveWorkbook method. When you use active workbook method, then gotta be very careful that no other workbook is opened / active state.

To avoid all these, the following may be a better approach.

Example

 path = "D:\Demo\TestData\Shopping_Cart.xls"
 Set oxl = CreateObject("Excel.Application")
 Set wbk = oxl.workbooks.open(path)
 sheetname = "Output" ' You can also refer the sheet by index
 Set sheet = wbk.sheets(sheetname)

 'Logic to retreive the data from webtable
 for i=1 to number_of_rows
   ' update the data in excel
 next

 'Save the workbook
 wbk.save
 wbk.close
 oxl.quit

Let me know if it works.