Through UFT I am trying to read rows from an Excel sheet (can be any number of rows based on the user input). I want to pass these values (are string values) to another function.
The below code which gives subscript out of range error at line 'fieldvalueChar(j-1) = ws.cells(j,1)'
Dim value
Dim lRow
Dim fieldvalue
Dim fieldvalueChar()
'Open the spreadsheet document for read-only access.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("Path\Input.xlsx")
Set ws = objWorkbook.Sheets("Sheet1")
rowcount = ws.usedrange.rows.count
for j = 1 to rowcount
fieldvalueChar(j-1) = ws.cells(j,1)
next
MsgBox(fieldvalueChar(0))
MsgBox(fieldvalueChar(1))
The Excel sheet will always have one column and dynamically changing number of rows based on the user input. I saw some VBA codes online but no VBS.
It's because you did not initialize the array. You can try something like this
Dim value
Dim lRow
Dim fieldvalue
ReDim fieldvalueChar(1) ' Just Declare an array of 1, Since array size has to be constant when declaring
'Open the spreadsheet document for read-only access.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\saravananp\Desktop\Items.xls")
Set ws = objWorkbook.Sheets("Items")
rowcount = ws.usedrange.rows.count
' Redefine array size dynamically based on number of Rows
ReDim fieldvalueChar(rowcount)
for j = 1 to rowcount
fieldvalueChar(j-1) = ws.cells(j,1)
next
MsgBox(fieldvalueChar(0))
MsgBox(fieldvalueChar(1))
In the other hand you could try datatables as well.