Help with Excel RangeWrite and Array Value

[Ibet]

Hey all, 

Ending day 2 of learning AutoIt, and I'm stumped. I wrote an extremely rudimentary script simulating keystrokes for reading/copying values from one excel spreadsheet and pasting them into another spreadsheet, line by line. It works, but it doesn't use any of the Excel UDFs and was just sloppy. So, I'm trying to re-write it using some Excel UDFs to not only optimize the script, but to also learn how to use the Excel UDFs. If the answer is in a help file, please explain as I'm sometimes having problems understanding the examples in the help files.

I'm getting the error: 

"C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Read spreadsheet 1 - write spreadsheet 2-version2.au3" (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
MsgBox(0,"Test","Test",$SourceEntry[1])
MsgBox(0,"Test","Test",^ ERROR
>Exit code: 1    Time: 1.804

Here is the code:

#include<Array.au3>
#include<Excel.au3>
;-------------------Read from Source---------------------------
Local $oExcel_Source = _Excel_Open()
Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel_Source,$sWorkbook)

Local $SourceRow = 3 ;--eventually will be used to iterate through the rows, one at a time
Local $SourceEntry[5] = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow)
_ArrayDisplay($SourceEntry, "1D Display") ;--Displays array values correctly
MsgBox(0,"Test","Test",$SourceEntry[1])  ;--Gives error, for any index in the array

I want to make sure I can read the values of the array individually, before I try putting them into another document. This is because I've got to add some checks against the values already existing in the destination spreadsheet before any manipulation. I've spent the last hour or more googling that error and reading multiple posts where that error is meaning many different things, so unsure EXACTLY what the problem is. Would greatly appreciate a fix and/or explanation as well as patience with my noob-ness.

Thanks in advance

[Subz]

Its a 2d array, so you need to use something like:

Local $SourceEntry = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow)
_ArrayDisplay($SourceEntry, "2D Display") ;--Displays array values correctly
MsgBox(0,"Test","Test",$SourceEntry[0][1]) ;~ [0] equals row number [1] = column 1

Rather than copying one row at a time you can capture the all used A:E columns in an array and then perform actions on that array, example:

Local $aSourceEntry = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:E"))
    _ArrayDisplay($aSourceEntry)
    For $i = 0 To UBound($aSourceEntry) - 1
        MsgBox(4096, "", "Row: " & $i & @CRLF & _
        "Column A: " & $aSourceEntry[$i][0] & @CRLF & _
        "Column B: " & $aSourceEntry[$i][1] & @CRLF & _
        "Column C: " & $aSourceEntry[$i][2] & @CRLF & _
        "Column D: " & $aSourceEntry[$i][3] & @CRLF & _
        "Column E: " & $aSourceEntry[$i][4])
    Next

 

Edited January 31, 2019 by Subz

[Ibet]

Interesting, hmm. I'm familiar with 2D arrays, but I would have thought that because I defined the array as a 1D with 5 spaces, that it would have stayed 1D. So, the _Excel_RangeRead forced it into a 2D array and didn't throw an error until I tried to use it as 1D. Had it told me that it couldn't write to the 1D array instead, or only stored 1 row of data, I may have realized it was capturing more than 1 row of data. The extra row seems to be the labels, even more interesting is that when I did a 1D ArrayDisplay, the data in the window looked the same as the 2D ArrayDisplay, so I just figured it was formatting for the window, not what was actually stored in the array. 

Also, had the 1D display only shown |Row|Col 0|Col 1|Col 2|Col 3|Col 4|, I might have realized it was putting extra things into the array. I guess I'm use to more strict languages that would not have changed the array like that.

Alrighty, thanks for your help Subz, AutoIT is a bit quirky, but I think I'm picking it up fairly well. Going into Day 3 with that info will help me accomplish my goal today.

[Ibet]

9 hours ago, Subz said:

Its a 2d array, so you need to use something like:

Local $SourceEntry = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow)
_ArrayDisplay($SourceEntry, "2D Display") ;--Displays array values correctly
MsgBox(0,"Test","Test",$SourceEntry[0][1]) ;~ [0] equals row number [1] = column 1

 

Using this exact example, the MsgBox text is blank where $SourceEntry[0][1] is, however the ArrayDisplay shows all the info in there. I've even tried [1][1] and every other field that is in that 2D array. Not getting any errors though.

[Subz]

Can you post a screenshot?  I'm guessing B3 in your spreadsheet is blank?  If you want to access other columns you would use [0][0] for column A, [0][1] for column B and so on.

PS: I actually don't know how you were able to run the code in the op, what version of Autoit are you using?  Because it should have alerted you to a syntax error.  You can't assign a value to a new array like that, you'd have to use square brackets i.e. [ data ].  However even if you added the square brackets you would be assigning the Excel array to column 0, so an array within a array.  Hope that made sense.

[Ibet]

Currently on AutoIt version 3.3.14.5

B3 in the source spreadsheet is definitely not blank. Fields A1 through E558 all have data in them, some are strings of text, some are numbers, no blanks. I was very surprised, in my original code, that I was even able to write into the $SourceEntry array defined as a 1D array with 2D of info. Other languages I've dabbled in wouldn't have allowed that. I spent several hours trying to get what I needed to happen, failed and stopped messing with it to get some actual work done. 

I'll post what my code looks like now in it's entirety. I've been trying everything I can to get a row number into a variable that I can use to locate that row in the destination spreadsheet. The idea is to loop, going one line at a time in the source spreadsheet, and save 1 row at a time to an array from the source spreadsheet, then switch to the destination spreadsheet, search for a matching name(which would be stored in the array slot 0), capture that row number, then save that entire row into another array. I would then setup if statements for certain column values that need to be compared and take actions depending on the result of the if statement.

