Jump to content

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


Recommended Posts

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.

Link to comment
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.
Link to comment
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

Link to comment
Share on other sites

  • Moderators

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.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
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).

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

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
Link to comment
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.

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

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

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...