#166791 - 2006-09-04 02:19 PM
ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
I am getting an error in expression in UDF ReadExcel2. The error is in the following line: $ReadExcel2[$!-1,$_-1]=$.sheets($1).cells($!,$_).value
I created an excel file in c:\script\test.xls so the command is: ReadExcel2('C:\script\test.xls',,,)
I must be missing something, but I can't get this function to work properly....
|
Top
|
|
|
|
#166793 - 2006-09-04 02:26 PM
Re: ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
Kixtart version 4.52, I tried version 4.50 as well....same result
Edited by HofKicKs (2006-09-04 02:29 PM)
|
Top
|
|
|
|
#166795 - 2006-09-04 03:42 PM
Re: ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
Computer is a WinXP, Office version is also XP (2002)
I didn't modify the UDF.. The expression is really simple. It's the same as the example used in the UDF..
Here's the code: Code:
;Function: ; ReadExcel2() ; ;Authors: ; Lonkero ; kdyer ; ;Version: ; 2.2.1 ; ;Version History: ; 2.2.1 13.9.2005 ; removed a code shortcut that does no longer work in 4.50 ; 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:\Documents and Settings\niemjo\Työpöytä\puhluett.xls',,-1,3) ; if @error ; "error occured: @serror (@error)" ; else ; for $counter=0 to ubound($range,2) ; ? "name: " $range[$counter,0] ; ? "phonenumber: " $range[0,1] ; ? "cellular: " $range[0,2] ; ? ; next ; ;Source: 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:\script\test.xls",,,) if @error "error occured: @serror (@error)" else for $counter=0 to ubound($range,2) ? "result: " $range[$counter,0] next
endif
{Allen: added code tags}
Edited by Allen (2006-09-04 04:08 PM)
|
Top
|
|
|
|
#166796 - 2006-09-04 03:58 PM
Re: ReadExcel2 error in expression
|
Allen
KiX Supporter
Registered: 2003-04-19
Posts: 4545
Loc: USA
|
Code:
$range=ReadExcel2("C:\script\test.xls",,,) Would it help to get rid of the extra commas? Personally, I've never tried adding them when they didn't need to be there so this is just a guess...
|
Top
|
|
|
|
#166797 - 2006-09-04 04:04 PM
Re: ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
I tried that already, and it doesn't make any difference... Same error...
|
Top
|
|
|
|
#166798 - 2006-09-04 05:00 PM
Re: ReadExcel2 error in expression
|
Lonkero
KiX Master Guru
Registered: 2001-06-05
Posts: 22346
Loc: OK
|
k, change the inner loop: Code:
for $_=1 to $3-$5+1 $ReadExcel2[$!-1,$_-1]=$.sheets($1).cells($!,$_).value next
to: Code:
for $_=1 to $3-$5+1 $!-1 " " $_-1 " (" $2-1 " " $3-1 ")" ? $ReadExcel2[$!-1,$_-1]=$.sheets($1).cells($!,$_).value next
what is the last line you get before error?
|
Top
|
|
|
|
#166799 - 2006-09-05 08:52 AM
Re: ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
Mmm....the substitution of the code did not work...I still get the same error.
The last line before the error is in red: for $!=1 to $2-$4+1 for $_=1 to $3-$5+1 $ReadExcel2[$!-1,$_-1]=$.sheets($1).cells($!,$_).value
Any other suggestions?
Edited by HofKicKs (2006-09-05 08:54 AM)
|
Top
|
|
|
|
#166800 - 2006-09-05 02:17 PM
Re: ReadExcel2 error in expression
|
HofKicKs
Fresh Scripter
Registered: 2005-10-06
Posts: 20
|
I created a new script which does the trick, so I don't need the UDF ReadExcel2 anymore. Thanks for all the help...
The new code is: $oXL = CreateObject("EXCEL.application") $oXL.visible = 0 $file = "abc.xls" $sheet = "sheet1"
$Rc = $oXl.Workbooks.Open(@SCRIPTDIR + "\" + $file) $Rc = $oXL.Worksheets("$sheet").Activate
;$Row = Row to start reading from ;$Column = Column to read
$Row=1 $Column=1 WHILE $oXL.Worksheets("$sheet").cells($Row,$Column).value<>'' ;need a value each time in column 1 IF $oXL.cells($Row,$Column).value='' ;Once it gets to a blank row... $oXL.Quit ;quit Excel $oXL=0 ;set the object to 0 Else $Value = $oXL.cells($Row,$Column).value ? "Value : " + $Value ; The following command writes data into a specified cel ---SET oXL.visible to 1--- to see the modification ; $oXL.cells(4,1).value = "Test"
EndIf $Row=$Row+1 Loop $oXL.Quit ;quit Excel $oXL=0 ;set the object to 0
Edited by HofKicKs (2006-09-05 02:18 PM)
|
Top
|
|
|
|
#173550 - 2007-02-02 05:21 PM
Re: ReadExcel2 error in expression
[Re: Lonkero]
|
therier
Lurker
Registered: 2007-02-02
Posts: 1
|
Hello,
I have the same problem...
The line outputed is :
0 0 (65535 255)
Do you have any idea?
Thanks!
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 248 anonymous users online.
|
|
|