How to display multiple Excel columns in array

[antmar904]

Hi all,

I am trying to disply columns B and F in an array but its not working.

 

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>

$oExcel = _Excel_Open ("", "", "", "", True)
$Book = _Excel_BookOpen($oExcel, @ScriptDir & "\LNExport.xlsx")
$Range1 = $Book.Activesheet.Range("B2").EntireColumn
$Range2 = $Book.Activesheet.Range("F2").EntireColumn
$Result = _Excel_RangeRead($Book, "Sheet 1", $Range1:$Range2)
_ArrayDisplay($Result)
_Excel_Close($Book)
_Excel_Close($oExcel)

 

I can successfully display either column B or column F but I cannot display them both at the same time.

Edited February 10, 2016 by antmar904

[water]

The UDF does not support unions. You would need to code something like this:

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>

$oExcel = _Excel_Open(Default, Default, Default, Default, True)
$oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsx")
$aSource1 = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.UsedRange.Columns("B"))
$aSource2 = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.UsedRange.Columns("F"))
Global $aTarget[UBound($aSource1, 1)][2]
For $i = 0 To UBound($aSource1, 1) - 1
    $aTarget[$i][0] = $aSource1[$i]
    $aTarget[$i][1] = $aSource2[$i]
Next
_ArrayDisplay($aTarget)
_Excel_Close($oBook)
_Excel_Close($oExcel)

 

[kylomas]

@water,

I can get this to work like this...

#include <Excel.au3>
#include <Array.au3>

$oExcel = _Excel_Open(Default, Default, Default, Default, True)
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

$oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xls")
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

$aSource1 = _Excel_RangeRead($oBook)
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

Global $aTarget[UBound($aSource1, 1)][2]
For $i = 0 To UBound($aSource1, 1) - 1
    $aTarget[$i][0] = $aSource1[$i][0]
    $aTarget[$i][1] = $aSource1[$i][4]
Next

_ArrayDisplay($aTarget)
_Excel_Close($oBook)
_Excel_Close($oExcel)

but when I use...

$aSource1 = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.UsedRange.Columns("B"))

I get an array with no values and @ERROR = 0.

Environment:

• Win 7 home x64
• Excel 2002/SP3

Incidentally, your code only works if both columns are the same length.  My code solves that but will take longer if there is a lot of data.

kylomas

Edit: This is what I ran...

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>

$oExcel = _Excel_Open(Default, Default, Default, Default, True)
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

$oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xls")
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

$aSource1 = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.UsedRange.Columns('B'))
;$aSource1 = _Excel_RangeRead($oBook)
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)
_ArrayDisplay($aSource1, '#1')

$aSource2 = _Excel_RangeRead($oBook, Default, $oBook.ActiveSheet.UsedRange.Columns("F"))
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)
_ArrayDisplay($aSource1, '#2')

Global $aTarget[UBound($aSource1, 1)][2]
For $i = 0 To UBound($aSource1, 1) - 1
    $aTarget[$i][0] = $aSource1[$i]
    $aTarget[$i][1] = $aSource2[$i]
Next

_ArrayDisplay($aTarget)
_Excel_Close($oBook)
_Excel_Close($oExcel)

using this spreadsheet...test.xls

Edited February 10, 2016 by kylomas

[water]

Run the following script and you will see that the UsedRange starts in column B. When you retrieve the UsedRange and then access column "B" in this range you actually access column "C" of the whole worksheet - which indeed is empty:

#include <Excel.au3>
#include <Array.au3>
#include <File.au3>

$oExcel = _Excel_Open(Default, Default, Default, Default, True)
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)

$oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test Kylomas.xls")
If @error Then Exit MsgBox(17, '', @error & @CRLF & @extended)
$oBook.ActiveSheet.UsedRange.Interior.ColorIndex = 3
MsgBox(0, "", "Usedrange")

_Excel_Close($oBook)
_Excel_Close($oExcel)

Edited February 11, 2016 by water

[antmar904]

@water

Thank you that worked well.

[water]