Question about MS Excel VBO - Copy and Paste Worksheet Range

Mata42

New Member
You need first merge the two collections (emp1 + emp2) and use the merged coll (for example emp3) in the loop.. all data will be in one collection, so you can get the duplications now
 

Rowdyremo

New Member
Hi. I am trying to use Copy and paste worsksheet range. and i am breaking with the following error. 1528711679613.png I have tried all possible steps suggested in this thread. This is my flowchart 1528711832461.png. And the data items are 1528711861626.png. The properties of copy paste worsheet as range is 1528711936201.png. I think i have done all the steps correct. not sure y i am still getting this error. Can anyone help me to find the solution
 

Rowdyremo

New Member
Finally i myself found the solution. I got the error since i did not save the workbook which i created. and now it works after saving the destination workbook.
 
  • Like
Reactions: VJR

AVBello

New Member
Thanks @RDawson9 for your comment. But, I have another problem. See the images:

First, I make this diagram:

1537676687234.png

I have this copy/paste operation, with the following configuration: 23-09-2018 1-26-10.jpg

And the error message is: : failed to copy worksheet: error en el método select de la clase range

I try to copy an amount of data to another workbook (PLANILLA MATCH.xls), and when I star the process, the message appear.

Sorry for the languaje, but I'm from Chile and the S.O. is in spanish... :)

If anyone know "a little thing" about this, I will be very grateful.

Greetings from Chile.
 

Attachments

  • 1537676687234.png
    9.2 KB · Views: 35
  • 23-09-2018 1-26-10.jpg
    141.1 KB · Views: 23

VJR

Well-Known Member
Hi AVBello,

The translation of the error message shows as "error in the select method of the range class"

- In the 'Destination Range' parameter can you check using just "A1" and check what happens
- Also check if the Destination does not have any merged cells etc
- Do a manual copy paste from A1:V52364 (File 1) to A1:V52364 (File 2)
Do it manually using Ctrl C, Ctrl V without Blue Prism and check if it pastes correctly.

Try the above three options and post back your observations.
 

AVBello

New Member
Hi AVBello,

The translation of the error message shows as "error in the select method of the range class"

- In the 'Destination Range' parameter can you check using just "A1" and check what happens
- Also check if the Destination does not have any merged cells etc
- Do a manual copy paste from A1:V52364 (File 1) to A1:V52364 (File 2)
Do it manually using Ctrl C, Ctrl V without Blue Prism and check if it pastes correctly.

Try the above three options and post back your observations.

Thanks for your response. I solve the problem with the step 2.

Thank you!!!
 

Hema Arul

New 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:

View attachment 94

View attachment 95


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 :)
Hi Rob,

I have tried the above and getting the following error.

View attachment 1538733692658.pngView attachment 1538733730860.png

Could you pls help me in fixing the error.
 

VJR

Well-Known Member
Hi Hema Arul,

The parameters you have given appear to be correct.
Do manually check once if you are able to copy paste the data.

Here are a few options to do-

- In the 'Destination Range' parameter check using just "A1" (this doesn't matter but worth a check during troubleshooting)
- IMP: Also check if the Destination does not have any merged cells
- Do a manual copy paste from A1:E5 (File 1) to A1:E5 (File 2)
Do it manually using Ctrl C, Ctrl V without Blue Prism and check if it pastes correctly.
- Also use 'Show' actions for both the workbooks and "visibly" see if something wrong is happening.
- Instead of hard coding the xlsx file names, directly use the data items Workbook Name and Workbook Name1.
 

Jory

New Member
Hello! I need to copy only some columns of .xls1 and paste in another .xls2 but in different order of the columns, that is to say, xls1 copied column A and C and paste it in .xls2 in D and E.
Please, I appreciate your help.
regards
 

VJR

Well-Known Member
Hello! I need to copy only some columns of .xls1 and paste in another .xls2 but in different order of the columns, that is to say, xls1 copied column A and C and paste it in .xls2 in D and E.
Please, I appreciate your help.
regards
I doubt it is allowed by BP to do so, although you can try that by using copy paste Worksheet range action.
If not, you can surely use the same action and copy A to D at first and then C to E.
 

Jory

New Member
Hello!

What I have done has been to create a SetConnection action using Data OLEDB.

"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\VoNet\RobotsVonet\BUYBACKMONITORINGSAP3.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""

To manipulate the collection (because I only want some columns)

And when I have the final collection, the last thing would be to paste it into a new excel sheet.

The problem that I have right now, which for you is very simple. It gives me an error when calling the columns with a query, could I call it with the name of the column (A or B) and not indicating the header? How could I select them? Can it be because of the content type of the column?

I do not know if the path I have taken is correct, but please, if no problem indicates it.

Thanks for your help.
 

VJR

Well-Known Member
I do not know if the path I have taken is correct, but please, if no problem indicates it.
Hi Jory,

You can still use the Copy Paste Worksheet Range as suggested above (copy A to D at first and then C to E).
You can go by the Data Oledb way if you really have very large volume of data and when there is lots of interaction involved with this data.
 

Niraj Shah

New Member
Question : I want to copy the data from WorkBook_1 which is excel formula to WorkBook_2. When I'm using 'Copy and Pate Worksheet Range' stage, the data is copied as formula from WorkBook_1 and same is pasted in WorkBook_2. How can I paste the copied data as 'values only'. Do I need to make any change in 'code' of 'Paste worksheet' available under 'Copy and Paste Worksheet Range' ? If yes, then can please guide what changes I should do?

View attachment capture-20181116-154655.png
 

Nitesh Kumar

New Member
Hi ,

I do have 1 problem,how to write data skipping hidden/filtered cells and rows in excel in blue prism?
For ex: If i have applied filter in Column A using "ABC" ,then in Column B i have to write "ABC/XYZ" data corresponding to the column A after filtering.
 

amohare

New 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:

View attachment 94

View attachment 95


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 :)
It Worked. you can use Get Number Rows action if you are not sure about rows in source sheet.
 

Justin Nguyen

New Member
Please help me understand the error and how to solve this problem ..

Your contribution is very valuable to me. Sincere thanks.
 

Attachments

  • copy and past.PNG
    30.5 KB · Views: 13
  • copy and past Failed.PNG
    100.4 KB · Views: 8

Vinutha

New 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:

View attachment 94

View attachment 95


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 :)

Hi Rob,
Am trying to copy a range of lines from one excel to macro excel giving variable name for no...of rows like "A1:"&"K"&[src row] where src row is a variable having num of rows in source excel and also tried mentioning the range exactly like "A1:K3" but both are not working. It says select method range not correct. Could you pls help.whats wrong in range format
 
Top