excelvbscripthp-uft

UFT - Exporting datatable content to specific excel rows


I need some help on getting the data to be imported to the correct row in excel

I have a table in excel that has content in the first 5 columns on the first 3 rows, like this: enter image description here

I need to get the data from the UFT datatable into the excel on the 2nd, 3rd rows but in the F, G column but when i run the script as is, the data is put into the 4th and 5th column(Like in the image), not the 2nd and 3rd like i need them to be

I have this function but am not sure how to fix it:

Public Function SetExcelCellData()
   Set objExcel = CreateObject("Excel.Application")
   objExcel.DisplayAlerts = False
   Set objWorkbook = objExcel.Workbooks.Open("C:\Users\WrightJ\Desktop\Excel\CompFormTest.xlsx")
   Set objSheet  = objWorkbook.Worksheets("Sheet1")
   Thank_you = Datatable("Thank you text",dtGlobalSheet)
   Prize = Datatable("Prize draw",dtGlobalSheet)
   rows = objSheet.UsedRange.Rows.count
   objSheet.Cells(rows+1,6).Value = Thank_you
   objSheet.Cells(rows+1,7).Value = Prize
   objWorkbook.SaveAs "C:\Users\WrightJ\Desktop\Excel\CompFormTest.xlsx"
   objWorkbook.Close
   Set objSheet  = Nothing
   Set objWorkbook = Nothing
   Set objExcel = Nothing
End Function

Any help would be great!!!


Solution

  • UsedRange returns a squared range of used cells, thus returning A1:G3 in your case.

    So when you call this line rows = objSheet.UsedRange.Rows.count, the variable rows takes 3 as value.

    To fix your code, delete the above line and replace following lines by:

    objSheet.Cells(2,6).Value = Thank_you
    objSheet.Cells(2,7).Value = Prize