Page 1 of 1 1
Topic Options
#166791 - 2006-09-04 02:19 PM ReadExcel2 error in expression
HofKicKs Offline
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
#166792 - 2006-09-04 02:24 PM Re: ReadExcel2 error in expression
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
what version of kixtart you are using?
Top
#166793 - 2006-09-04 02:26 PM Re: ReadExcel2 error in expression
HofKicKs Offline
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
#166794 - 2006-09-04 03:33 PM Re: ReadExcel2 error in expression
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
may I see your whole script (at least the part related to this)?
did you modify the udf itself in any way?
which version of windows and office does that computer have?

Top
#166795 - 2006-09-04 03:42 PM Re: ReadExcel2 error in expression
HofKicKs Offline
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 Administrator Offline
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 Offline
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 Administrator Offline
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 Offline
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 Offline
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
#166801 - 2006-09-05 04:39 PM Re: ReadExcel2 error in expression
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
I asked you to give me the last line outputted by the script before the error.
I know what is the last line before the error in the code.

Top
#173550 - 2007-02-02 05:21 PM Re: ReadExcel2 error in expression [Re: Lonkero]
therier Offline
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
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 248 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.056 seconds in which 0.02 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org