Sign in to follow this  
Followers 0
will never learn

_Excelwritesheettoarray problem with Range (RESOLVED)

12 posts in this topic

#1 ·  Posted (edited)

Hello all,

I am trying to generate a routine that updates Excel Sheets on a locked workstation every night. To update the calculation I use _ExcelReadSheetToArray to read the data. Since I am using Microsoft Excel 2003 in German language, I edited the Excel.udf as described in This Bugtrack

Since my data fills up to 20.000 rows in the .csv (opened in Excel) and the size of the array is limited, I split up the array for 1000 rows at a time.

This works fine for the first 1000 rows. However, trying to copy any rows beyond Row 1426 fails. I get error 2,0 "Row out of Range"

This works

$arrayAuftraege2 = _ExcelReadSheetToArray($aExcel,1426,1,1,12,True)
;or
$arrayAuftraege1 = _ExcelReadSheetToArray($aExcel,1,1,1000,12,True)

This doesn't work:

$arrayAuftraege2 = _ExcelReadSheetToArray($aExcel,1427,1,1,12,True)
;or
$arrayAuftraege1 = _ExcelReadSheetToArray($aExcel,1001,1,1000,12,True)

All functions were tested isolated, so a combination of the functions cannot be the problem.

Thank you for your help.

Edited by will never learn

Share this post


Link to post
Share on other sites



This code works for me:

#include <Excel.au3>
#include <Array.au3>

Local $oExcel = ObjCreate("Excel.Application")
$oExcel.Workbooks.Open(@ScriptDir & "\test.xls")
Local $data = _ExcelReadSheetToArray($oExcel,1427,1,1,12,True)
_ArrayDisplay($data)
$data = _ExcelReadSheetToArray($oExcel,1000,1,1000,12,True)
_ArrayDisplay($data)

$oExcel = 0

Conditions: German Excel 2003, test.xls - workbook with a single spreadsheet 2000x12, _ExcelReadSheetToArray() unchanged from current AutoIt distribution.

The error must be somewhere else in your code, so it would help if you posted more of it.


UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

Are you using the same array after the first 1000 rows are written? If so, try doing a Redim on the array and see what you get.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Are you using the same array after the first 1000 rows are written? If so, try doing a Redim on the array and see what you get.

Why would I do that?

May I remind you, I am not the one, who's writing a program with _ExcelReadSheetToArray() but the OP is. I showed code that definitely works, now it is his/your turn to show something that doesn't.

Edited by doudou

UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

I don't think "will never learn" and "gtifsi" is the same person. But then I have been wrong before :blink:

Actually it doesn't matter. Whoever needs the functionality and doesn't manage to get his code fly shall provide a showcase.

UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

Actually it doesn't matter. Whoever needs the functionality and doesn't manage to get his code fly shall provide a showcase.

I agree. But still, I think it looked a little weird what you did above. But then I have been wrong before :blink:

Share this post


Link to post
Share on other sites

But still, I think it looked a little weird what you did above. But then I have been wrong before :blink:

So we all have... Therefore I modified my original reply to avoid misunderstandings.

UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Thank you for replying so fast. Here in Germany I had to wake up again first...

After some more testing, I am receiving two different error messages on two different scenarios:

This is the complete code I just tested:

Please note that the first line, in which I receive the error message, has changed slightly from 1427 to 1447.

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
#include <Date.au3>
#include <Excel.au3>
#include <Array.au3>
WinMinimizeAll()


$sFileAuftraege = "Auftraege.csv"

$aExcel = _ExcelBookOpen("O:\Terminpflege\Grunddaten - NICHT VERÄNDERN\"&$sFileAuftraege)
$arrayAuftraege2 = _ExcelReadSheetToArray($aExcel,1447,1,1,12,True)
msgbox(0,"",@error&","&@extended)

_ExcelBookClose($aExcel,  0,  0)

In the message box, I showed error "2,0", which according to the HelpFile means:

@error=2: Start parameter out of range

@extended=0: Row out of range

What I really want to do is getting large amounts of data, When trying to do this, and reaching beyond line 1447, I receive error 3,0

@error=3: Count parameter out of range

@extended=0: Row count out of range

The code is changed in the _ExcelReadSheetToArray command:

$arrayAuftraege2 = _ExcelReadSheetToArray($aExcel,1001,1,1000,12,True)

In the end, I was trying to do this:

$arrayAuftraege1 = _ExcelReadSheetToArray($aExcel,1,1,1000,12)
$arrayAuftraege2 = _ExcelReadSheetToArray($aExcel,1001,1,1000,12)
$arrayAuftraege3 = _ExcelReadSheetToArray($aExcel,2001,1,1000,12)
$arrayAuftraege4 = _ExcelReadSheetToArray($aExcel,3001,1,1000,12)

Conditions:

MS Office Excel 2003, SP3. German Version

On Terminalserver with MS Windows Server 2003, Standard Edition, German Version.

Thank you very much for your help:

P.S.: I have no idea who gtifsi is.

Edited by will never learn

Share this post


Link to post
Share on other sites

After some more testing, I am receiving two different error messages on two different scenarios:

This is the complete code I just tested:

Please note that the first line, in which I receive the error message, has changed slightly from 1427 to 1447.

I can only guess that something is wrong with the input data. Could you post a portion of the Auftraege.csv (some lines 1400 - 1450)? If it contains confidential data, please replace it with something that produces the same errors in your script.

UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

Seems like I was temporarily blindsided. The .csv file contains only 1446 lines. Yesterday there were 1426 lines of data.

This was the whole problem all the time. I will now just use the standard _ExcelReadSheetToArray with Default values (0) for RowCount and ColumnCount to get the full array.

Thank you very much for the help. Seems like it just needed a fresh set of eyes.

Share this post


Link to post
Share on other sites

Seems like I was temporarily blindsided. The .csv file contains only 1446 lines. Yesterday there were 1426 lines of data.

This was the whole problem all the time. I will now just use the standard _ExcelReadSheetToArray with Default values (0) for RowCount and ColumnCount to get the full array.

Super.

Thank you very much for the help. Seems like it just needed a fresh set of eyes.

De nada. You'll send them back when you are finished, will you? :blink:

UDFS & Apps:


DDEML.au3 - DDE Client + Server[*]
Localization.au3- localize your scripts[*]
TLI.au3 - type information on COM objects (TLBINF emulation)[*]
TLBAutoEnum.au3 - auto-import of COM constants (enums)[*]
AU3Automation - export AU3 scripts via COM interfaces
TypeLibInspector

- OleView was yesterday

Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCE 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0