Home > FAQ > Using Files, Databases, Excel worksheets

Using Files, Databases, Excel worksheets

Can I read data from a file/write data to a file in a script?

Yes, various formats for external files are supported. 

 

Can I read a column in Excel without knowing the number of cells?

ReadExcel function needs to know the maximum number of rows which can be available in the column. But after ReadExcel, you can use a loop to process the cells until the first one which is empty. The code is: 

'Declare an array with a dimension the maximum numbers of rows, in this example 1000
Dim column$(1000)
'Read the A column of the Excel file called data.xlsx located in the same folder as the script
ReadExcel("data.xlsx","A1:A1000",column$())
'Loop until the cell is empty - An array starts at index 0, so column$(0) contains the A1 cell
i=0
repeat
i=i+1
until column$(i)=""

number_of_non_empty_cells=i

Can I populate an Excel sheet line by line?

Yes, you can use a loop which iterates the ReadExcel function line by line. The code is:

'Declare an array with a dimension the maximum numbers of rows, in this example 1000
'Declare the array which contains the data to write to one row in the Excel file, in this example 5 cells from A column to E column
Dim line$(5)
'The array is populated at each iteration for example from a web data capture process
'Write to Excel in row A1:E1, then A2:E2, and so on
'It needs to concatenate the index i to a string, this is made by str$(i) to convert i integer to a string'and then the concatenation is done by the + operator, so "A"+str$(i)+":E"+str$(i)
'Excel starts at row 1, change to which number to start
i=1
repeat
'Populate line$. In this example, it's just an assignment and so the Exel file will be filled by identical lines
line$(0)="cell1"
line$(1)="cell2"
line$(2)="cell3"
line$(3)="cell4"
line$(4)="cell5"
WriteExcel("data.xlsx","A"+str$(i)+":E"+str$(i),line$())
i=i+1
until i=20
'Change the 20 to your number of rows to write to Excel