Jump to content

[Solved] Excel - Copied cells to array


AnonymousX
 Share

Recommended Posts

Hello,

I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 

I don't want range to be specific to a workbook, or sheet, or set of cells.

I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 

Could someone help me with this?

Thank you,

I've tried to write the framework: (edited)

#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <Excel.au3>


HotKeySet("!v", "Pastedata")

While True
    Sleep(1000)
WEnd

func Makearray()

local $bArray

;User has cells already copied
;Convert clipboard into an array
    ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array


_Arraydisplay($bArray)
MsgBox(0,0,$bArray)

return $bArray
endfunc



func Pastedata()

Local $aArray
MsgBox(0,0,"wait",1)

;make array based on assumption user has already copied a range to clipboard
    $aArray = Makearray()

;paste code
    ;don;t worry about this I got the rest
endfunc

 

Edited by AnonymousX
Link to comment
Share on other sites

Use _Excel_RangeRead and specify the selected Range of cells:

$aSelectedCells = _Excel_RangeRead($oWorkbook, Default, $oExcel.Selection)

 

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

10 hours ago, water said:

Use _Excel_RangeRead and specify the selected Range of cells:

$aSelectedCells = _Excel_RangeRead($oWorkbook, Default, $oExcel.Selection)

 

Thank you, but the problem with this is that I don't have a specific workbook to declare. I was hoping to have it work with any excel workbook that is open. 

I've done a bit more looking into this and seems like maybe I need to be using the _Excel_BookAttach function, but doesn't seem to be working for me.

Could you give me a bit more of a hand? Thank you

 

#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <Excel.au3>

HotKeySet("!v", "Pastedata")

While True
    Sleep(1000)
WEnd

Func Makearray()

    Local $bArray

    Local $oExcel = _Excel_Open()
    If @error Then
        MsgBox(0, "Error", "Error creating Excel object")
        _Excel_Close($oExcel)
        Exit
    EndIf
    Local $oWorkbook = _Excel_BookAttach($oExcel)
    If @error Then
        MsgBox(0, "Error", "Error opening the workbook")
        _Excel_Close($oExcel)
        Exit
    EndIf

    ;User has cells already copied
    ;Convert clipboard into an array
    $bArray = _Excel_RangeRead($oWorkbook, Default, $oExcel.Selection)

    ;_ArrayDisplay($bArray)

    Return $bArray
EndFunc   ;==>Makearray



Func Pastedata()

    Local $aArray

    ;make array based on assumption user has already copied a range to clipboard
    $aArray = Makearray()


    _ArrayDisplay($aArray)
    MsgBox(0, 0, "done")
    

    ;don't worry about the rest
EndFunc   ;==>Pastedata

 

Link to comment
Share on other sites

@AnonymousX

Did you see at least which parameters you have to enter in the _Excel_BookAttach()?

_Excel_BookAttach

Attaches to the first instance of a workbook where the search string matches based on the selected mode

Parameters

$sString String to search for
$sMode [optional] specifies search mode:
    FileName - Name of the open workbook
    FilePath - Full path to the open workbook (default)
    Title - Title of the Excel window
$oInstance [optional] Object of the Excel instance to be searched (default = keyword Default = all instances)

Please, read carefully! :)

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

2 minutes ago, FrancescoDiMuro said:

@AnonymousX

Did you see at least which parameters you have to enter in the _Excel_BookAttach()?

_Excel_BookAttach

Attaches to the first instance of a workbook where the search string matches based on the selected mode

Parameters

$sString String to search for
$sMode [optional] specifies search mode:
    FileName - Name of the open workbook
    FilePath - Full path to the open workbook (default)
    Title - Title of the Excel window
$oInstance [optional] Object of the Excel instance to be searched (default = keyword Default = all instances)

Please, read carefully! :)

I did see that but don't have full understand. I thought I'd just leave the optional's as default, and give it the $oExcel variable, like I saw in someone else's example. 

I don't really know what I'm doing, I just keep trying to piece other peoples examples together from other forums and hope that I get the results I'm looking for. In this case I keep failing so I'm hoping to get some help. For someone with minimal programming experience it's not helpful to tell them just to look at the UDF pages and expect them to understand... 

I'm trying to find a way to reference a random workbook that maybe open, so I can use the function Water posted.  Every example I keep seeing seems to use

_Excel_BookOpen with the parameters $0Excel and the location of the file, but I don't have a file location because it needs to be more universal and work for any currently active excel sheet that has something copied.

 

Appreciate you replying to my thread, and I know you're trying to be helpful, however the responses feel more like your saying "Just figure it out yourself dummy." which is kind of annoying. If you know the solution please just tell me  

Link to comment
Share on other sites

@AnonymousX

I want to help you, as I try to do with everyone who asks for help :)

I never meant to insult or offend you in any way, and please, if you feel like that, my apologizes.

The thing is, that I prefer to give you some hints to help you, but, at the same time, let you understand and learn from what you're reading ( "Give a man a fish, and you feed him for a day; teach a man to fish, and you feed him for a lifetime" ).

So, talking about what you're asking for, let me ask you a couple questions :)

Do you want to make a script which let you have what the user has copied from an Excel woorkbook, is it right?

Could this thing be automated, or it needs to have user interaction? 

Cheers :)

 

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

Or you could try _Excel_BookList. This returns an array holding all open Excel workbooks. The value in "cell" 0 of every row holds the workbooks object. Which is the same as returned by _Excel_BookAttach or _Excel_BookNew.

BTW: _Excel_BookAttach has one mandatory and two optional parameters ;)

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

5 minutes ago, FrancescoDiMuro said:

@AnonymousX

