;Function: 

; ReadExcel2() 

; 

;Authors: 

; Lonkero 

; kdyer 

; 

;Version: 

; 2.2 

; 

;Version History: 

; 2.2 11.2.2004 

; fixed lot of typos and the header 

; 2.1 16.5.2003 

; added ability to read full sheet (slow) 

; added option to read all data (faster ) 

; 2.0 16.5.2003 

; cleaned the code from console outputs 

; added proper errorcodes 

; added proper parameter-definitions 

; proper closing of excel 

; well, totally made this a new Xperience 

; 

; 1.4 (initial code = ReadExcel UDF) by kdyer 

; http://www.kixtart.org/board/ultimatebb.php?ubb=get_topic;f=12;t=000121 

; 

; 

;Action: 

; Read from Excel to 2-dimensional array 

; 

;Syntax: 

; ReadExcel2(FILE, [SHEET], [ROWCOUNT], [COLUMNCOUNT], [ROWSTART], [COLUMNSTART]) 

; 

;Parameters: 

; FILE 

; REQUIRED STRING 

; File to read from 

; SHEET 

; OPTIONAL INTEGER 

; Sheet index from which to read. 

; default = 1 

; ROWCOUNT 

; OPTIONAL INTEGER 

; Amount of Rows to read 

; if -1 reads until empty field found at the start of row 

; if left out reads all rows 

; COLUMNCOUNT 

; OPTIONAL INTEGER 

; Amount of Columns to read 

; if -1 reads until empty field found at row 1 of Column 

; if left out reads all Columns 

; ROWSTART 

; OPTIONAL INTEGER 

; Row from which to start reading 

; if not specified, defaults to 1 

; COLUMNSTART 

; OPTIONAL INTEGER 

; Column from which to start reading 

; if not specified, defaults to 1 

; 

;Remarks: 

; Excel uses row and column numbers starting from 1 

; thus the resulting range that starts from [0,0] might confuse a little 

; 

;Returns: 

; 2-dimensional array (table) or 

; nothing if failed (see errorcode for reason) 

; 

;Errorcodes: 

; 0 ERROR_SUCCESS The operation was successfully completed. 

; 2 ERROR_FILE_NOT_FOUND The system cannot find the file specified. 

; 87 ERROR_INVALID_PARAMETER The parameter is incorrect. 

; 1154 ERROR_INVALID_DLL Excel does not exist or is too old to support com. 

; 

;Dependencies: 

; Excel -97 or newer 

; 

;Example: 

; ;read the default sheet (1) to the first empty field (with 3 columns). 





 $range=readexcel2('C:\kix\06292004.XLS',,-1,1) 

 ReadExcel2($range)

 

Function ReadExcel2($0,optional $1,optional $2,optional $3,optional $4,optional $5)

Dim $,$! 

If 0=Exist($0) Exit 2 EndIf 

$=CreateObject('Excel.application') 

If @error Exit 1154 EndIf 

If 8<>VarType($0) Exit 87 EndIf 

$.workbooks.Open($0). 

If VarType($1) 

If 3<>VarType($1) Exit 87 EndIf 

If 1>$1 Exit 87 EndIf 

Else 

$1=1 

EndIf 

If VarType($2) 

If 3<>VarType($2) Exit 87 EndIf 

If -1=$2 

For $!=1 to $.sheets($1).rows.count 

If NOT $.sheets($1).cells($!,1).value 

$2=$!-1 

$!=$.sheets($1).rows.count 

EndIf 

Next 

EndIf 

Else 

$2=$.sheets($1).rows.count 

EndIf 

If VarType($3) 

If 3<>VarType($3) Exit 87 EndIf 

If -1=$3 

For $!=1 to $.sheets($1).columns.count 

If NOT $.sheets($1).cells(1,$!).value 

$3=$!-1 

$!=$.sheets($1).columns.count 

EndIf 

Next 

EndIf 

Else 

$3=$.sheets($1).columns.count 

EndIf 

If VarType($4) 

If 3<>VarType($4) Exit 87 EndIf 

If 1>$4 Exit 87 EndIf 

Else 

$4=1 

EndIf 

If VarType($5) 

If 3<>VarType($5) Exit 87 EndIf 

If 1>$5 Exit 87 EndIf 

Else 

$5=1 

EndIf 

Dim $_[$2-1,$3-1] 

$ReadExcel2=$_ 

For $!=1 to $2-$4+1 

For $_=1 to $3-$5+1 

$ReadExcel2[$!-1,$_-1]=$.sheets($1).cells($!,$_).value 

Next 

Next 

$.workbooks.Close. 

$.Quit 

EndFunction 

 $range=readexcel2('C:\kix\06292004.XLS',,-1,1) 
 ReadExcel2($range)