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

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

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.

Solution

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.