[Question] Theres a way to use a "selection" in excel to create an array?

[ssnake]

Hi guys.

Sorry for the stupid question, but: Whats the best way to get a selection from a sheet in excel (previously selected using the mouse), and create an array with the selected content?

Note that the range and the content may vary.

I know that is possible to copy the whole sheet to an array using _ExcelReadSheetToArray, but my question is about the user selection.

Thanks, and sorry for any inconvenience.

[jdelaney]

_ExcelReadSheetToArray($oExcel [, $iStartRow = 1 [, $iStartColumn = 1 [, $iRowCnt = 0 [, $iColCnt = 0 [, $iColShift = False]]]]])

if you include the $iRowCnt, and $iColCnt variables (as non-zeros), it will limit the selection.

Check out the 'example' section of the help file for the _excelreadsheettoarray function.

$aArray = _ExcelReadSheetToArray($oExcel, 2, 3, 4, 5) ;Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns

_ArrayDisplay($aArray, "Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns")

[ssnake]

_ExcelReadSheetToArray($oExcel [, $iStartRow = 1 [, $iStartColumn = 1 [, $iRowCnt = 0 [, $iColCnt = 0 [, $iColShift = False]]]]])

if you include the $iRowCnt, and $iColCnt variables (as non-zeros), it will limit the selection.

Check out the 'example' section of the help file for the _excelreadsheettoarray function.

$aArray = _ExcelReadSheetToArray($oExcel, 2, 3, 4, 5) ;Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns

_ArrayDisplay($aArray, "Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns")

Ohh yeah, I understand that, but I dont get it on how to determine (by the user selection) the $iRowCnt, and $iColCnt.

I mean, I dont know how to get the "range" and use it as a variable in the script.

I think that is a bit confuse to explain

[JLogan3o13]

You can also use _ExcelReadArray if you want just a single row or column. Not sure if anything in the Excel UDF would amount to the same as the Worksheets("Sheet 1").Range object, letting you grab A1:K12, for example.

[boogieoompa]

I dont know if the UDF has a function that can get what the user selected but cant you activate excel, copy it and than ClipGet() it? You can than parse it into an array... I think they are delimited by tabs (and @crlf for next line, or next array).

[boogieoompa]

This is a 1/2 way working example but I think you get the gist. Basically if you _arraydisplay($parsed_by_row) you will get each row, if you _ArrayDisplay($parsed_by_column) (which is in the loop currently) you will get each row broken out. There is the unintentonal effect of having an extra array populate when using @crlf but it is mentioned in the helpfile under stringsplit so my guess is there is example code on the forum to fix that.

#include <Array.au3>
;put code here to activate your excel document and send a ^c
Local $parsed_by_row
Local $parsed_by_column
$excel_string = ClipGet()
;;;;seperates each row by looking for linecarriages... right now I'm getting 2... not sure why
$parsed_by_row = StringSplit($excel_string,@crlf)
$i = 1
While $i < UBound($parsed_by_row)
 
 $parsed_by_column = StringSplit($parsed_by_row[$i],@tab)
 $i = $i+1
 _ArrayDisplay($parsed_by_column)
WEnd

[jdelaney]

Here, modified the _excelreadsheettoarray to use current selection:

Func _ExcelReadSelectionToArray($oExcel)
 Local $avRET[1][2] = [[0, 0]] ; 2D return array
;~  $iRowStart = $oExcel.Application.Selection.Rows(1).Row
;~  $iColStart = $oExcel.Application.Selection.columns(1).Column
 $iRowCnt = $oExcel.Application.Selection.rows.count
 $iColCnt = $oExcel.Application.Selection.columns.count
;~  ConsoleWrite($iRowStart & @CRLF)
;~  ConsoleWrite($iColStart & @CRLF)
 ConsoleWrite($iRowCnt & @CRLF)
 ConsoleWrite($iColCnt & @CRLF)
 ; Size the return array
 ReDim $avRET[$iRowCnt][$iColCnt]
  For $r = 0 To $iRowCnt-1
   For $c = 0 To $iColCnt-1
    $avRET[$r][$c] = $oExcel.Application.Selection.Cells($r+1,$c+1).Value
   Next
  Next
 ;Return data
 Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

[water]

That's a very good idea! I will add this to the _Excel_RangeRead function of my ExcelEX UDF.

[ssnake]

Here, modified the _excelreadsheettoarray to use current selection:

Func _ExcelReadSelectionToArray($oExcel)
Local $avRET[1][2] = [[0, 0]] ; 2D return array
;~ $iRowStart = $oExcel.Application.Selection.Rows(1).Row
;~ $iColStart = $oExcel.Application.Selection.columns(1).Column
$iRowCnt = $oExcel.Application.Selection.rows.count
$iColCnt = $oExcel.Application.Selection.columns.count
;~ ConsoleWrite($iRowStart & @CRLF)
;~ ConsoleWrite($iColStart & @CRLF)
ConsoleWrite($iRowCnt & @CRLF)
ConsoleWrite($iColCnt & @CRLF)
; Size the return array
ReDim $avRET[$iRowCnt][$iColCnt]
For $r = 0 To $iRowCnt-1
For $c = 0 To $iColCnt-1
    $avRET[$r][$c] = $oExcel.Application.Selection.Cells($r+1,$c+1).Value
Next
Next
;Return data
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

I owe you a beer.

That's a very good idea! I will add this to the _Excel_RangeRead function of my ExcelEX UDF.

