Excel - load values from specific columns into array

[val75]

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]

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.

[val75]

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]

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

[draien]

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

[val75]

Thanks a lot :-)