val75 Posted February 25, 2015 Posted February 25, 2015 Hallo everybody, I'm just starting to learn AutoIT :-) I'm trying to load all values from A, B and Z columns to array. It is possible use _Excel_RangeRead() to load specific columns to array or load A, B and Z columns to separate arrays and marge them into one array on the end? Local $sResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("A:Z"), 2) Thanks in advice
water Posted February 25, 2015 Posted February 25, 2015 Welcome to AutoIt and the forum! _Excel_RangeRead only works on a contiguous range of cells. So you can read columns A and B into one array and colum Z into another. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki
val75 Posted February 25, 2015 Author Posted February 25, 2015 (edited) Thanks, I tried to load values into multidimensional array, but it consumes to much time. Excel sheet contains almost 6000 rows. Local $sArrayFromExcelValues[0][3] For $i = 1 To $oWorkbook.ActiveSheet.UsedRange.Rows.Count Local $collA = _Excel_RangeRead($oWorkbook, Default, "A" & $i) Local $collB = _Excel_RangeRead($oWorkbook, Default, "B" & $i) Local $collZ = _Excel_RangeRead($oWorkbook, Default, "Z" & $i) Local $arrTemp[1][3] = [[$collA , $collB , $collC]] _ArrayAdd($sArrayFromExcelValues, $arrTemp) Next _ArrayDisplay($sArrayFromExcelValues, "excel values") Thanks Edited February 25, 2015 by val75
kylomas Posted February 25, 2015 Posted February 25, 2015 val75, Try the following... #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> OnAutoItExitRegister ( "_fini" ) local $st = timerinit() Local $oE = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL,'Open Failed', @error & @lf & @extended) ; get columns and populate array Local $sWorkbook = @ScriptDir & "\Exceltest.xls" Local $oWB = _Excel_BookOpen($oE, $sWorkbook, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $aResult = _Excel_RangeRead($oWB, Default, $oWB.ActiveSheet.Usedrange.Columns("A:Z"), 2) local $aFinal[UBound($aResult)][3] for $1 = 0 to UBound($aResult) - 1 $aFinal[$1][0] = $aResult[$1][0] $aFinal[$1][1] = $aResult[$1][1] $aFinal[$1][2] = $aResult[$1][25] next ConsoleWrite('Time to get spreadsheet and populate final array = ' & timerdiff($st)/1000 & @CRLF) _arraydisplay($aFinal,'Final',default,default,default,'A|B|Z') func _fini() _Excel_Close($oE) endfunc The spread sheet is 6000 rows and runs as follows >"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\ADMIN010\Documents\help - Excel.au3" /UserParams +>16:07:38 Starting AutoIt3Wrapper v.14.801.2025.0 SciTE v.3.4.4.0 Keyboard:00000409 OS:WIN_7/Service Pack 1 CPU:X64 OS:X64 Environment(Language:0409) +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\ADMIN010\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.13.19) from:C:\Program Files (x86)\AutoIt3 input:C:\Users\ADMIN010\Documents\help - Excel.au3 +>16:07:38 AU3Check ended.rc:0 >Running:(3.3.12.0):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\ADMIN010\Documents\help - Excel.au3" --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop Time to get spreadsheet and populate final array = 1.05814865721724 +>16:07:43 AutoIt3.exe ended.rc:0 +>16:07:43 AutoIt3Wrapper Finished. >Exit code: 0 Time: 5.86 kylomas AnonymousX, santhoshkumargr and addjon 3 Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill
draien Posted February 26, 2015 Posted February 26, 2015 (edited) There you go: ;Copy a certain column from Excel sheet to an Array Func _ReadFromExcel($column) Local $data Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, $xlsPath) If @error Then Switch @error Case 2 MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen", "Specified Filepath does not exist" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Case 3 MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen", "Error opening the selected Excel file." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Case 4 MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen", "Readwrite access could not be granted. Workbook might be open by another users/task." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndSwitch ProcessClose("EXCEL.exe") Else $data = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns($column & ":" & $column), 2) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) EndIf Return $data EndFunc Just call it like: $xlsPath = "Excel.xls" ; Path to your excelsheet $aExcelCol = _ReadFromExcel("A") Edited February 26, 2015 by draien PoojaKrishna 1
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