Sign in to follow this  
Followers 0
ssnake

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

14 posts in this topic

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.

Share this post


Link to post
Share on other sites



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


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

_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

Share this post


Link to post
Share on other sites

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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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
1 person likes this

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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

1 person likes this

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, 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 :D. Good to read that

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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

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
Sign in to follow this  
Followers 0