Question about MS Excel VBO - Copy and Paste Worksheet Range

rickjh123

New Member
I'm trying to copy ALL data from a particular worksheet in workbook "A" and simply paste that exact same worksheet's data into a particular worksheet in workbook "B". I've set handle 1 as the "destination" workbook, and handle 2 as the "source" workbook. In the MS Excel VBO "Copy and Paste Worksheet Range" object, I've added the source workbook and source worksheet names, as well as the source range such as "A1:B5", and did the same thing for destination. I'm confused about what to put in the "Handle" row. In the inputs section of the VBO, which handle is the input? The source or the destination or neither?

When I try to run it I get "Failed to copy worksheet: Invalid index. (Exception from HRESULT : 0x8002000B (DISP _E_BADINDEX))

All I want to do is simply copy and paste one worksheet to another. I don't need to manipulate the data so I don't want to bring it into Blue Prism if possible as a collection because it's too slow, if possible.

What am I doing incorrectly? Prior to the copy/paste action, i've created instances for both workbooks, opened the workbooks, and show them.

Thanks!
 

RDawson9

Geek
Staff member
Hi @rickjh123

You shouldn't need handle 2 to complete this operation. You should be able to open both Workbooks under handle 1. Note that the "Create Instance" action outputs the handle for me, rather than me having to assume the number. Here's how I did it:

1516718332581.png

1516718365670.png


The "invalid index" message seems to appear when you set the full file path/name as the Source/Destination Workbook. In my example, when I used [File1] as the Source and [File2] as the Destination, I got the index error. When I amended these to the actual Workbook name (Tech.xls and Tech2.xls), I no longer received the error.

Hope this helps.
Rob :)
 

Manu

New Member
Hi @RDawson9, i have a question, I need to copy an entire excel sheet and I dont know how, What should I put in the Source Range and Destination Range value?

Many thanks in advance!
 

RDawson9

Geek
Staff member
Hi Manu,

I would set extremely high values like "A1:ZZ1000" to make sure I capture all the potential cells.

What would maybe be a better idea though would be to use "Get Worksheet as Collection" to convert the spreadsheet into a Blue Prism Collection. Then use "Write Collection" to send the Collection to your desired Excel sheet. This is a more foolproof method than the first one.

Rob :)
 
How to create copy of already exists excel file using blueprism?
Hi @Ketan Nagariya

To copy an existing excel file, you can use the copy object of the MS Excel Vbo. At first create instance and open workbook of the excel file that you want to copy. Then use the copy object of the MS Excel Vbo. After that use the "Save WorkBook As" object of the MS Excel Vbo to save it to a different location. I am attaching screenshots for help.
 

Attachments

  • copy existing excel file.PNG
    copy existing excel file.PNG
    11.9 KB · Views: 793
  • save workbook as object.PNG
    save workbook as object.PNG
    18.4 KB · Views: 792

AndyK16

New Member
Question about MS Excel VBO

Hi All I have 10 data ITEMS in Blue prism which I then need to transfer to an
excel sheet folder so basically I need the data to go from A1 to J1
Then when I open the folder again or keep the folder open ... the next 10 data items go in to the same folder and start from A2 to J2
and so on repeat A3 to J3 so in theory if I did 20 checks it would continue entering this data on the same excel sheet starting from a1 to j2

repeating and adding data until A20 to J20

any help and info would be a great help
 

Nisha

New Member
Hello All

Facing same trouble - HRESULT: 0x8002000B (DISP_E_BADINDEX)) error while copying data from one excel sheet to another excel sheet

Scenario: Merge data from different excel files into single excel file with different sheets

I want to copy data from multiple excel file (one by one) and want to save those under one excel file but with different sheets

Can anybody help?

Also I tried this with collection, but its not working files which are having huge data at least in 10000's also having some formatting, how one should copy data with all formatting?
 

VJR

Well-Known Member
Staff member
Hi Nisha,

The cause for the error is the same reason as mentioned by @RDawson9 above.
When full path is given to the Source and Destination Workbook parameters, then it throws up this error.
Instead, either use just "Workbookname.xlsx" or you can directly use the Workbook Name output parameter that you got from the 'Open Workbook' action.
This should resolve your error.
Once you check this out please post back for the other issues if you have.
 

Nisha

New Member
Hi Nisha,

The cause for the error is the same reason as mentioned by @RDawson9 above.
When full path is given to the Source and Destination Workbook parameters, then it throws up this error.
Instead, either use just "Workbookname.xlsx" or you can directly use the Workbook Name output parameter that you got from the 'Open Workbook' action.
This should resolve your error.
Once you check this out please post back for the other issues if you have.

Hi VJR,

Thanks for support, but VJR I want it to work dynamically, as I am getting list of excel files from collection, I want to pass file/ workbook name from collection. I don't want to put hardcode name of the file.
As I am dealing with multiple files, I have applied loop on collection for all files to get copied and paste into final/resultant file.S
So can we have any other solution where I can pass file name through my variable name (through collection.workbookname)?
 

Nisha

New Member
Also @VJR having one more error which is from blueprism action - Get worksheet as collection attaching screen shot for reference, please refer if you could help me on this as well

Detail Error.PNG
 

VJR

Well-Known Member
Staff member
Hi VJR,

Thanks for support, but VJR I want it to work dynamically, as I am getting list of excel files from collection, I want to pass file/ workbook name from collection. I don't want to put hardcode name of the file.
As I am dealing with multiple files, I have applied loop on collection for all files to get copied and paste into final/resultant file.S
So can we have any other solution where I can pass file name through my variable name (through collection.workbookname)?

Hi Nisha,

For that purpose I have mentioned you to use the below.
"..or you can directly use the Workbook Name output parameter that you got from the 'Open Workbook' action"

Suppose you have two handles (instances) - 1 for the Source file and 1 for the Destination file.
Source file will be the one coming from your collection in the loop. You need to create instance and close it once the copy pasting is done inside the loop. Everytime when you take the file path from the collection and do a Create Instance/Open Workbook for it, the 'Workbook Name' Data Item will have the file name exactly for that particular file. This is the data item that you can use in the copy paste range action for the Source file parameter.

The Destination file is the one where you want to paste all your files into.
This will open once in the beginning before the loop but will close only after your collection is over and after all files are done pasting. So it will have a separate handle and a separate Workbook Name data item (with a different name say [Workbook Name Destn]) whatever name you give. This data item will go in your Destination Workbook parameter.
 
Last edited:

VJR

Well-Known Member
Staff member
Also @VJR having one more error which is from blueprism action - Get worksheet as collection attaching screen shot for reference, please refer if you could help me on this as well

View attachment 503

For this error can you show the screenshot of the properties of the stage where you are using the 'Get Worksheet as Collection' action.
I hope you are not modifying the columns in the collection returned by the 'Get Worksheet as Collection'.
 
Top