mugh Posted July 27, 2008 Share Posted July 27, 2008 Hello,I used Excel.au3 / _ExcelReadSheetToArray() to read an Excel file into an array. The Excel file consists of only one sheet containing 600 rows and 7 columns. I tried the following code. But it returns nothing, when callung _ArrayDisplay().Here are my questions:- How can one read the content of the Excelsheet into an array? Alternatively, how is it possible to read from an CSV file into an array?- How can one transfer that data/array to include it into AutoIT V3 source code in the form myarray [row0/col0] [row1/col1] .. [rowX/colX]. The use is, that one has all data in source code and that there are no external files necessary at runtime.- What ist the right way to address (read/write) one element within an array (e.g. $myelement = myarray [1] [2]; addresses the element contained in the 2nd row and 3rd column ?That is what I tried to solve the first problem;#include <GUIConstants.au3> ; #include <ExcelCOM_UDF.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #cs format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <- declare an array for the login data sets first #ce Dim $arLogin[1000][7] ; Declare variables for Excel.au3 $oExcel = "liste.xls"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row) $iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column) ;$iRowCnt =; $iRowCnt - Count of rows to read, defaults to 0 (all) ;$iColCnt =; $iColCnt - Count of columns to read, defaults to 0 (all) ;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column. False by Default to match R1C1 values. ; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3 $arLogin = _ExcelReadSheetToArray($oExcel) ; Alternative method explicitly mentioning the defaults ; $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt) _ArrayDisplay($arLogin,"ArrayDisplay") MsgBox(0, "MyArraqy", $arLogin)Martin Haneke Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 Hello, I used Excel.au3 / _ExcelReadSheetToArray() to read an Excel file into an array. The Excel file consists of only one sheet containing 600 rows and 7 columns. I tried the following code. But it returns nothing, when callung _ArrayDisplay(). Here are my questions: - How can one read the content of the Excelsheet into an array? Alternatively, how is it possible to read from an CSV file into an array? - How can one transfer that data/array to include it into AutoIT V3 source code in the form myarray [row0/col0] [row1/col1] .. [rowX/colX]. The use is, that one has all data in source code and that there are no external files necessary at runtime. - What ist the right way to address (read/write) one element within an array (e.g. $myelement = myarray [1] [2]; addresses the element contained in the 2nd row and 3rd column ? That is what I tried to solve the first problem; #include <GUIConstants.au3> ; #include <ExcelCOM_UDF.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #cs format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <- declare an array for the login data sets first #ce Dim $arLogin[1000][7] ; Declare variables for Excel.au3 $oExcel = "liste.xls"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row) $iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column) ;$iRowCnt =; $iRowCnt - Count of rows to read, defaults to 0 (all) ;$iColCnt =; $iColCnt - Count of columns to read, defaults to 0 (all) ;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column. False by Default to match R1C1 values. ; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3 $arLogin = _ExcelReadSheetToArray($oExcel) ; Alternative method explicitly mentioning the defaults ; $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt) _ArrayDisplay($arLogin,"ArrayDisplay") MsgBox(0, "MyArraqy", $arLogin) Martin Haneke Example: $FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]) $ROW1_COL1 = $FILE[1][1] $ROW4_COL5 = $FILE[4][5] When the words fail... music speaks. Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Dear Andreik, thanks for that quick response. I set values for the variables now although it was mentioned that Excel.au3 contained the defaults for $iStartRow, $iStartColumn, $iRowCnt, $iColCnt Now I have the error: ExcelArrayTest.au3 (32) : ==> Subscript used with non-Array variable.: ; Declare variables for Excel.au3 $oExcel = "liste.xls"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $iStartRow = 0; $iStartRow - Row number to start reading, defaults to 1 (first row) $iStartColumn = 0; $iStartColumn - Column number to start reading, defaults to 1 (first column) $iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all) $iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all) ;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column. False by Default to match R1C1 values. ; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3 ;$FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]); AndreikĀ“s example ;$arLogin = _ExcelReadSheetToArray($oExcel); deactivated $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt); changed $ROW1_COL1 = $arLogin[1][1]; added $ROW4_COL5 = $arLogin[4][5]; added ; _ArrayDisplay($arLogin,"OtherArray") _ArrayDisplay($arLogin,"Display arLogin") MsgBox(0, "MyArraqy", $arLogin) MsgBox(0, "MyArraqy", $ROW1_COL1); added MsgBox(0, "MyArraqy", $ROW4_COL5); added Martin Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 (edited) You must open xls file first: $oExcel = _ExcelBookOpen($PATH & "\liste.xml",0) PS: Don't forget #Include <Excel.au3> Edited July 27, 2008 by Andreik When the words fail... music speaks. Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Hello Andreik, the problem stays the same: The array seems to have no content. Both _ArrayDisplay and MSsgBox return empty values. Although I opened it first with $oExcel = _ExcelBookOpen($PATH & "\liste.xml",0) And I defined the $PATH without trailing backslash. BTW: Is it necessary to open the XLS-file first with $oExcel = _ExcelBookOpen() ? I thought that this would have been done by _ExcelReadSheetToArray() Martin Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 Hello Andreik,the problem stays the same: The array seems to have no content. Both _ArrayDisplay and MSsgBox return empty values.Although I opened it first with $oExcel = _ExcelBookOpen($PATH & "\liste.xml",0)And I defined the $PATH without trailing backslash.BTW: Is it necessary to open the XLS-file first with $oExcel = _ExcelBookOpen() ? I thought that this would have been done by _ExcelReadSheetToArray() MartinYes it`s necessary. First parameter of _ExcelReadSheetToArray() is $oExcel.$oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 I write an example: #Include <Excel.au3> #include <Array.au3> $FILE = FileOpenDialog("OPEN",@DesktopDir,"Excel Files (*.xls)",1) $Excel = _ExcelBookOpen($FILE,0) $DATA = _ExcelReadSheetToArray($Excel,1,1,10,7) _ArrayDisplay($DATA) MsgBox(0,"TEST",$DATA[5][5]) _ExcelBookClose($Excel,0) When the words fail... music speaks. Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Andreik, sorry. I read it meanwhile. But I still have the problem, that I have empty values. Now I see, that it is the missing Excel object. I do not know why the object turns out to be empty. Any ideas? Dim $oExcel = _ExcelBookOpen("C:\liste2.xls",0) .. MsgBox(0, "Excelobject", $oExcel) Martin Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Hello Andreik, let me test Your example first. And thank You so far. Martin Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Hello Anreik, You wonĀ“t believe it: I took Your source, put it into a new File and together with book1.xls, excel.au3 and array.au3 into their own directory. Resulkt: empty values and no message box. I hav not the faintest idea what the problem is. BTW: I shut down the abandoned excel processes which resided in my computerĀ“s memory before I started the script. #Include <Excel.au3> #include <Array.au3> $FILE = FileOpenDialog("OPEN","C:\book1","Excel Files (*.xls)",1) $Excel = _ExcelBookOpen($FILE,0) $DATA = _ExcelReadSheetToArray($Excel,1,1,10,7) _ArrayDisplay($DATA) MsgBox(0,"TEST",$DATA[5][5]) _ExcelBookClose($Excel,0) Error Log says: >"C:\Programme\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Book1\ExcelArrayTest02.au3" /autoit3dir "C:\Programme\AutoIt3" /UserParams +>16:10:25 Starting AutoIt3Wrapper v.1.10.1.8 Environment(Language:0407 Keyboard:00000407 OS:WIN_XP/Service Pack 2 CPU:X86) >Running AU3Check (1.54.13.0) from:C:\Programme\AutoIt3 +>16:10:25 AU3Check ended.rc:0 >Running:(3.2.12.1):C:\Programme\AutoIt3\autoit3.exe "C:\Book1\ExcelArrayTest02.au3" C:\Book1\ExcelArrayTest02.au3 (8) : ==> Subscript used with non-Array variable.: MsgBox(0,"TEST",$DATA[5][5]) MsgBox(0,"TEST",$DATA^ ERROR ->16:10:28 AutoIT3.exe ended.rc:1 >Exit code: 1 Time: 3.656 Martin Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 What version of Autoit use? When the words fail... music speaks. Link to comment Share on other sites More sharing options...
picaxe Posted July 27, 2008 Share Posted July 27, 2008 Start column and row should be 1. This works for me #include <GUIConstants.au3> ; #include <ExcelCOM_UDF.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #cs format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <- declare an array for the login data sets first #ce Dim $arLogin[1000][7] ; Declare variables for Excel.au3 $Excel = "liste.xls"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row) $iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column) $iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all) $iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all) ;$iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column. False by Default to match R1C1 values. $oExcel = _ExcelBookOpen(@ScriptDir & "\" & $Excel) ; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3 ;$FILE = _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]); AndreikĀ“s example $arLogin = _ExcelReadSheetToArray($oExcel, $iStartRow, $iStartColumn, $iRowCnt, $iColCnt); changed ;~ $ROW1_COL1 = $arLogin[1][1]; added ;~ $ROW4_COL5 = $arLogin[4][5]; added ; _ArrayDisplay($arLogin,"OtherArray") _ArrayDisplay($arLogin,"Display arLogin") ;~ MsgBox(0, "MyArraqy", $arLogin) ;~ MsgBox(0, "MyArraqy", $ROW1_COL1); added ;~ MsgBox(0, "MyArraqy", $ROW4_COL5); added Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 3.2.12.1 regular version Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Hello, does anbody has an idea why the following code returns an empty value for the excelsheet? expandcollapse popup#include <GUIConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #cs format of source file is -> idxno;city; username; old_number;hostname; new_username; new_password;IP <- declare an array for the login data sets first #ce Dim $arLogin[1000][7] ; Declare variables for Excel.au3 ; Dim $oExcel = "xxxxx"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() Dim $iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row) Dim $iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column) Dim $iRowCnt = 600; $iRowCnt - Count of rows to read, defaults to 0 (all) Dim $iColCnt = 7; $iColCnt - Count of columns to read, defaults to 0 (all) ;Dim $iColShift =; $iColShift - Determines if the Array returned, from Excel, will begin in the 0-index base or 1-index base Column. False by Default to match R1C1 values. ; Syntax of _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]) ; $FILE = FileOpenDialog("OPEN","C:\book1","Excel Files (*.xls)",1) $FILE = "C:\book1\book1.xls" $oExcel = _ExcelBookOpen($FILE,0) ; Fill array with data with Func _ExcelReadSheetToArray() from Excel.au3 ;$arLogin = _ExcelReadSheetToArray($oExcel); simple variant $arLogin = _ExcelReadSheetToArray($oExcel,$iStartRow,$iStartColumn,$iRowCnt,$iColCnt) ; Display Excel and other objects MsgBox(0, "Excelobject", $oExcel) _ArrayDisplay($arLogin,"Display arLogin") MsgBox(0, "MyArray", $arLogin) ; Close Excel Sheet ; Syntax: _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0) _ExcelBookClose($oExcel,0,0) Link to comment Share on other sites More sharing options...
Andreik Posted July 27, 2008 Share Posted July 27, 2008 Code work fine. Look at your xls, maybe there is something wrong. PS: Don't use MsgBox(0, "Excelobject", $oExcel) Will not return nothing. Use MsgBox(0, "Excelobject", $oExcel[0]) to view number of subscripts or a for loop to view values from array. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
picaxe Posted July 27, 2008 Share Posted July 27, 2008 Depending on what object info you want you need something like MsgBox(0,"Excel File Name", $oExcel.ActiveWorkbook.Name) Link to comment Share on other sites More sharing options...
mugh Posted July 27, 2008 Author Share Posted July 27, 2008 Hello, I did not solve my problem yet. But it turned out that _ExcelReadSheetToArray() is missing its first and most important parameter ($FilePath). While I did not found out what the problem is, may be somebody has a hint on me? I left the parameters on defaults and "$oExcel" seems to be emtpy when calling _ExcelreadToArray(). Although MsgBox (0,$oExcel) shows up with value "C:\Book1\Books1.xls". Shotrly afteer I call _ExcelReadSheetToArray() returning errorcode 2. That means "@error=2 - Start parameter out of range". Syntax: _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt], [$iColShift]]) expandcollapse popup#include <GUIConstants.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> Dim $arLogin ; Declare variables for Excel.au3 ;Dim $oExcel = "liste.xls"; Filename; Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $iStartRow = 1; $iStartRow - Row number to start reading, defaults to 1 (first row) $iStartColumn = 1; $iStartColumn - Column number to start reading, defaults to 1 (first column) $iRowCnt = 0; $iRowCnt - Count of rows to read, defaults to 0 (all) $iColCnt = 0; $iColCnt - Count of columns to read, defaults to 0 (all) $iNumCells = 6 $iIndexBase = 0 $iDirection = 0 $iColShift = False $fReadOnly = True $fVisible = 0 $sPassword = "" $sWritePassword = "" $sFilePath = "C:\Book1\Book1.xls" $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly, $sPassword, $sWritePassword) MsgBox (0,"File Path",$sFilePath) MsgBox (0,"ExitCode OpenBook",@error) $arLogin = _ExcelReadSheetToArray($oExcel,$iStartRow,$iStartColumn,$iRowCnt,$iColCnt,$iColShift) MsgBox (0,"ExitCode ExcelReadtoArray",@error) MsgBox (0,"Excel Object",$oExcel) MsgBox (0,"StartRow",$iStartRow) MsgBox (0,"StartCol",$iStartColumn) MsgBox (0,"RowCnt",$iRowCnt) MsgBox (0,"ColCnt",$iColCnt) MsgBox (0,"ColShift",$iColShift) _ArrayDisplay($arLogin,"Display arLogin as list") _ExcelBookClose($oExcel,0,0) _ArrayDisplay($arLogin,"Display arLogin as list") 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