Question about MS Excel VBO - Copy and Paste Worksheet Range

VJR

Well-Known Member
Hi Mata42,

Which error are you trying to resolve?
If you are facing the index error then it is fixed by the solution mentioned in Post #2.
If it is something else you can detail out the issue.
 

Mata42

New Member
Hi Mata42,

Which error are you trying to resolve?
If you are facing the index error then it is fixed by the solution mentioned in Post #2.
If it is something else you can detail out the issue.
I was replying to this post “I just tried this, but I got "Failed to copy worksheet: The given key was not present in the dictionary."” but i missclicked probably. Anyway it works for me now somehow, but idk how i fixed it.. :)
 
  • Like
Reactions: VJR

hoangqdinh

New Member
hello VJR and Rob,

I was facing the same problem with this VBO. I want to copy range "C2:C1002" from source file (XLSX) to "B3:B1003" in destination file (XLSM). I followed Rob's instruction, but the VBO doesn't copy the data unless in both source and destination range I put "C2:C1000". Do you know why there are this problem?
 

VJR

Well-Known Member
Hi hoangqdinh,

The attached screenshots are working fine for the Ranges you mentioned.
The 'Show' action is optional.
If you are still facing problems please post back with the issue details.
 

Attachments

  • 1. Process Diagram.JPG
    1. Process Diagram.JPG
    29.8 KB · Views: 135
  • 2. CopyPaste.JPG
    2. CopyPaste.JPG
    46.1 KB · Views: 137

Ravi

New Member
Hi All,
I am getting this Error-
"Failed to copy worksheet: Invalid index. (Exception from HRESULT : 0x8002000B (DISP _E_BADINDEX))

Task is - I have an existing excel file which has some data . I want to create a new workbook and in it a new worksheet and then paste the data from Source Workbook to this newly created Workbook in the newly created worksheet and the Save As this new workbook.
I have used two handles. s_handle for source wb and d_handle for destination wb. I have tried all things in the Inputs section of Copy & Paste As Range but it is giving same error.
Below are the snaps
1524644690128.png

1524644738541.png

Please help. Thanks in advance.
 

VJR

Well-Known Member
Hi Ravi,

The parameters you have set here look to be right. But the main thing to see is what parameter values get passed when the process runs. Can you show the pre-filled data items just before executing the Copy as Range stage. (Stop at the Copy as Range stage).

Make sure the value of the 'Worksheet Name' Input parameter of 'Create Worksheet' action is the same as the value returned in the Output parameter of 'Get Worksheet Name'.

Also do share the screenshot of 'Get Worksheet Name' properties.
 

VJR

Well-Known Member
Hi Ravi,

I am able to copy paste to another xlsx without any issues using the below properties.
I have taken only one handle.

- Can you show your Copy/Paste stage properties
- Screenshot of Create Worksheet properties
- Also share the screenshot of the error you are getting

1524643210639.png
 

Ravi

New Member
Hi VJ,
Please find the below Snaps-

Error Msg
1524813700862.png

Copy & Paste Stage-

1524813749090.png

Create Worksheet-

1524813816279.png

Today I tried using One Handle but its giving same error msg.
Could you please share your Process Flowchart ?
 

VJR

Well-Known Member
Hi Ravi,

Below is the diagram with the populated values in the Data items.
1524811315899.png

Try giving another name to the 'Worksheet' data item. Not sure if that is interrupting the already existing keyword of excel with the same name.
 

rpabp

New Member
Hello All,
I have a requirement like, I have two excel files(Excel1,Excel2). I have to find out the unique values from both excel1 ,excel2 and paste into new excel sheet(excel3). How to find the unique values in excel sheet ?
 

Mata42

New Member
Hi,
You can get the excel sheets (1,2) as collections into BluePrism and loop through the collections to find your uniqe value.. Then you will just write it into the 3rd new excel worksheet. Let me know if you get it. :)
 

VJR

Well-Known Member
Hi rpabp,

