I'm having some trouble using the ReadExcel2 udf and I'd appreciate any help. All I'm trying to do with this file is to get a rowcount. I'm probably doing this all wrong? I've read over the UDF usage documentation, but I'm still a little lost. Thanks.
This is the error I'm getting:
ERROR : Error in expression: this type of array not supported in expressions.!
Script: C:\Kix\excelkix.kix
Line : 92
This is how I'm trying to use the UDF:
Code:
;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
