|Subject:||Re: Cut and Paste - Table into Excel -
|Date:||Sat, 4 Aug 2018 17:51:40 +1000
|From:||Robert MacMillan <email@example.com>
its not the library which is great. It is just that sometimes finding
the solution to a particular problem can be difficult. And this is one
of those issues. There probably is a one step "Paste" function available
but search as I might I have not yet found the documentation. So my
current solution is exactly as you are using. Cell at a time and because
of all the formatting involved that recalculates each time a cell is
updated. So its really slow.
I am using a Read Only Template Excel Sheet so I am thinking about
creating a macro within the sheet to do the paste. And then invoking it.
Microsoft are real bastards in terms of making things easy to find and
implement. There are usually ways to do most things but sometimes they
really hard to find.
On 4/08/2018 1:50 PM, Kevin Zawicki wrote:
> I have worked with that library and some mods I made for a while. I have
> tried to do what you want but often end up going cell by cell which is lowest
> I have and limited other success with other techniques.
> add a sheet, paste on sheet, select all, remove all grid formatting, paste
> into desired sheet, some variation son this.
> Write excel vba code to import from text, invoke the code via ole.
> Also, I have been on a quest to find a OPAL example to use excel ole to sort
> a sheet.
> Robert MacMillan <firstname.lastname@example.org> wrote:
>> Thanks heaps modridirkac.
>> This is one of those really frustrating Paradox and in this case OLE
>> issues that once solved will remain solved forever. In the meantime much
>> So if I use sel.Invoke("PasteSpecial")
>> It does indeed paste. However it Pastes everything on the clipboard
>> including the stuff that is not visible so all the horizontal and
>> vertical bars one sees in a table so those are being stored on the
>> Clipboard but are not visible.
>> If I use sel.Invoke("Paste") ....... and am watching what is going on
>> with the spreadsheet then the focus goes to the Paste Button which then
>> waits for interaction. And will paste. So the focus is in the right spot
>> in the spreadsheet. One can continue with the Paste using the down arrow
>> on the Paste Button or Ctrl+V also pastes correctly.
>> What I cant seem to do and I must be stupid because if "PasteSpecial"
>> works then there must be a way to emulate the Ctrl+V command but I cant
>> figure it out. Tried absolutely everything I can think of so far.
>> Another one of those things which should be obvious but is not.
>> Anyway. Thanks for the help and it was a huge help because it gets me to
>> where all I have to do is figure what I am missing.
>> On 31/07/2018 5:41 PM, modridirkac wrote:
>>> This code works for me.
>>> You must have something in the clipboard, before calling this script.
>>> You must also have an xls file in tem folder, to open it.
>>> This code opens OLE t oexcel, loads file (c:\temp\excel.xlsx) and pasts
>>> to cells B5:C8
>>> and shows final result in excel.
>>> ; Excel coord. types
>>> xlA1 = 1 ; A1
>>> xlR1C1 = - 4150 ; R1C1
>>> method run(var eventInfo Event)
>>> ; paste from clipboard to excel
>>> var excel OleAuto
>>> Workbooks, sel OLEAuto
>>> ; start excel
>>> ; open file
>>> Workbooks = Excel.Workbooks
>>> if WorkBooks.count>0 then return endif
>>> Workbooks.invoke("Open", "c:\\temp\\excel.xlsx")
>>> ; mark your range
>>> s = Excel.convertFormula("B5:C8", xlA1, xlR1C1, True)
>>> sel = Excel.Selection
>>> ; paste from clipboard to that selection
>>> ; show the result