ReadExcel
File management function.
The ReadExcel function reads a cell, a row or a column from an Excel workbook. Microsoft Excel must be installed on the PC, but does not have to be open. The workbook itself can be already open or not in Excel at the time of the ReadExcel.
Usage
You read data from an external souce, an Excel file. For example, you populate 3 arrays reading three columns in an Excel file, first one is First name, second is Last name, third is Town, and in a loop, you submit to a yellow pages Web site those three data for capturing phone numbers.
Syntax
ReadExcel(<workbook>,<range_descriptor>,<array_result$>[,<readPasswd$>])
or
ret=ReadExcel(<workbook>,<range_descriptor>,<array_result$>[,<readPasswd$>])Parameters
<workbook>, string, name of the Excel workbook to read from. It is a string which can be a constant or a variable. Long names and UNC names are accepted, such as \\Server\c\my_directory\my_file.xls. If the path is not specified, the file is searched in the current directory.
<range_descriptor>, string. If only one sheet exists in the workbook, only the range of cells must be specified, such as A4:F4 for a row or A2:A12 for a column. If multiple sheets exist in the workbook, the required syntax is "sheet_name!range_of_cells" ; for example, TOOLS!A9:F9
Other range examples: mycell1 (a named cell), A1, A3:B3, tools!$b$2:$b$4
<array_result$>, array of strings. The array must be declared at the beginning of the script using Dim. The contents of the cells specified in <range_descriptor> is placed into this array, beginning with element 0.
<readPasswd$>, optional string parameter; password for reading the <workbook> if needed.Return value
Ret, numeric return code. When the read is successful, the function returns the number of cells read, otherwise use this return code for Error Handling. The return code is -1 if an error is reported by Excel or the range is not a single cell, a column or a row, the return code is -2 if the array is smaller than the number of cells read.
See also
How to read all the lines of an Excel worksheet until the cell in the first column is empty
Capture data from a web site and write them in an Excel file
Example
dim array$(20)
ret=ReadExcel("C:\My documents\myexcel_file.xls","TOOLS!A9:F9",array$())
msgbox(ret,,"number of cells read")
Example code
The script below reads all the lines from an Excel file from A column to C column and writes them in another Excel file.
dim arrayline$(10)
fileexcel$="c:\example1.xls"
filetowritein$="c:\test.xls"
j=1
repeat
' The complex string "sheet1!"+"a"+str$(j)+":c"+str$(j) means if j=1
' "Sheet1!a1:c1"readexcel(fileexcel$,"sheet1!"+"a"+str$(j)+":c"+str$(j),arrayline$())
WriteExcel(filetowritein$,"sheet1!"+"a"+str$(j)+":c"+str$(j),arrayline$())
j=j+1
until arrayline$(0)=""