Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Error trying to paste data from clipboard into Excel using Excel VBO

TonyHann
Level 4

‎10-11-20 05:06 PM

Hi All,

I am having problems trying to paste data from a 3rd party application into Excel using the Excel VBO.

The Automation I am working on is to automate the production of various system reports at specific points in time, the output from one of the reports is required in Excel, however the application does not have a native 'Export' function for this particular report.

The manual process is to currently run the report to screen, highlight the application, then CTR+A, CTR+C to copy the report to clipboard, and then to CTR-V into Excel - which works correctly.

I can get the data into the clipboard of my automation by using Global Send keys and sending the CTR+A, CTR+C to the application - the problem I have is in then trying to paste this data into Excel.

So far I have tried the following:-

MS Excel VBO - Create Workbook
MS Excel VBO - Select (to select Cell reference A1)
MS Excel VBO - Paste

This generates the error message 'Exception : Failed to paste from clipboard: PasteSpecial method of Range class failed'

I have tried modifying the Excel VBO from a PasteSpecial into just a Paste, by amending line 9 of the VBO code
From
range.PasteSpecial(Paste:=Paste_Option, Operation:=-4142, SkipBlanks:=False, Transpose:=False)

To
range.paste

However this generates the Error message 'Exception : Failed to paste from clipboard: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'

I think the problem is due to my data not originating in the same Excel instance that I am trying to paste it into, so I used the macro recorder to record the VBA generated by pasting the data into Excel, which returned:-

'ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False'

So using this I amended the VBO Paste - line 9 
To
range.PasteSpecial(Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True)

However, this also generated the error 'Exception : Failed to paste from clipboard: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))'  - the same as simply using range.paste

I know that the data is correctly in the clipboard as I can use a 'Utility - Environmnet, Get Clipboard' and successfully write it to a data item, however,  this then removes all of the formatting so even if I write this data item to a .txt file - the resulting txt file is not suitable to open in Excel.

If anyone has any ideas as to how I can paste my 3rd party data clipboard directly into Excel this would be greatly appreciated.

Many thanks
Tony










------------------------------
Tony Hann
------------------------------
0 Likes
8 REPLIES 8

hossein.azimi
Staff
Staff

‎15-11-20 11:43 PM

Hi Tony,

I suggest
Instead, using "paste" will throw the data into the workbook instead of the worksheet that contains the selected cells; use the action "Set Cell Value" to define the destination cell (that is, "A1") and paste it into that cell.








------------------------------
Hossein Azimi
Customer Support Engineer, APAC
Blue Prism
Sydney NSW
------------------------------
0 Likes

TonyHann
Level 4
In response to hossein.azimi

‎18-11-20 01:09 PM

Hi Hossein,

Many thanks for taking the time to reply,

Sorry - I should have also mentioned in my original post, I have already tried exactly what you show here using the 'Set Cell Value' action in the Excel VBO - and whilst it does paste the clipboard data item into cell A1 - there are a few reasons why this doesn't work for me.

When using Right Click & Paste, or CTR+V to paste the clipboard into Excel, all of the formatting from the original report is retained and as such financial values are in the correct required columns.

If I write the clipboard to a .txt file and use Excel to open the file - then whilst I get a separate line for every line of the report, all of the data is in column A
If I use the 'Set Cell Value' option above then the entire report is entered into the single Cell A1.

The issue with having all the text in Column A is that there is then no reliable way to split the columns to ensure the financial values end up in the column I require - as some of the report columns before and after these values contain text fields - and as such the financial values never appear after a 'fixed' number of spaces (there may also be numbers in these text fields).

I have created a work around at the moment by creating a Business Object that attaches to Excel, and then sending a CTR+V to Excel via Global Send Keys - however this is a bit of a clunky work around - and I'm not sure why I cant get the 'Paste' function to work from the standard Blue Prism VBO.

Is there any way to achieve this result via a code stage?

Many thanks

Tony

------------------------------
Tony Hann
------------------------------
0 Likes

michaeloneil
MVP
In response to TonyHann

‎22-02-21 05:51 PM

Hi Tony

I noticed you said the actions you used to try and paste the data were

MS Excel VBO - Create Workbook
MS Excel VBO - Select (to select Cell reference A1)
MS Excel VBO - Paste

Did you also do a create instance before the create workbook action?

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------
0 Likes

AmySnagg
Level 3

‎30-06-23 12:30 PM

Hi Tony, I know this post is a few years old now but I was just wondering if you can remember if you figured out a way to fix this and if so how please. I am having the same issue as I need to paste an image to excel from a 3rd party website but can't get the paste function to work within the excel VBO. Thanks, Amy



------------------------------
Amy Snagg
------------------------------
0 Likes

Dan.Lister
Level 4
In response to AmySnagg

‎30-06-23 01:51 PM

Hi Amy, 

I have done something similar that may work.

