Jump to content
Ibet

Help with Excel RangeWrite and Array Value

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

 

#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.

Share this post


Link to post
Share on other sites

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. :shifty:

Share this post


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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

_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. 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites

Glad to be of service :) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

  • Similar Content

    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • By sksbir
      Hi
      Trying this from autoit v3.3.14.5 and SCITE 3.19.102.1901.0 :
      help file , page "Language Reference - Variables" , sample autoit script about maps:

      Maps must be declared before use by defining their scope using the 'Global/Local/Static' keywords. Local $mControls[]
      So is my test script : only with this local declation.
      -check syntax is OK
      - running script : 

      test.au3" (13) : ==> Variable subscript badly formatted.: Local $mControls[] Local $mControls[^ ERROR ->14:51:49 AutoIt3.exe ended.rc:1
      any clue ?
    • By Blitzkid
      Hello, i want to search several directories for files with the largest numbers behind them (Like "video123") . They dont have a datatype. But there are also files with longer names and datatypes in these folders (Like "video778.mp4"). Is it possible to filter the _FileListToArray Syntax from
      to smth. like
       
      Here is my Code
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <array.au3> #include <File.au3> $filedir = @ScriptDir & "\" _checkfile() Func _checkfile() ConsoleWrite("______________________" & @CRLF) Local $arr[3] = ["music", "picture", "video"] For $i = 0 To UBound($arr) - 1 Local $arrayfiles = _FileListToArray($filedir & $arr[$i], $arr[$i] & "*", 1) If @error = 1 Then ConsoleWrite($arr[$i] & "Error 1") EndIf If @error = 4 Then ConsoleWrite($arr[$i] & "Error 2") ;Exit EndIf $arrayfilter = _ArrayMax($arrayfiles, 0, 1) Global $stringfiles = StringReplace($arrayfilter, $arr[$i], "") ConsoleWrite($arrayfilter & @CRLF) Next EndFunc ;==>_checkfile  
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
×
×
  • Create New...