Reading Spreadsheet Row

[audioman]

I'm new to the forum but have been using Autoit for several years. I am now, for the first time, trying to work with Excel with much frustration. I've visited the forums and found code which should work but doesn't. I need a script which stores a single row selected by the user and stores it in an array. The script will then use the stored information in another application. I can write a script which will read the array so that's not the problem, it's getting the values into the array. I'm using the script below which uses code I found in these forums. The code runs without errors but the array apparently contains no data. Can anyone tell me what I'm doing wrong?

NBP-TestScript.au3

[faustf]

can you  try for me  to use Xls not  xlsx , do  the same effect?

[faustf]

if  you use  this  "A:J")

you must use  3° example in help F1

; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

 

[audioman]

Thanks for your reply. I did try changing the spreadsheet and script to .xls and the script stopped at script the  dir line (line 19)

[faustf]

can you attach also a file xls?

 

Edited April 22, 2020 by faustf

[faustf]

forr  me  work

#include <MsgBoxConstants.au3>
#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
Dim $aArray[20]
Local $oExcel = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\SpreadsheetTest.xls"
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

#cs
$aArray = _Excel_RangeRead($oWorkbook, Default, "A:J")
For $i = 0 to UBound($aArray) -1
    MsgBox($MB_SYSTEMMODAL, "Test", $aArray[$i])
Next
#ce

; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:J"), 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A")

 

[Subz]

The issue with your script is that your using @ScriptDir & "C:\Users\SpreadsheetTest.xlsx", so for example if your script was in C:\Scripts then your path would be "C:\ScriptsC:\Users\SpreadsheetTest.xlsx" as @faustf pointed out use @ScriptDir & "\SpreadsheetTest.xlsx" (assuming SpreadsheetTest.xlsx is the same folder as your script).

When referencing 2d items remember to use $aArray[row][col] (both offset by - 1)

Basic example:

#include <Array.au3>
#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\SpreadsheetTest.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:J"))
;~ If your spreadsheet is quite large than use the following to get UsedRanges A:J values:
;~ Local $aArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:J"), 1, True)
Local $iRow, $sRow
For $i = 0 To UBound($aArray) - 1
    $sRow = ""
    $iRow = $i + 1
    For $j = 0 To UBound($aArray, 2) - 1
        $sRow &= _Excel_ColumnToLetter($j + 1) & $iRow & " = " & $aArray[$i][$j] & @CRLF
    Next
    MsgBox(4096, "SpreadsheetTest", $sRow, 5)
Next
_ArrayDisplay($aArray)

 

Edited April 23, 2020 by Subz

[audioman]

First, thanks Subz, I didn't really understand the function of @ScriptDir. I've made some modifications to the script and it runs. However, _ArrayDisplay displays the entire spreadsheet. I just wanted to capture one row selected by the user. The values from that row would then be sent to another application (software to print sections of a book). When ready to print the next book, the user would then move to the next row and repeat the process. So, you can see that loading the entire spreadsheet into an array serves no purpose in this case.

Thanks again for your help. I just need to solve this one remaining problem

NBP-TestScript2.au3

[Subz]

It depends on what you mean by "capture one row selected by the user"

You could ask the user to enter the row number something like:

#include <Array.au3>
#include <Excel.au3>
Global $g_iSelectRow
Global $g_oExcel = _Excel_Open()
Global $g_sWorkbook = @ScriptDir & "\SpreadsheetTest.xlsx"
Global $g_oWorkbook = _Excel_BookOpen($g_oExcel, $g_sWorkbook)
_SelectRow()
Global $g_aArray = _Excel_RangeRead($g_oWorkbook, Default,"A" & $g_iSelectRow & ":J" & $g_iSelectRow)
Global $g_sRow = ""
For $i = 0 To UBound($g_aArray, 2) - 1
    $g_sRow &= _Excel_ColumnToLetter($i + 1) & $g_iSelectRow & " = " & $g_aArray[0][$i] & @CRLF
Next
MsgBox(4096, "SpreadsheetTest", $g_sRow, 5)
_ArrayDisplay($g_aArray)

Func _SelectRow()
    Local $iSelectRow = InputBox("Excel Selection", "Please enter row number", 1)
        ;~ Check if an error occurred
        If @error Then Exit MsgBox(4096, "Input Error", "Error occurred, exiting script.")
        ;~ Check if the input equals 0, ask user to re-enter row number
        If Int($iSelectRow) = 0 Then Return _SelectRow()
    $g_iSelectRow = $iSelectRow
EndFunc

 

[audioman]

