Question about MS Excel VBO - Copy and Paste Worksheet Range

#64
want to copy data from one sheet having different range for copying data and paste it into new workbooks
hi @varsha kanthikar
U want to copy data from 1 worksheet to transfer it to other workbooks right.
U can go with copy and paste get worksheet range as collection.
Then u can paste in different workbooks by giving corerct handles and workbook names in get worksheet range as collection action.
 
#65
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,

Thanks for the details, I have replicate the same which you have done. But when am opening the Workbook, its
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,

Thanks for the details. I have replicate the same which you have shown here. But when am opening the Excel (it is .MHTML format) having 38000 of records. Its taking more time nearly 1 min, after its not pasting the data in the destination file. Its not showing any errors, but when open the destination file, nothing is there. (i have tried with copying 2 rows data only).

Am not sure this is happening bcoz of ".MHTML to .xls" convertion.

Can you please help me.
 
#67
I just tried this, but I got "Failed to copy worksheet: The given key was not present in the dictionary."
Have you used two different VBOs for Excel? Perhaps the regular VBO and an Extended VBO you created? That's the message you would get in the case that, for example, you create the Excel instance with an Excel VBO and then Open a workbook with a different Excel VBO.
 
Top