I had to copy the Set Clipboard action in the Environment VBO and create a new one called Set Clipboard - Image (Attached). This then allowed the standard Excel Paste action to work. Feed in your image file saved from the website and it should do the trick.

Thanks



------------------------------
Dan Lister
Developer
Arvato
Europe/London
------------------------------
BPA Object - Set Clipboard - Image.bpobject
0 Likes

_Amlansahoo
MVP
In response to AmySnagg

‎30-06-23 07:34 PM

Hi Amy,

Please add below action to the excel object. It should do your work.

<process name="__selection__MS Excel VBO - Extended" type="object">
  <subsheet subsheetid="7dbfb4fc-fb9c-4bcb-a90e-da709a29d296" type="Normal" published="False">
    <name>Insert image into cell</name>
    <view>
      <camerax>-157</camerax>
      <cameray>-53</cameray>
      <zoom version="2">1.25</zoom>
    </view>
  </subsheet>
  <stage stageid="d2e58874-1fbb-4180-85d2-ae807e5b2d28" name="Insert image into cell" type="SubSheetInfo">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <display x="-195" y="-105" w="150" h="90" />
  </stage>
  <stage stageid="f5deaa2e-a79f-44ab-971c-26e7548d45c3" name="Start" type="Start">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-60" y="-180" />
    <inputs>
      <input type="text" name="Workbook Name" narrative="The name of the workbook in which the sheet exists." stage="Workbook name" />
      <input type="text" name="Worksheet Name" narrative="The name of the sheet which should be activated." stage="Worksheet name" />
      <input type="number" name="handle" narrative="The integer handle identifying the instance on which the worksheet which should be activated resides. " stage="handle" />
      <input type="text" name="Cell reference" stage="Cellref" />
      <input type="text" name="Image Path" stage="Image" />
    </inputs>
    <onsuccess>75f16bc1-6f30-4786-805a-0e648644f7ea</onsuccess>
  </stage>
  <stage stageid="75f16bc1-6f30-4786-805a-0e648644f7ea" name="Write Image" type="Code">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-60" y="-105" />
    <inputs>
      <input type="text" name="Workbookname" expr="[Workbook name]" />
      <input type="text" name="Worksheetname" expr="[Worksheet name]" />
      <input type="text" name="Cellref" expr="[Cellref]" />
      <input type="text" name="Image" expr="[Image]" />
      <input type="flag" name="Createifmissing" expr="[createifmissing]" />
      <input type="number" name="handle" expr="[handle]" />
    </inputs>
    <outputs>
      <output type="flag" name="sheetexists" stage="sheetexists" />
    </outputs>
    <onsuccess>7059cc70-cf5f-490b-a3cb-272afa613ced</onsuccess>
    <code><![CDATA[Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname,createifmissing)
sheetexists = ws IsNot Nothing 
If sheetexists then 
ws.Activate()
ws.Range(Cellref).Activate()
ws.Pictures.Insert(Image)
End If


]]></code>
  </stage>
  <stage stageid="71a21d1a-ee01-43ec-8163-9a8089a42545" name="Workbook name" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="-15" w="120" h="30" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="30fcb284-99ed-462f-bbe9-8d801ba04e98" name="Worksheet name" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="15" w="120" h="30" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="a506948b-b6b4-4db9-bcfb-e41cdb5a9d45" name="Cellref" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="135" w="120" h="30" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="a99d6380-fe8c-451d-ad86-f87ee7366eaa" name="Image" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="45" w="120" h="30" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="7f988ed6-3831-4851-8eb5-5fc1c1078b71" name="createifmissing" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="75" w="120" h="30" />
    <datatype>flag</datatype>
    <initialvalue>False</initialvalue>
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="b1e083ec-c166-4e77-b258-ec6e2908155e" name="handle" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-300" y="105" w="120" h="30" />
    <datatype>number</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="1f092d45-b2f8-4430-ac72-59f50ed48390" name="sheetexists" type="Data">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <display x="-165" y="-15" w="120" h="30" />
    <datatype>flag</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="7059cc70-cf5f-490b-a3cb-272afa613ced" name="End" type="End">
    <subsheetid>7dbfb4fc-fb9c-4bcb-a90e-da709a29d296</subsheetid>
    <loginhibit />
    <display x="-60" y="-15" />
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <outputs>
      <output type="flag" name="success" stage="sheetexists" />
    </outputs>
  </stage>
</process>


------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------
Regards,
Amlan Sahoo
0 Likes

AmySnagg
Level 3
In response to Dan.Lister

‎03-07-23 08:17 AM

Thank you!



------------------------------
Amy Snagg
------------------------------
0 Likes

AmySnagg
Level 3
In response to _Amlansahoo

‎03-07-23 08:17 AM

That's great thank you!



------------------------------
Amy Snagg
------------------------------
0 Likes