Home > Language Reference > Excel Functions > ReadExcel

ReadExcel

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

Used to read data from 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 website those three data for capturing phone numbers.

Video: How to use ReadExcel function to read a whole column or line of an Excel file, populate an array and type the read data into a web form.

Syntax

ret = ReadExcel(<Excel_filename>,<range_identifier>,<tab_result> [<password>])

Parameters

<Excel_filename>, string, Excel filename to read. If the path is not specified in <Excel_filename>, the file is searched in the current working folder.

<range_identifier>, string. It follows the syntax:
letter for the colum concatenated to the row number, then a : character, then letter for the column concatenated to the row number. Examples:
A4:F4 for row 4, columns A to F
A2:A12 for column A, rows 2 to 12.
If multiple sheets exist in the workbook, the required syntax is "sheet_name!range_of_cells" ; for example:
TOOLS!A9:F9
Other range examples: A1, A3:B3, tools!$b$2:$b$4

<tab_result>, array of strings, variable only. The contents of the cells specified in <range_identifier> are placed into this array, starting at index 0. The array must be declared at the beginning of the script using Dim  statement.

<password>, optional string, read password if <Excel_filename> is password protected.

Return value

ret, optional numeric return value. If the function succeeds, the return value is the number of read cells. If the function fails, the return value is a nonzero value: -1 if an error is reported by Excel or the range is not a single cell, a column or a row ; -2 if the array is smaller than the number of cells read (see File Functions Error Codes).

See also

CloseExcelCom
CreateExcelFile
ExecExcelMacro
WriteExcel

Examples

dim array$(20)
ret=ReadExcel("C:\wttest\\myexcel_file.xls","TOOLS!A9:F9",array$())
msgbox(ret,,"number of cells read")



'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:\wttest\example1.xlsx"
filetowritein$="c:\wttest\test.xlsx"

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)=""