Read and store the values from Excel rows to variables/arrays using VBscripting in UFT

Question

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.

Answer 1

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.