Jump to content
jamesinnewcastle

Paste from Clipboard into Excel - Harder than it looks?

Recommended Posts

Hi All

I have used my script to paste a string ($TestCSVGenerator) onto the clipboard - it has text seperated by tabs and at certain intervals there are CRLF. I am certain that this is a valid string because after I run my script (and it fails to do what I need) I can then manually 'Paste' the string into Excel using CTRL v and Excel puts the text in rows separated by the tab position and starts a new row of cells after a CRLF. (I make sure that the clipboard was empty before running my program).

All I need is a command that pastes the string into a certain cell and in theory the above should happen but it doesn't!

First I tried the following:

_Excel_RangeWrite ($oWorkbook, 1, $TestCSVGenerator, "C18", False, True)

This pastes, but everything goes in one cell! I've tried all the combinations of True/false

Next I tried:

_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, Default, "C18")

This pastes nothing at all (gives @error =4 ), if I define a range of cells Excel does highlight them.

 

Any ideas or alternative methods? I guess I could paste the text painfully cell by cell but Excel seems to understand what I have on the clipboard if I paste it manually - it's the automation that is flooring me!

 

Cheers

James

 

 

 

Share this post


Link to post
Share on other sites

Hi Again

After typing 'manually pasting' a couple of times in my question, and getting a cup of tea, I have just realised that I had forgotten the whole reason for using Autoit in the first place!! I wanted to play back key presses! So...

Send ("{CTRLDOWN}")
Send ("v")
Send ("{CTRLUP}")

Solves my problem! (Just hope that there is a 'move cursor to cell x,y' instruction)

Anyway I'm still interetsted as to why the other two methods didn't work.

 

James

 

 

Share this post


Link to post
Share on other sites

Arrgh

Can't find a quick way of putting the Excel cursor to a particular cell! Kinda back to square one! I could just use this:

_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, Default, "B16")

That moves the cursor to B16!

 

James

Share this post


Link to post
Share on other sites

_Excel_RangeWrite does not work this way. You provide a string and tell _Excel_RangeWrite to write it into a single-celled Range. _Excel_RangeWrite exactly does what it is being told to do ;)
Will come up with a solution how to insert data without automating the GUI!


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

Use this code to create an array out of the string and then use _Excel_RangeWrite.

#include <Array.au3>
Global $sString = "Row 1 Col 1" & @TAB & "Row 1 Col 2" & @CRLF & "Row 2 Col 1" & @TAB & "Row 2 Col 2"
Global $aTarget = _StringSplit2D($sString, @TAB)
_ArrayDisplay($aTarget)

; #FUNCTION# ======================================================================================
; Name ................:    _StringSplit2D($str, $delimiter)
; Description .........:    Create 2D array from delimited string
; Syntax ..............:    _DBG_StringSplit2D($str, $delimiter)
; Parameters ..........:    $str       - EOL (@CR, @LF or @CRLF) delimited string to split
;                           $delimiter - Delimter for columns
; Return values .......:    2D array
; Author ..............:    kylomas
; =================================================================================================
Func _StringSplit2D($str, $delimiter)

    Local $a1 = StringRegExp($str, '[^\r\n]+', 3)
    Local $rows = UBound($a1), $cols = 0
    ; determine max number of columns
    For $i = 0 To UBound($a1) - 1
        StringReplace($a1[$i], $delimiter, '')
        $cols = (@extended > $cols ? @extended : $cols)
    Next
    ; define and populate array
    Local $aRET[$rows][$cols + 1]
    For $i = 0 To UBound($a1) - 1
        $a2 = StringSplit($a1[$i], $delimiter, 3)
        For $j = 0 To UBound($a2) - 1
            $aRET[$i][$j] = $a2[$j]
        Next
    Next
    Return $aRET

EndFunc   ;==>_StringSplit2D

 

Edited by water
Code was missing

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

Hi Water

I couldn't see any code attached?

I actually had an array before I converted it to a string, I've been through all the same pain with the Array but I'm looking forward to seeing your suggestion!

 

James

Share this post


Link to post
Share on other sites

Added script above.
If you already have an array then there shouldn't be a problem with _Excel_RangeWrite. Which problems did you have?


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

I dont know if i got it right, but something like this maybe?

#include <Excel.au3>

Local $sWorkbook = "?" ;put the file name here ***************************

$oWorkbook = _Excel_BookAttach($sWorkbook, "filename")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$sString = "A1" & @CRLF & "A2" & @TAB & "B2" & @TAB & "C2" & @TAB & @CRLF & "A3" & @TAB & "B3" & @TAB & @CRLF & "A4"
ClipPut($sString)
$oWorkbook.Activesheet.Paste($oWorkbook.Activesheet.Range("A1"))
Exit

You need to open manually and attach or change the method to"_Excel_BookOpen".

Share this post


Link to post
Share on other sites

Hi Water

The same problem with pasting an array using  _Excel_RangeWrite in that it puts all the text into one cell. Seems odd to call it 'RangeWrite' if it only goes into a single cell - better name might be CellWrite?

When it does put all the text in one cell I can 'copy' the text in that cell (from the edit line at the top of the Excel window) and then 'paste' it into another cell on the sheet and hey presto it puts all the text into seperate cells! I was wondering if any 'copy special' stuff was the issue? As you say it looks as if _Excel_RangeWrite goes to a lot of effort to put everything into a single cell.

 

Cheers

James

Share this post


Link to post
Share on other sites
1 hour ago, jamesinnewcastle said:

Seems odd to call it 'RangeWrite' if it only goes into a single cell - better name might be CellWrite?

Did you try the example scripts that come with the help file for _Excel_RangeWrite? I know that the function can handle strings, 1D and 2D arrays.


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

Please post an example script (a stripped down reproducer of your problm) where you use an array.


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

Hi Water

Hi MichaelHB - sorry I missed your post.

Attached is my program - it all runs in one directory - you can see the path name, you may need to change it I guess. There are two ancilliary files that need to be there. The program reads in a file and tries to put it into the execl sheet on a one item per cell basis.

You should see that it does take notice of the CRLF in that the strings get put into cells one below each other but the TABs are ig nored. If you highlight a cell and copy the text from the Excel edit box at the top of the page and then paste that into another cell - then Excel does take note of the Tabs and put each part of the string into a different cell.

What I need is to have each chunk of text in its own cell. You can see what I want to do by looking at the Excell file - basically this program interrogates a database and pastes the results into Excel.

I've attached a file - hope it works - ther was an interesting 'autoit.zip' function when I zipped the files so I used that.

 

Cheers

JamesAuto It.zip

 

 

Edited by jamesinnewcastle

Share this post


Link to post
Share on other sites

Hmmmm

It eems that I have never understood csv and Excel!

It is completely correct that Excel 'pastes' csv files into single cells for each string up to a CRLF. So neither Autoit or Excel are doing anything wrong - looks like its just me again!! There is a command applied manually that will seperate the string apparently but I think it will be easier for me to do the paste cell by cell!

 

Thansk all

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 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
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
×
×
  • Create New...