MS Excel VBO will not paste my text value, error message: 'PasteSpecial method of Range class failed'

[Anonymous]

Hi all, I am working on creating a queue from a web-based application. It was not possible to use 'Get Table', so instead I am using 'Get HTML' and storing this in a Text type Data item. After this, I want to copy this into an Excel, to get a list of numbers that I can then populate my queue with. However, I don't seem to be able to paste the data. I am using the standard MS Excel VBO, with the following sequence of actions: - Create Instance - Create Workbook - Create Worksheet - Write to Clipboard So far so good, this all works and I know that the data is in my clipboard because if I go to Excel manually at this point I can paste the data. Then I use the 'Paste' action from MS Excel VBO, and it keeps giving the following error message: 'Failed to paste from clipboard: PasteSpecial method of Range class failed'. Can anyone help me solve this? I have already tried the following things: - Activate workbook/worksheet/both before attempting to paste - Leaving out the 'Create Worksheet' action - Showing the Excel before attempting to paste - 'Values only' input parameter 'True' and 'False' Thanks for your help!

[EnricoEmme2 · Accepted solution]

Hi kvanhal, 'Failed to paste from clipboard: PasteSpecial method of Range class failed' occurs because either the object is empty or it has not target. With ""paste"" you throw your data into a workbook, rather worksheet with selected cell. Instead, try to use the action ""set cell value"", define your destination cell (i.e. ""A1"") and give it a re-run. Hope that works!

[KevinMcKenzie]

Hi eemme, I am having the same issue where I have copied a table from another program and need to simply paste it into a new excel spreadsheet. It works fine if I do it manually by using ctrl+v but I get the error message 'PasteSpecial method of Range class failed' when I try using BP. Is there a way to send global send keys using MS Excel VBO? Or is there something else I can do to paste this table? Thanks!

[BryantBackus1]

Any word on this? I'm having the exact same problem. Converting to a collection and using the Write function is not an option because I have a large data set that takes too long to write in.

------------------------------
Bryant Backus
------------------------------

[AndreyKudinov]

Blueprism action "Paste" is actially PasteSpecial, not what you get when pressing ctrl-v. Make new action with ws.Paste, it would work.
Writing big collection to excel is actually fast, unless you use default blueprism write action.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

[BryantBackus1]

Thank you,

I ended up finding a work around for my particular case.

Originally I had my table in a collection and wrote code to write the collection to my clipboard. Once on the clipboard. I went into the Excel VBO and created an action that combined the "activate worksheet" and "go to cell pages" with a navigation stage that sent a global sendkey for CTRL v to Excel. To do that I had to open the application modeler and create the link between the VBO so that it would attach to an already running instance of Excel. 

My final solution, to avoid sendkeys, was to use the "Get collection as CSV" action in the strings utility VBO. I then used the "Append text to file" Action in the file management VBO to create a temporary csv file. I then used the "Import csv to Excel" in the excel VBO to write the csv to the target sheet. 

I'm sure a similar approach can be taken even if you don't have the table as a collection if you use the get clipboard as collection action.


------------------------------
Bryant Backus
------------------------------