(Most things commented out are my various attempts at a solution based on reading other forum posts. Will eliminate what doesn't work or isn't needed after I get it to work)

 

expandcollapse popup

#include<Array.au3>
#include<Excel.au3>
;-------------------Open Workbooks---------------------------
Local $oExcel = _Excel_Open()
Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel,$sWorkbook)
Local $tWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_DEST.xlsx"
Local $pWorkbook = _Excel_BookOpen($oExcel,$tWorkbook)
;------------------------------------------------------------

;-----------------Read from source---------------------------
Local $SourceRow = 325
Local $SourceEntry[][] = _Excel_RangeRead($oWorkbook,"AlarmListDB","A"&$SourceRow&":E"&$SourceRow)
;--------------------------------------------------------------

;----------------Search Dest for row number--------------------
$DestSearch = _Excel_RangeFind($pWorkbook,$SourceEntry[0][0])  ;--Searches for match, returns info about cell found, address is in [0][2]
;_ArrayDisplay($DestSearch,"2D Display") ;--For testing
;$DestRow = StringRegExp(_ArrayToString($DestSearch[0][2],"\$\w*\$(\d*)",3),"",0) ;--Regex strips everything from cell address from find, except row number. Ex. $A$1 becomes 1 Then converts to string
$DestRow = StringRegExp($DestSearch[0][2],"\$\w*\$(\d*)",3) ;--Regex strips everything from cell address from find, except row number. Ex. $A$1 becomes 1
;$DestRow = _ArrayToString($DestSearchRowNum,"",0)
;MsgBox(0,"Value",$DestRow)
;--------------------------------------------------------------

;---------------------Read from Dest---------------------------
Local $DestEntry[][] = _Excel_RangeRead($pWorkbook,Default,"A"&$DestRow&":V"&$DestRow)
;_ArrayDisplay($DestEntry, "2D Display")

;--------------------------------------------------------------


;---------------------Write to Dest----------------------------
;Local $DestEntry = _Excel_RangeRead($pWorkbook,Default,"A1:E1")

;If $DestEntry[0][0] == $SourceEntry[0][0] Then
;   MsgBox(0,"Match?","YES")
;Else
;   MsgBox(0,"Match?","NO")
;  _Excel_RangeWrite($pWorkbook,Default,$DestEntry,"A3:V3")
;EndIf
;---------------------------------------------------------------

;-----------------------Capture row #---------------------------
;Local $activeCell = $oExcel_Dest.ActiveCell.Address
;MsgBox (0,"Current Active Cell Address",$activeCell)
;---------------------------------------------------------------

Since it has been highly modified since the original post, I'm afraid a screenshot might not help. But, if you still need, I can take it back to the way it was.

[Ibet]

The worst feeling part of all this, is that the sloppy macro I made yesterday that only mimics keystrokes, utilizes copy/paste and ClipGet to save into arrays, finished out the spreadsheet task I had to do today. So now, I just want to get it to work more quickly and efficiently, because I will have to do this again in the future AND for the sake of learning something new. 1000+ lines compared and altered accordingly at 13 seconds per line.

The biggest issue I had with the sloppy macro were getting occasional ctrl locks and clipboard errors in excel. But, I'd just stop the macro and restart it where it left off. 

[Ibet]

52 minutes ago, Subz said:

However even if you added the square brackets you would be assigning the Excel array to column 0, so an array within a array.  Hope that made sense.

I think what would REALLY help me, is a better understanding of EXACTLY how _Excel_RangeRead returns the information into the array. I've only been assuming that it returns each cell's value into the next array index. If it's creating an array and returning a populated array, into the array I defined, then I could see where the "array within an array" could happen. What a mess  It also seems that MsgBox doesn't like returning a single index value from an array. Even when I made a 2d array and manually defined each individual value, MsgBox still would not display it. (did this as part of testing)

i.e. - this MsgBox did not display a 2

$aTest[][] = [[1,2,3],[4,5,6]]
MsgBox(0,"Test","Value:",$aTest[0][1])

 

Edited January 31, 2019 by Ibet

[Subz]

As the help file mentions, if you only select one cell it will return a string however if you select a range of cells it will return an array.  Did you test the second example I posted above to show you how to iterate through the array?

[water]

_Excel_RangeRead exactly returns what you ask for. Example: You read cells A1:E7. You get a 2D-array with 7 rows and 5 columns. Cell A1 is $Result[0][0], cell D3 is $Result[3][4] and so on. 

[Ibet]

10 minutes ago, Subz said:

As the help file mentions, if you only select one cell it will return a string however if you select a range of cells it will return an array.  Did you test the second example I posted above to show you how to iterate through the array?

Ok, so using a range, it returns a complete array. That makes more sense as I was thinking it needed an established array (sizing included) to write into. So it just needs an empty variable. As for your example of iterating through the array, I understand how to do that and have considered saving the entire source spreadsheet into an array for easier iterations. However, the destination spreadsheet has 4233 rows and 22 columns, with only 1072 rows and 3 columns being relevant to this process. I didn't really want to copy that whole thing into an array for comparison operations. So, figured I'd stick to line by line with a find and overwrite the array values in each iteration.

5 minutes ago, water said:

_Excel_RangeRead exactly returns what you ask for. Example: You read cells A1:E7. You get a 2D-array with 7 rows and 5 columns. Cell A1 is $Result[0][0], cell D3 is $Result[3][4] and so on. 

Water, I've read many posts with your knowledge in them over the last 2 days. Subz as well. Thank you both for taking a moment to help me learn. I think the primary issue lies within my writing an array within an array. I will attempt it again without doing that.

[water]

Glad to be of service