Nice . Good to read that

[ssnake]

Here, modified the _excelreadsheettoarray to use current selection:

Func _ExcelReadSelectionToArray($oExcel)
Local $avRET[1][2] = [[0, 0]] ; 2D return array
;~ $iRowStart = $oExcel.Application.Selection.Rows(1).Row
;~ $iColStart = $oExcel.Application.Selection.columns(1).Column
$iRowCnt = $oExcel.Application.Selection.rows.count
$iColCnt = $oExcel.Application.Selection.columns.count
;~ ConsoleWrite($iRowStart & @CRLF)
;~ ConsoleWrite($iColStart & @CRLF)
ConsoleWrite($iRowCnt & @CRLF)
ConsoleWrite($iColCnt & @CRLF)
; Size the return array
ReDim $avRET[$iRowCnt][$iColCnt]
For $r = 0 To $iRowCnt-1
For $c = 0 To $iColCnt-1
    $avRET[$r][$c] = $oExcel.Application.Selection.Cells($r+1,$c+1).Value
Next
Next
;Return data
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

I am trying to identify an error in the function.

Its returning only the last cell of the current selection.

[jdelaney]

Are you doing multiple selections at once (that won't work)? Something like this would be needed, and an array of arrays needs to be returned:

Sub x()
    Dim intArea As Integer
    Dim rngCell As Range
    
    For intArea = 1 To Selection.Areas.Count
        For Each rngCell In Selection.Areas(intArea).Cells
            Debug.Print rngCell.Address
        Next
    Next
End Sub

I'm not able to reproduce.

Would you post back the ConsoleWrite outputs of the function (I added some)?

Func _ExcelReadSelectionToArray($oExcel)
If Not isObj ($oExcel) Then
   consolewrite ("invalid excel object" & @crlf)
   Return False
EndIf

Local $avRET[1][1]
$iRowStart = $oExcel.Application.Selection.Rows(1).Row
$iColStart = $oExcel.Application.Selection.columns(1).Column
$iRowCnt = $oExcel.Application.Selection.rows.count
$iColCnt = $oExcel.Application.Selection.columns.count
ConsoleWrite($iRowStart & @CRLF)
ConsoleWrite($iColStart & @CRLF)
ConsoleWrite($iRowCnt & @CRLF)
ConsoleWrite($iColCnt & @CRLF)
; Size the return array
ReDim $avRET[$iRowCnt][$iColCnt]
For $r = 1 To $iRowCnt
For $c = 1 To $iColCnt
$avRET[$r-1][$c-1] = $oExcel.Application.Selection.Cells($r,$c).Value
Next
Next
ConsoleWrite("Ubound1=" & UBound($avRET) & @CRLF)
ConsoleWrite("Ubound2=" & UBound($avRET,2) & @CRLF)
;Return data
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

Edited April 26, 2013 by jdelaney

[ssnake]

Are you doing multiple selections at once (that won't work)? Something like this would be needed, and an array of arrays needs to be returned:

Sub x()
    Dim intArea As Integer
    Dim rngCell As Range
    
    For intArea = 1 To Selection.Areas.Count
        For Each rngCell In Selection.Areas(intArea).Cells
            Debug.Print rngCell.Address
        Next
    Next
End Sub

I'm not able to reproduce.

Would you post back the ConsoleWrite outputs of the function (I added some)?

Func _ExcelReadSelectionToArray($oExcel)
If Not isObj ($oExcel) Then
consolewrite ("invalid excel object" & @crlf)
Return False
EndIf

Local $avRET[1][1]
$iRowStart = $oExcel.Application.Selection.Rows(1).Row
$iColStart = $oExcel.Application.Selection.columns(1).Column
$iRowCnt = $oExcel.Application.Selection.rows.count
$iColCnt = $oExcel.Application.Selection.columns.count
ConsoleWrite($iRowStart & @CRLF)
ConsoleWrite($iColStart & @CRLF)
ConsoleWrite($iRowCnt & @CRLF)
ConsoleWrite($iColCnt & @CRLF)
; Size the return array
ReDim $avRET[$iRowCnt][$iColCnt]
For $r = 1 To $iRowCnt
For $c = 1 To $iColCnt
$avRET[$r-1][$c-1] = $oExcel.Application.Selection.Cells($r,$c).Value
Next
Next
ConsoleWrite("Ubound1=" & UBound($avRET) & @CRLF)
ConsoleWrite("Ubound2=" & UBound($avRET,2) & @CRLF)
;Return data
Return $avRET
EndFunc ;==>_ExcelReadSheetToArray

No, not multiple selections, but a range (ex: A1:E25). The function returns only the E25 as Array[1][1]

[jdelaney]

I have lots of consolewrites, above...please run with the new function, and copy the console output here.

example...i ran with selection a1:e25, and had this output:

1

1

25

5

Ubound1=25

Ubound2=5

Edited April 26, 2013 by jdelaney

[ssnake]

I have lots of consolewrites, above...please run with the new function, and copy the console output here.

example...i ran with selection a1:e25, and had this output:

1

1

25

5

Ubound1=25

Ubound2=5

The Output for a selection from A1 to D12:

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\tbrfdx\Desktop\lab.au3"
1
1
12
4
Ubound1=12
Ubound2=4

Note that I also used a _ExcelWriteSheetFromArray to print the array to a sheet, and the output was the selection, except for the first row and column (from B2 to D12)

Edited April 26, 2013 by ssnake