anthonyjr2

Creating an array from a row in Excel

12 posts in this topic

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!


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites



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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by anthonyjr2

UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

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

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites
3 minutes ago, anthonyjr2 said:

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

Do you get the expected result?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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."


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

Can you please run

_ArrayDisplay($aArray)

after _Excel_RangeRead and post the result?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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")

Capture.PNG


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

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)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now