jamesinnewcastle

Paste from Clipboard into Excel - Harder than it looks?

14 posts in this topic

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Sorry - been out of the office - will try to post a reply tomorrow - might take a while to strip down the code.

The example scripts didn't quite do what I needed.

 many thanks

James

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

    • SkysLastChance
      By SkysLastChance
      Is there an easy way to format a cell to date or currency? I found some old threads that were not much help. 
      I have looked at the wiki but I only see how to format as a number
      I assume there is not a way like I did for making the text bold. 
      Any suggest or help would be appreciated. 
       
       
      I am able to get the format changed to text using
      $oExcel.Activesheet.range("A1:L1").NumberFormat = "@"  
      #include <Excel.au3> Global $sBox Func InputHowMany() While 1 $sBox = Number(InputBox("How many?", "How many?")) If $sBox = 0 Then $iMsg = MsgBox(1, 'Oops', 'Please enter a valid number') If $iMsg = 2 Then Exit Else Return $sBox - 1 EndIf WEnd EndFunc InputHowMany() $oExcel = _Excel_Open() $oExcel = _Excel_BookNew($oExcel) Local $t = 2 Local $w = 1 Local $c = 301 $oExcel.Activesheet.range("A1:L1").font.bold = True Do _Excel_RangeWrite($oExcel,Default, "Status", "A1") _Excel_RangeWrite($oExcel,Default, "Last Name", "B1") _Excel_RangeWrite($oExcel,Default, "Last Name", "C1") _Excel_RangeWrite($oExcel,Default, "SSN", "D1") _Excel_RangeWrite($oExcel,Default, "DOB", "E1") _Excel_RangeWrite($oExcel,Default, "Email", "F1") _Excel_RangeWrite($oExcel,Default, "Mailing Address", "G1") _Excel_RangeWrite($oExcel,Default, "City", "H1") _Excel_RangeWrite($oExcel,Default, "State", "I1") _Excel_RangeWrite($oExcel,Default, "Zip Code", "J1") _Excel_RangeWrite($oExcel,Default, "Gender", "K1") _Excel_RangeWrite($oExcel,Default, "Phone", "L1") _Excel_RangeWrite($oExcel,Default, '=B' & $c, "B" & $t) _Excel_RangeWrite($oExcel,Default, '=C' & $c, "C" & $t) _Excel_RangeWrite($oExcel,Default, '=D' & $c, "D" & $t) _Excel_RangeWrite($oExcel,Default, '=E' & $c, "E" & $t) _Excel_RangeWrite($oExcel,Default, '=F' & $c, "F" & $t) _Excel_RangeWrite($oExcel,Default, '=G' & $c, "G" & $t) _Excel_RangeWrite($oExcel,Default, '=H' & $c, "H" & $t) _Excel_RangeWrite($oExcel,Default, '=I' & $c, "I" & $t) _Excel_RangeWrite($oExcel,Default, '=J' & $c, "J" & $t) _Excel_RangeWrite($oExcel,Default, '=K' & $c, "K" & $t) _Excel_RangeWrite($oExcel,Default, '=L' & $c, "L" & $t) $c = $c + 1 $t = $t + 1 $w = $w + 1 Until $w > $sBox  
       
       
    • mmoalem
      By mmoalem
      I seem to have an issue with clipget() - the following bit of code copy URL from chrome address bar - than using clipget() i try to grab the URL into a variable and input it into a spreadsheet. the script did not put anything into the sheet 
      originally I though it was an issue with  OOo/LibO Calc UDF and posted it there :
       
      than I tried writing $NewURL into a text file and than just a msgbox but it comes blank. (the URL is in the clipboard as i can paste it)
       
      send ("!d") ;select URL in browser send ("^c") ;copy selected URL Local $NewURL = ClipGet() MsgBox($MB_ICONINFORMATION + $MB_TOPMOST, $sTitle, $NewURL ) am I doing something wrong? is there an issue with a URL characters?
    • tcurran
      By tcurran
      Here's a short UDF that will, at least in most cases, detect whether a window can be copied from or pasted to programmatically--for example, by Send()ing ctl-c, ctl-v. This is often disabled when programs (like your AutoIt script) run at a lower UAC integrity level than the application they are trying to operate on.
      #include <WinAPI.au3> Func _WindowIsPasteable($handle) ;accepts window handle; returns true or false whether a window will accept Ctl-C, Ctl-V Local $bCanPaste = True Local $hTestWindowPID = 0 Local $hTestWindowTID = _WinAPI_GetWindowThreadProcessId($handle, $hTestWindowPID) _WinAPI_AttachThreadInput(_WinAPI_GetCurrentThreadId(), $hTestWindowTID, True);attach to window we want to paste into $bCanPaste = _WinAPI_GetFocus() ;Test whether window is paste-able--returns False if it is not _WinAPI_AttachThreadInput(_WinAPI_GetCurrentThreadId, $hTestWindowTID, False);detach from window thread Return $bCanPaste EndFunc Pass it a window handle; it returns true or false whether a window will accept programmatic pasting. The function may not work on the CMD window, since it handles the clipboard uniquely.
      This function works by attaching to the program thread of the window whose handle it receives, then attempting to perform a GetFocus on that thread. In most cases, the attempt will fail if the window will not accept programmatic copy-paste.
    • singbass
      By singbass
      I have an issue with disk space on a server so I wrote a simple little script to check specific directories and save the sizes to an Excel spreadsheet.  For this script, I am still using version 3.3.8.1.  Everything works fine, I just have a question.
      #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.8.1 #ce ---------------------------------------------------------------------------- #include<date.au3> #include<excel.au3> #include<array.au3> $file = FileOpen(@ScriptDir & "\CMScriptDirList.txt", 0) If @error Then Exit ;--folder view still OK $oexcel = _ExcelBookOpen(@ScriptDir & "\CMScriptDirSizesCopy.xlsx") If @error Then Exit ;--folder view now parent folder $excelArray = _ExcelReadSheetToArray($oexcel) $lastrow = $excelArray[0][0] $lastcol = $excelArray[0][1] _ExcelWriteCell($oexcel, _NowCalc(), 1, $lastcol + 1) While 1 $line = FileReadLine($file) If @error Then ExitLoop $size = DirGetSize(StringStripWS($line, 3)) / 1024 / 1024 $iIndex = _ArraySearch($excelArray, $line, 0, 0, 0, 0, 1, 1) If @error Then ContinueLoop _ExcelWriteCell($oexcel, $size, $iIndex, $lastcol + 1) WEnd FileClose($file) _ExcelWriteFormula($oexcel, "=SUM(R2C" & $lastcol + 1 & ":R38C" & $lastcol + 1 & ")", 39, $lastcol + 1) _ExcelWriteFormula($oexcel, "=R39C" & $lastcol + 1 & "/1024", 40, $lastcol + 1) $oexcel.ActiveSheet.columns($lastcol).copy ;used to copy the format of the original last column of the spreadsheet $oexcel.ActiveSheet.columns($lastcol + 1).PasteSpecial(-4122, Default, Default, Default) ;this just pastes the format of the original last column to the new last column $oexcel.ActiveSheet.Range("A1").Select ;select cell A1 just to unselect the entire column from previous command $oexcel.columns.AutoFit ;auto sizes the column width _ExcelBookClose($oexcel, 1) ;save file when closing The script is compiled and sitting is a sub-directory on the server in question. The text file and the spreadsheet that are used are both in this same folder as well.  When I navigate to the folder and run the script by double-clicking on the executable, the process runs but the folder view where I ran the script will go back up one level so when the script completes, I am in the parent folder from where I started.  I have added message boxes throughout the script and have determined that the folder view goes back up one level at some point after the @error check for the file open and before the @error check for the ExcelBookOpen (where the comments are).
      I just wanted to know if someone can tell me why and if there is a way to prevent it. (Note: still using v3.3.8.1 on this machine but slowly converting scripts to v3.3.14.2).