You can do it in multiple ways:
- copy both excel files to a collection, remove duplicates from the collection and paste the resultant data into the third file.
- copy both excel files into the third excel file and then write a code stage to mimic the Remove duplicates feature of Excel.

Information given by various members in the below threads will be useful in your scenario.

http://www.rpaforum.net/threads/how-to-get-unique-values-from-a-collection.757/
http://www.rpaforum.net/threads/how-to-remove-duplicates-from-a-excel-column.890/
http://www.rpaforum.net/threads/compare-two-excel-worksheets.878/
 

rpabp

New Member
Hi VJR,
Thank you for giving your answer, I know this is very basic question to you.

I am very new to RPA - BP. If you can provide any tutorial links, it would be very helpful to me.
 

rpabp

New Member
Hi,
You can get the excel sheets (1,2) as collections into BluePrism and loop through the collections to find your uniqe value.. Then you will just write it into the 3rd new excel worksheet. Let me know if you get it. :)
Hi,

I am able to write the data of excel1,excel2 into excel3. In excel 3 I have some duplicate numbers, I not able to get the logic of copy the duplicate and paste in 'B' column.
Here, below are the steps I followed:
1.Create Instance->Open excel1->Getdata(Output-data1collection)->Open excel2->Getdata(Output-data2collection)-> Close instance->
2.Create Instance-> Create new workbook->opennewworkbook->write the data1->write data2->save workbook->close instance

In step 2, after write data2, I need to copy the duplicate numbers and paste in paste in 'B' column.

Can you help me!
 

Mata42

New Member
Hi,

I am able to write the data of excel1,excel2 into excel3. In excel 3 I have some duplicate numbers, I not able to get the logic of copy the duplicate and paste in 'B' column.
Here, below are the steps I followed:
1.Create Instance->Open excel1->Getdata(Output-data1collection)->Open excel2->Getdata(Output-data2collection)-> Close instance->
2.Create Instance-> Create new workbook->opennewworkbook->write the data1->write data2->save workbook->close instance

In step 2, after write data2, I need to copy the duplicate numbers and paste in paste in 'B' column.

Can you help me!
Can you upload screenshot of the two merged excel files and show us some case for example please ? :)
 

rpabp

New Member
Can you upload screenshot of the two merged excel files and show us some case for example please ? :)
In emp_new sheet, the data which was from emp1, emp2 excel sheets. I am able to paste in emp_new.xl. I need to find out duplicates from A column, and put in to B column.
 

Attachments

  • Emp_new.PNG
    Emp_new.PNG
    7.4 KB · Views: 26
  • ProcessDiag.PNG
    ProcessDiag.PNG
    24.3 KB · Views: 27

VJR

Well-Known Member
Hi VJR,
Thank you for giving your answer, I know this is very basic question to you.

I am very new to RPA - BP. If you can provide any tutorial links, it would be very helpful to me.
Hi rpabp, no it is nothing like a basic question. The links I have provided you already has everything you need to find the unique values from a collection/excel. It's good to see that Mata42 is assisting you further. Regarding Tutorials, if you are looking for something specifically for excel then you will need to do a web search and find the relevant ones. For overall Blue Prism tutorials, the one from Busy Ping is good to go.
 
Last edited:

Mata42

New Member
In emp_new sheet, the data which was from emp1, emp2 excel sheets. I am able to paste in emp_new.xl. I need to find out duplicates from A column, and put in to B column.
You can use loop, save data from first row into data item, and then ask if it is equal to another row.. if yes -> write into new sheet in column b and you will do this for every single row.. it’s only one decision and one calculation stage inside the loop :)
 

rpabp

New Member
You can use loop, save data from first row into data item, and then ask if it is equal to another row.. if yes -> write into new sheet in column b and you will do this for every single row.. it’s only one decision and one calculation stage inside the loop :)
Confusion, when I use loop , I need to select collection from dropdown right. But it was not creating any collection while writing the data to excel3(I am using Write collection action from drop down).
 
Top