Creating an array from a row in Excel

[anthonyjr2]

I'm having a lot of trouble getting the rows in my spreadsheet to get into an array. I've done this a million times with a single column, but for some reason the same syntax doesn't transfer over easily when using rows.

Previously I used 

$aArray = _Excel_RangeRead($oExcel, Default, $oExcel.ActiveSheet.UsedRange.Columns("A:A"))

To grab everything from the first column so I could use it. I don't really know how to do this for an entire row, and even when I might have gotten it, the UBound for my spreadsheet was turning out to be 1. For example, trying to get the row into an array by doing:

$oExcel.ActiveSheet.Range("A" & $i+1).EntireRow.Select
$aArray = _Excel_RangeRead($oExcel, Default, $pExcel.Selection)

gives me a UBound on $aArray of 1, even though my spreadsheet has over 30 columns in that row.

Can anyone tell me what I'm doing wrong? Thanks a bunch!

[water]

$aArray = _Excel_RangeRead($oExcel, Default, $oExcel.ActiveSheet.UsedRange.Rows("1:1"))

reads row 1 into a 2D array with a single row.

[anthonyjr2]

18 hours ago, water said:

$aArray = _Excel_RangeRead($oExcel, Default, $oExcel.ActiveSheet.UsedRange.Rows("1:1"))

reads row 1 into a 2D array with a single row.

That doesn't seem to be working for me. When I check the UBound of the resulting array, it returns a size of 1 even though I have around 30 columns.

Edited August 12, 2016 by anthonyjr2

[water]

What do you get with? This returns the number of columns in the array.

MsgBox(0, "UBound", UBound($aArray, 2))

 

[anthonyjr2]

Oh wow, I completely misunderstood how arrays worked in autoit. I was using 

ConsoleWrite(UBound($aArray, 0) & @LF)

because I wanted to access the subscripts, I didn't think it would import it as columns in the array. Using your flag makes it show up correctly. How can I go about accessing the elements of the array now? I was trying to just use $aArray[$i] where i is the index.

[water]

3 minutes ago, anthonyjr2 said:

I was trying to just use $aArray[$i] where i is the index.

Do you get the expected result?

[anthonyjr2]

On 8/12/2016 at 0:16 PM, water said:

Do you get the expected result?

No, I didn't. When I try to access it using the index I get a "Subscript dimension range exceeded."

[water]

Can you please run

_ArrayDisplay($aArray)

after _Excel_RangeRead and post the result?

[anthonyjr2]

Here's a picture of the output, I just crossed off the sensitive information. That does seem to be the information in row 1, so now I just don't know how to access it through a variable. Another quick question, how can I go about accessing rows based off of a variable? Like if I want to access row $j, I can't seem to figure out how to do it in the same style as:

$oExcel.ActiveSheet.UsedRange.Rows("1:1")

[water]

The result is a 2D array with just a single row. That's what [1][137] at the bottom of the ArrayDisplay tells you (1 row, 137 columns).
To access the first column use:

$aArray[0][0]

that's because counting rows and columns starts with 0. So the highest row index is 0, and the highest column index is 136.

To use a variable I suggest:

$oExcel.ActiveSheet.UsedRange.Rows($j & ":" & $j)

 

[anthonyjr2]

3 minutes ago, water said:

The result is a 2D array with just a single row. That's what [1][137] at the bottom of the ArrayDisplay tells you (1 row, 137 columns).
To access the first column use:

$aArray[0][0]

that's because counting rows and columns starts with 0. So the highest row index is 0, and the highest column index is 136.

To use a variable I suggest:

$oExcel.ActiveSheet.UsedRange.Rows($j & ":" & $j)

 

Awesome! Thanks a bunch for the quick answers water! Both of those answers were exactly what I was looking for.

[water]