will never learn Posted August 2, 2010 Share Posted August 2, 2010 (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 BugtrackSince 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 August 3, 2010 by will never learn Link to comment Share on other sites More sharing options...
doudou Posted August 2, 2010 Share Posted August 2, 2010 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 + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
gtifsi Posted August 3, 2010 Share Posted August 3, 2010 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. Link to comment Share on other sites More sharing options...
doudou Posted August 3, 2010 Share Posted August 3, 2010 (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 August 3, 2010 by doudou UDFS & Apps: Spoiler DDEML.au3 - DDE Client + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
AdmiralAlkex Posted August 3, 2010 Share Posted August 3, 2010 I don't think "will never learn" and "gtifsi" is the same person. But then I have been wrong before .Some of my scripts: ShiftER, Codec-Control, Resolution switcher for HTC ShiftSome of my UDFs: SDL UDF, SetDefaultDllDirectories, Converting GDI+ Bitmap/Image to SDL Surface Link to comment Share on other sites More sharing options...
doudou Posted August 3, 2010 Share Posted August 3, 2010 I don't think "will never learn" and "gtifsi" is the same person. But then I have been wrong before 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 + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
AdmiralAlkex Posted August 3, 2010 Share Posted August 3, 2010 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 .Some of my scripts: ShiftER, Codec-Control, Resolution switcher for HTC ShiftSome of my UDFs: SDL UDF, SetDefaultDllDirectories, Converting GDI+ Bitmap/Image to SDL Surface Link to comment Share on other sites More sharing options...
doudou Posted August 3, 2010 Share Posted August 3, 2010 But still, I think it looked a little weird what you did above. But then I have been wrong before So we all have... Therefore I modified my original reply to avoid misunderstandings. UDFS & Apps: Spoiler DDEML.au3 - DDE Client + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
will never learn Posted August 3, 2010 Author Share Posted August 3, 2010 (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 August 3, 2010 by will never learn Link to comment Share on other sites More sharing options...
doudou Posted August 3, 2010 Share Posted August 3, 2010 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 + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
will never learn Posted August 3, 2010 Author Share Posted August 3, 2010 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 More sharing options...
doudou Posted August 3, 2010 Share Posted August 3, 2010 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? UDFS & Apps: Spoiler DDEML.au3 - DDE Client + ServerLocalization.au3 - localize your scriptsTLI.au3 - type information on COM objects (TLBINF emulation)TLBAutoEnum.au3 - auto-import of COM constants (enums)AU3Automation - export AU3 scripts via COM interfacesTypeLibInspector - OleView was yesterday Coder's last words before final release: WE APOLOGIZE FOR INCONVENIENCEĀ Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now