Jump to content
AnonymousX

[Solved] Excel - Copied cells to array

Recommended Posts

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)

#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

Share this post


Link to post
Share on other sites
FrancescoDiMuro

@AnonymousX

You can do it using Excel UDF :)

Did you take a look at it?


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

 

#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

 

Share this post


Link to post
Share on other sites
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! :)

  • Confused 1

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


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

Share this post


Link to post
Share on other sites
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 :)

 


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
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 :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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)

 

  • Like 1

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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)

 

  • Like 1

Share this post


Link to post
Share on other sites
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!

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by AnonymousX

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

  • Similar Content

    • Epic007
      By Epic007
      Hey guys I'm stuck with a program.....I need a make an automation which copies text from a chat bot app and paste it in notepad to log the replies of my chat bot and to analyse stuff....a lil help pls
       
       
       
       
       
       
       
      Thanks in advance
    • yasha
      By yasha
      i want am trying to select a nimber to run a program and then select where to save the excel result at before hand
      the problem is that it does not save in the folder i want but the folder before any solutions
      #.................
      $sFolder = ""
          ; Create a constant variable in Local scope of the message to display in FileSelectFolder.
          Local Const $sMessage = "Select a folder"
          ; Display an open dialog to select a file.
          $sFileSelectFolder = FileSelectFolder($sMessage, $sFolder)
          If @error Then
              ; Display the error message.
              MsgBox($MB_SYSTEMMODAL, "", "No folder was selected.")
          Else
              ; Display the selected folder.
              MsgBox($MB_SYSTEMMODAL, "", "You chose the following folder:" & @CRLF & $sFileSelectFolder)
          EndIf
      .......
      ........
      $oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
      $oExcel.Visible = 1                                        ; Let Excel show itself
      $oExcel.Workbooks.Open("J:\OPS\OPS_Share\Planners\2 - Weekly Reports\Auto download\"& $YY & $MM & $DD & " ORDER.xls",0)
      $oExcel.ActiveWorkbook.Saveas ( $sFileSelectFolder,""& $YY & $MM & $DD & " ORDER.xlsx", 1)
      $oExcel.ActiveWorkBook.Close
      $oExcel.Quit
      i only want to save it as ddmmyy order inside documents but it saves in libraries as documents ddmmyy order.
    • PiyushJhawar
      By PiyushJhawar
      I am part of QA team of an analytics application. We support third party tools like Excel , Tableue .
      I have to write automation script that connect Excel to our analytics application. In short i want below to automate
      > Open Excel
      > Click on "Data" option available in header and then click on "From Other Services " then click on "From Analysis Services"
      > It will open pop up and then need to write username password there.
      I am new in this tool . Can any one please provide me link of any document that help me to create above script
    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • nooneclose
      By nooneclose
      My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.
      This is what I have so far.
      Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill") _ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") _Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)  
      Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 
×