Hi Subz and thanks for your input. I couldn't get it to work but to answer your question, the user will simply put the cursor on the cell in the first column of any row and then execute the script. Next, the script will send the resulting array values to another application. So my concern at the moment is to be able to put the cursor on the first cell of any row and use the script to grab the cell values in that row. I just wanted to use _ArrayDisplay to verify that the data is in the array. _ArrayDisply won't be used in the final script. Hope this helps and thanks again.

[Nine]

I think maybe this is what you are looking for :

#include <Constants.au3>
#include <Excel.au3>

Opt ("MustDeclareVars", 1)

HotKeySet ("!{HOME}", _SelectRow)
HotKeySet ("{ESC}", _Exit)

Global $oExcel = _Excel_Open()
Global $sWorkbook = @ScriptDir & "\Test.xls"
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

While True
  Sleep (100)
WEnd

Func _SelectRow()
  Local $sActiveCell = $oExcel.ActiveCell.Address
  ConsoleWrite ($sActiveCell & @CRLF)
  Local $oRange = $oWorkBook.Activesheet.UsedRange.Rows($oExcel.ActiveCell.row)
  Local $aArray = _Excel_RangeRead ($oWorkbook, $oWorkBook.Activesheet, $oRange, 1)
  _ArrayDisplay ($aArray)
EndFunc

Func _Exit ()
  _Excel_Close ($oExcel)
  Exit
EndFunc

User clicks on a cell and press Alt+Home, et voilà, the current row in an array.

You could make a small GUI if you prefer, I went for the simple solution 

[audioman]

Thank you Subz and Nine, you guys are fantastic. You've given me lots to work with. I'll be back in touch when I get further along with this.

[audioman]

Hi folks:

Using either of the scripts provided by Subz and Nine, I need to grab individual values from the array. In the case of the script by Subz, I believe I'll find the value of the string in the first column of the selected row at $g_aArray[0][0] and the second column value at $g_aArray[0][1] Am I correct?

[faustf]

Multi Dimensional Arrays

Now what is a good explanation of a multi-dimensional array? It could be a table where you access one item in the table at a time.

Local  $arr[3][3] = [[1, 2, 3], [2, 3, 4], [3, 4, 5]]

For $i = 0 to UBound( $arr, 1) - 1
    For $j = 0 to UBound($arr, 2) - 1
        ConsoleWrite("$arr[" & $i & "][" & $j & "]:=" & $arr[$i][$j] & @LF)
    Next 

    ConsoleWrite(@LF)
Next

You can add a number of dimensions not to exceed sixty-four as stated in the help file section AutoIt3 limits/Defaults.

Here is a four dimensional example. You tell me how that initializer is for readability.

 
; NOTE: The following is supposed to be all on one line
; but we use the "_" character to split it into multiple lines for readability

Local $arr[3][3][3][3] = _
		[ _
		[ _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]] _
		], _
		[ _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]] _
		], _
		[ _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]], _
		[[1, 2, 3], [2, 3, 4], [3, 4, 5]] _
		] _
		]

For $i = 0 To UBound($arr, 1) - 1
	For $j = 0 To UBound($arr, 2) - 1
		For $k = 0 To UBound($arr, 3) - 1
			For $l = 0 To UBound($arr, 4) - 1
				ConsoleWrite("$arr[" & $i & "][" & $j & "][" & $k & "][" & $l & "]:=" & $arr[$i][$j][$k][$l] & @LF)
			Next
		Next
	Next
Next

ConsoleWrite(@LF)

 

look here  https://www.autoitscript.com/wiki/Arrays

 

[audioman]

First, the information you all have provided has been extremely helpful. Thank you. In the past I've used Autoit to fill an array with data in an MS Word document which was open on the desktop. Then, using WinActivate(applicationName) the script changed the focus to another application on the desktop and loaded the individual array values into that application. As I said, that has worked well in the previous scripts I've written. With this script, the focus does change as expected but It doesn't transfer the array values and I don't know why. Any ideas?

[faustf]

if  post   with tag  <> code   your code  the  comunity will be happy  help you

[audioman]

I solved my problem! I needed to run the program as administrator and was trying to avoid recompiling the script every time I made a change. I finally gave up on that and am now doing a compile after each change. That's a pain but it solves the problem.

[audioman]

I'm trying  to find a way to get the script written by Subz to run without the row number query. In other words the user selects the desired row with the mouse and then runs the compiled script. I haven't found any code in the forum that does that and code like "$oExcel.ActiveCell.Row" doesn't work. Any ideas?

[faustf]

post the code and  use a tag

[Nine]

@audioman  Did you even look at my example ???  It precisely does what you want to do !!!

I made it so the user just do a hotkey instead of starting the script at every selection.

Edited May 8, 2020 by Nine