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