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-02-03 - Version 1.4.7.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-02-03 - Version 1.4.7.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-02-03 - Version 1.4.7.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-02-03 - Version 1.4.7.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-02-03 - Version 1.4.7.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

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I would like to know if I can use the Excel UDF to manipulating a .csv file without having Office installed on the PC I'm going to work...
      I read somewhere that it could be done, but I'm here to ask and be sure of what I remember... 
      I'd like to post another question...
      How can I retrieve the handle of a windows from a PID of an .exe?
      I have my script that does a ShellExecute ( which returns the PID of the .exe ), and then, switching a parameter read from a .ini file, adapt the Window on the screen ( Maximize, Minimize, On Top )...
      I tried, but without having success with this:
       
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=prova.exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <WinAPIEx.au3> #include <Array.au3> Local $sFileConfigurazione = @ScriptDir & "\configurazione_exe.ini" If(FileExists($sFileConfigurazione)) Then Local $aSezioniIni = IniReadSection($sFileConfigurazione, "CONFIGURAZIONE_EXE") If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file: " & @CRLF & $sFileConfigurazione & @CRLF & "Errore: " & @error) Else ; Lancio dell'applicativo indicato nel file di configurazione Local $iPID = ShellExecute($aSezioniIni[1][1]) Local $hWnd If($iPID <> 0) Then Local $aWinList = WinList() For $i = 1 To $aWinList[0][0] If(WinGetProcess($aWinList[$i][1] = $iPID)) Then $hWnd = $aWinList[$i][1] EndIf Next Switch($aSezioniIni[2][1]) Case $aSezioniIni[2][1] = "MIN" WinSetState($hWnd, "", @SW_MINIMIZE) Case $aSezioniIni[2][1] = "MAX" WinSetState($hWnd, "", @SW_MAXIMIZE) Case $aSezioniIni[2][1] = "TOP" WinSetOnTop($hWnd, "", $WINDOWS_ONTOP) EndSwitch EndIf EndIf EndIf It just set on top the .exe I'm launching...
      Thanks
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I was looking for a method with rename an Excel Sheet, but I didn't find a lot...
      So, I decided to make it in another way...
      Since I have to create a new Workbook, I thought that, creating a new Workbook, deleting the existing sheet, and adding a new one, would be almost the same...
      But I'm encountering a lot of issues, both when I delete the sheet and when I add the new sheet...
      This is what I do:
       
      ; Create the Excel Object... Local $oExcel_PRV_HW = _Excel_Open(False) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & @CRLF & "Esteso: " & @extended) Else ; Create the Workbook with 1 worksheet... Local $oWorkbook_New = _Excel_BookNew($oExcel_PRV_HW, 1) ; Save the Workbook in order to open it and work with it... _Excel_BookSaveAs($oWorkbook_New, $sFilePreventivo, $xlOpenXMLWorkbook, True) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro." & @CRLF & "Errore: " & @error) EndIf ; Open the Workbook to work with... Local $oWorkbook_PRV_HW = _Excel_BookOpen($oExcel_PRV_HW, $sFilePreventivo) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file '" & $sFilePreventivo & "'." & @CRLF & "Errore: " & @error) Else ; Here I would add the _Excel_SheetDelete() and _Excel_SheetAdd() as I did, but they return errors... EndIf EndIf Can someone help me out, please? Thanks
    • Vencejo
      By Vencejo
      Hi all,
      I think i´m doing something wrong. In the following code, _excel_RangeFind() does not find all occurrences. From sheet1 to sheet9 it does not find the occurrence of row 1, and on sheet10 it find it, but puts it last. Where am I wrong? Thank you very much and sorry for my inglish. The code:   #include <Excel.au3> Local $oAppl = _Excel_Open(True) Local $oWorkbook= _Excel_BookNew($oAppl, 10) Local $namesheet= "hoja";<-- Default name for sheet in spanish language: hoja1, hoja2, hoja3 etc. For $x= 1 to 10;<-- $x completes the name of the excel sheet: $namesheet & $x for $y= 1 to 5 _Excel_RangeWrite($oWorkbook, $namesheet & $x , "sofia" & " " & $namesheet & $x & " " & $y,"A" & $y) Next Next Local $search= _Excel_RangeFind($oWorkbook, "sof") _ArrayDisplay($search) This is using 3.3.12 version and office 2007.
    • Paranthaman
      By Paranthaman
      Hi Everyone,
      I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly.
      What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite .
      _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered
      What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns
      i.e) For i=0 loop, every array content should be written in A Column of excel
      For i = 1 loop, every array content should be written in B Column of excel.
       
      Can anyone give me an idea of  how can i do this? Thanks 
    • SkysLastChance
      By SkysLastChance
      Why is my code not writing X in cell D1? 
      #include <Excel.au3> Global $r = 1,$oExcel Excel () Func Excel() While ProcessExists("EXCEL.EXE") $ms = MsgBox(5,"","Process error. You have an Excel sheet open. You must close it in order to let this program work. Please close it now.") If $ms=2 Then Exit WEnd Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)") If FileExists($sExcelFile) Then $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file. Else $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist. EndIf EndFunc Sleep (2000) _Excel_RangeWrite($oExcel,Default, "X", "D" & $r) Exit