Jump to content
AnonymousX

[Solved] Excel - Copied cells to array

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

Share this post


Link to post
Share on other sites

@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

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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-11-07 - Version 1.3.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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

 

Share this post


Link to post
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

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

@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

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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-11-07 - Version 1.3.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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

Share this post


Link to post
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

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

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)

 

Share this post


Link to post
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!

Share this post


Link to post
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)

 

Share this post


Link to post
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!

Share this post


Link to post
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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (NEW 2019-11-07 - Version 1.3.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@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

    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • By sksbir
      Hi
      Trying this from autoit v3.3.14.5 and SCITE 3.19.102.1901.0 :
      help file , page "Language Reference - Variables" , sample autoit script about maps:

      Maps must be declared before use by defining their scope using the 'Global/Local/Static' keywords. Local $mControls[]
      So is my test script : only with this local declation.
      -check syntax is OK
      - running script : 

      test.au3" (13) : ==> Variable subscript badly formatted.: Local $mControls[] Local $mControls[^ ERROR ->14:51:49 AutoIt3.exe ended.rc:1
      any clue ?
    • By Blitzkid
      Hello, i want to search several directories for files with the largest numbers behind them (Like "video123") . They dont have a datatype. But there are also files with longer names and datatypes in these folders (Like "video778.mp4"). Is it possible to filter the _FileListToArray Syntax from
      to smth. like
       
      Here is my Code
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <array.au3> #include <File.au3> $filedir = @ScriptDir & "\" _checkfile() Func _checkfile() ConsoleWrite("______________________" & @CRLF) Local $arr[3] = ["music", "picture", "video"] For $i = 0 To UBound($arr) - 1 Local $arrayfiles = _FileListToArray($filedir & $arr[$i], $arr[$i] & "*", 1) If @error = 1 Then ConsoleWrite($arr[$i] & "Error 1") EndIf If @error = 4 Then ConsoleWrite($arr[$i] & "Error 2") ;Exit EndIf $arrayfilter = _ArrayMax($arrayfiles, 0, 1) Global $stringfiles = StringReplace($arrayfilter, $arr[$i], "") ConsoleWrite($arrayfilter & @CRLF) Next EndFunc ;==>_checkfile  
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
×
×
  • Create New...