I want to help you, as I try to do with everyone who asks for help :)

I never meant to insult or offend you in any way, and please, if you feel like that, my apologizes.

The thing is, that I prefer to give you some hints to help you, but, at the same time, let you understand and learn from what you're reading ( "Give a man a fish, and you feed him for a day; teach a man to fish, and you feed him for a lifetime" ).

So, talking about what you're asking for, let me ask you a couple questions :)

Do you want to make a script which let you have what the user has copied from an Excel woorkbook, is it right?

Could this thing be automated, or it needs to have user interaction? 

Cheers :)

 

 

Thanks mate, I understand the fishing philosophy however sometimes you need to give the man the fish to show him how you did it. Otherwise it just feels frustrating that I've got my line in the water and nothing is happening, and I don't know why I'm not getting the fish.

 

To answer your questions, no there definitely needs to be user interaction. Either in by user having range selected already or by user having range copied already, either would work. Basically I'm copying random ranges and data, and pasting them somewhere else that is also random, that has individual feels. So I can't paste in a range I need to have split into individual elements and paste 1 by one. I have the 2nd half figured out to paste information in instantaneously if I have an array. However I don't know how to get the initial data into that array form. The excel sheets aren't save to a specific location, don't have a specific name, there is no automation here. It's just a mater of what do the user happen to have open, and what have they happen to copy, or need to copy. 

So I just need the code that can identify the selected range of a random open workbook, and turn it into an array variable. I've already spent a bunch of time trying to look at other forums and everything always just shows how to do it if you're creating a new workbook, or have a known workbook, but nothing about if it's just a random unknown workbook that happens to be open. 

 

I think I need something like:

;1. $oWorkbook = active excel window

;2. Array equals excel reading selected range

 

Water gave me the 2nd piece, now I just need to know how to do the first piece

Link to comment
Share on other sites

@AnonymousX

In fct, you could use a combination of:

  • _Excel_BookList() to get a list of Woorkbooks opened, getting the object, the name of the workbook, and the full path of the workbook opened, as water suggested;
  • _Excel_RangeRead() to get data from the current selected range, as water suggested.

But, if you want to get data from the clipboard ( if the user copies data with Ctrl+C, for example ), you have to work with other functions: Clip* and _Clip* functions.

Since your application does a lot of "random things", it is difficult to have a "strandard" approach.

Maybe water has more specific hints for you :)

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

Here is one way to use Clipboard Excel Data into an array

#include <Array.au3>

Local $sExcelData = ClipGet()
    $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2)
Local $aExcel1D = StringSplit($sExcelData, @LF, 2)
If IsArray($aExcel1D) Then
    If StringInStr($sExcelData, "|") Then
        Local $aExcel2D[0][0]
        For $i = 20 To 0 Step - 1
            If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop
            $aExcelLine = StringSplit($aExcel1D[$i], "|", 2)
            ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)]
            _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine))
        Next
    Else
        Local $aExcel2D = $aExcel1D
    EndIf
EndIf
_ArrayDisplay($aExcel2D)

 

Link to comment
Share on other sites

9 minutes ago, Subz said:

Here is one way to use Clipboard Excel Data into an array

#include <Array.au3>

Local $sExcelData = ClipGet()
    $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2)
Local $aExcel1D = StringSplit($sExcelData, @LF, 2)
If IsArray($aExcel1D) Then
    If StringInStr($sExcelData, "|") Then
        Local $aExcel2D[0][0]
        For $i = 20 To 0 Step - 1
            If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop
            $aExcelLine = StringSplit($aExcel1D[$i], "|", 2)
            ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)]
            _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine))
        Next
    Else
        Local $aExcel2D = $aExcel1D
    EndIf
EndIf
_ArrayDisplay($aExcel2D)

 

HEY SUBZ!!!

Thanks so much!

It seems to break if I have a 2D array copied, but it's good enough for me to work with.  

Appreciate how you always have what I need!

Link to comment
Share on other sites

Whoops sorry, I was testing something before I posted and forgot to revert it back, here is the correct code for 2d.

#include <Array.au3>

Local $sExcelData = ClipGet()
    $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2)
Local $aExcel1D = StringSplit($sExcelData, @LF, 2)
If IsArray($aExcel1D) Then
    If StringInStr($sExcelData, "|") Then
        Local $aExcel2D[0][0]
        For $i = (UBound($aExcel1D) - 1) To 0 Step - 1
            If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop
            $aExcelLine = StringSplit($aExcel1D[$i], "|", 2)
            ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)]
            _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine))
        Next
    Else
        Local $aExcel2D = $aExcel1D
    EndIf
EndIf
_ArrayDisplay($aExcel2D)

 

Link to comment
Share on other sites

Just now, Subz said:

Whoops sorry, I was testing something before I posted and forgot to revert it back, here is the correct code for 2d.

#include <Array.au3>

Local $sExcelData = ClipGet()
    $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2)
Local $aExcel1D = StringSplit($sExcelData, @LF, 2)
If IsArray($aExcel1D) Then
    If StringInStr($sExcelData, "|") Then
        Local $aExcel2D[0][0]
        For $i = (UBound($aExcel1D) - 1) To 0 Step - 1
            If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop
            $aExcelLine = StringSplit($aExcel1D[$i], "|", 2)
            ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)]
            _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine))
        Next
    Else
        Local $aExcel2D = $aExcel1D
    EndIf
EndIf
_ArrayDisplay($aExcel2D)

 

Thanks mate!

Link to comment
Share on other sites

Btw: There is no need to quote every post. That just clutters the thread. 

Add your text at the end of the page and click Submit Reply. 

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

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

×
×
  • Create New...