Jump to content

_Excelwritesheettoarray problem with Range (RESOLVED)


Recommended Posts

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
Link to comment
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:

Spoiler

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Ā 

Link to comment
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.

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:

Spoiler

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Ā 

Link to comment
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:

Spoiler

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Ā 

Link to comment
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:
Link to comment
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:

Spoiler

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Ā 

Link to comment
Share on other sites

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
Link to comment
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:

Spoiler

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Ā 

Link to comment
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.

Link to comment
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:

Spoiler

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Ā 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...