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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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

    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Written by GreenCan and water.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have a problem with the deletion of an empty row in Excel.
      My code:
       
      If $vRow_2 = "" Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf I want to delete the second row. $vRow_2 is an empty cell, "A2".
      After running the code, the second row is not deleted.
      I have tried also:
       
      If $vRow_2 = Null Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf But it doesn't work.
      Any suggestion?
      Thanks in advance.
    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • rudi
      By rudi
      Hello.
      I fail trying to save content from WhatsApp other then plain text to a file that way, that non-char-content will be preserved in it's correct encoding. e.g. Emojis are spoiled.
      I've tried to do so using just filewrite() as well as forcing UTF
      #include <clipboard.au3> MsgBox(0,"Ready to read clipboard","Mark some WhatsApp content with non-char elements, e.g. Emojis, copy that to your clipboard (CTRL+c), then click OK here.") $ClipContent=_ClipBoard_GetData() ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $ClipContent = ' & $ClipContent & @CRLF & '>Error code: ' & @error & @CRLF & "Extended: " & @extended & @CRLF) ;### Debug Console $File="C:\temp\ClipSaveToFile.txt" $h=FileOpen($File,2+8+64) ; overwrite, create path, UTF16 FileWrite($h,$ClipContent) FileClose($h) $h=FileOpen($File,64) $NeuGelesen=FileRead($h) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $NeuGelesen = ' & $NeuGelesen & @CRLF & '>Error code: ' & @error & @CRLF & StringLen ($NeuGelesen) & @CRLF ) ;### Debug Console FileClose($h) _ClipBoard_SetData($NeuGelesen) MsgBox(0,"Done","Re-read content of TEMP file was placed to the clipboard. Now paste this into WhatsApp. (Emojis will be spoiled)") I've also tried other encodings (16, 32, 64, 512)

      Or what is a different, functional approach to save WhatsApp Content to File in a way, to be able to to get it back to post it back to WhatsApp?

      Regards, Rudi.