[Solved] Excel - Copied cells to array

[AnonymousX]

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)

expandcollapse popup

#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 September 27, 2018 by AnonymousX

[FrancescoDiMuro]

@AnonymousX

You can do it using Excel UDF

Did you take a look at it?

[water]

Use _Excel_RangeRead and specify the selected Range of cells:

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

 

[AnonymousX]

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

 

expandcollapse popup

#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

 

[FrancescoDiMuro]

@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!

[AnonymousX]

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  

[FrancescoDiMuro]

@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

 

[water]

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

[AnonymousX]

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

[FrancescoDiMuro]

@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

[Subz]

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)

 

[AnonymousX]

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!

[Subz]

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)

 

[AnonymousX]

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!

[water]

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. 

[AnonymousX]

@water

Thanks for the tip, I agree it does make a ton of clutter.

I really like what FrancescoDiMuro was doing using the @_____ to reference someone

Edited September 28, 2018 by AnonymousX