ExcelWriteArray = Parameter out of range

[roofninja]

I can't seem to figure out why I am getting an error. It happens with the _excelwritearray line. The error is @error=2 - Parameter out of range. I have checked that the array has data and that $eof has is a number. The other parameter is the excel file. I have checked all that I can think of and still can't find what I did wrong. Point me in the write direction, please. Thanks for any help.

CODE

#include<ExcelCOM_UDF.au3>

#include <Array.au3>

dim $tarray[1]

$count = 0

$eof = 0

$file = FileOpen(@ScriptDir&"\sptxt\inventory.txt", 0)

; Check if file opened for writing OK

If $file = -1 Then

MsgBox(0, "Error", "Unable to open inventory.txt data file.")

Exit

EndIf

$oExcel = _ExcelBookOpen(@ScriptDir&"\sptxt\inventory.xls",0, False);this will open the inventory.xls file.

If @error >= 1 Then

$oExcel = _ExcelBookNew(0);this is here to create the inventory.xls file if it does not exist.

EndIf

;I want to read the file into an array until data divider and then write array to excel or eof

While 1

$line = FileReadLine($file)

If @error = -1 Then ExitLoop

$skip = StringReplace($line,"*","",0,0)

$count = $count+1

if $skip<>";" then

_ArrayAdd($tarray,$line) ;this reads the file into an array

;MsgBox(0, "Line read:", $line)

EndIf

if $count = 14 or $skip = ";" then

$sheet = _ExcelSheetNameGet($oExcel);finds the name of the active sheet

$array = _ExcelSheetUsedRangeGet($oExcel,$sheet);finds the last line of data.

if $eof <= $array[3] Then ;this keeps us from copiing over data in sheet

$eof = ($array[3] +1)

EndIf

_ExcelWriteArray($oExcel, $eof, 0, $tarray, 0, 0)

;_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0)

; On Failure - Returns 0 and sets @error on errors:

; @error=1 - Specified object does not exist

; @error=2 - Parameter out of range

; @extended=0 - Row out of range

; @extended=1 - Column out of range

; @error=3 - Array doesn't exist / variable is not an array

; @error=4 - Invalid direction parameter

MsgBox(0,"Error message for excel",@error)

; _ArrayDisplay($tarray,"Line to write to excel")

for $ts = 1 to 13

_ArrayDelete($tarray,$ts) ;this deletes the array

Next

$count = 0

EndIf

Wend

; Now we save it into the temp directory; overwrite existing file if necessary

_ExcelBookSaveAs($oExcel,@ScriptDir&"\sptxt\inventory.xls","xls",0,0)

; And finally we close out

_ExcelBookClose($oExcel)

This is the txt file.

CODE

File1

Coulter

R52

IBM

XP

1023MB

Pentium M 1862MHz

LVP9102

352-9999

None

352-None

No

No

;***************************************

File2

Linda

GX270

Dell

XP

1023MB

Pentium M 1862MHz

LVP9102

352-8888

HP LaserJet 1200

352-8899

No

No

;***************************************

[flyingboz]

You'll need to dig a bit under the covers.

My excel functions predate the two serious attempts at standardizing excel automation via au3,

and, as it took significant effort to 'get in the head' of the creator of the object model,

I have not wanted to spend the effort to try to get in the head of an au3 programmer as well.

Out of range parameters could mean a lot of things.. Just as an example, I ran into similar issues when trying to create

borders, not realizing that you have to worry about things like whether the cell you're trying to create a border on

has a "left" or "top" in the active or selected range. Then there are the little VBA quirks/bugs, like how you copy a worksheet in a workbook more than 254 times <the docs say you can have as many worksheets as memory, but just try on excel 2000 or 2003 to get there by copying and pasting a worksheet n times.>

Create little test cases if necessary, and see where it blows up, line by line in the UDF; or Roll your own function, stripping out any unnecessary stuff in the UDF.

You'll get good at little tricks like doing what you want with the excel macro recorder, then examing the vba code for what commands were used, then reading the object model documentation for the pertinent methods / properties , then looking up vbs snippets using those object methods and properties, then converting them to au3.

As time goes on, you'll develop your own library of excel functions.

[PsaltyDS]

I can't seem to figure out why I am getting an error. It happens with the _excelwritearray line. The error is @error=2 - Parameter out of range. I have checked that the array has data and that $eof has is a number. The other parameter is the excel file. I have checked all that I can think of and still can't find what I did wrong. Point me in the write direction, please. Thanks for any help.

; ...

        _ExcelWriteArray($oExcel, $eof, 0, $tarray, 0, 0)

; ...

Excel Row/Col numbers are 1-based. Should be:

_ExcelWriteArray($oExcel, $eof, 0, $tarray, 1, 0)

[roofninja]

Excel Row/Col numbers are 1-based. Should be:

_ExcelWriteArray($oExcel, $eof, 0, $tarray, 1, 0)

Ok, I feel stupid... Thanks.