How to copy sheet from Excel to another with a new sheet name

So I’ve been trying to copy a sheet from an excel to another, but with a different sheet name. Before the copy starts, Would Kill Application for Excel. Add an Excel Application Scope With Copy sheet activity inside of it. The Excel App Workbook path is file1, copy “Orders” sheet from it, then add to to excel file2 with sheetname “Failed Orders”. The file2 will always be new or created by excel application app.

Problem is, It keeps giving me either, 0x800706ba or 0x800a03ec error. I’ve tried using balareva’s “Create Workbook” , add delay, then “Copy To File” to copy the sheet. But the same error occurs on “Copy To File” activity.

Hi @Archie,

Instead of doing the copy paste you can use the read range and write range activities that might can solve you issue.

Regards,
Sanjit

Hi @Archie
Use Read range activity where mention the particular sheetname we want to get extracted with data and get the output with a variable of type datatable named dt

Now use WRITE RANGE activity and mention the sheetname we want and range as “” with ADD HEADERS property enabled and pass the variable dt as input
This will write that datatable to the new sheet

Thanks

Hi @Archie

Try to use Send Hotkey activity

Have a look on the thread

Alternative method

Use Read Range activity store the value in the Dt Variable

Use Write Range activity mention the new sheet name with Add header and pass the Dt variable

Regards
Gokul

Ok I’ll try this too. But how about the sheet name? I need to change the sheet name to “Failed Orders”

Hi @Archie

Have a look on the skeleton

Temp CopypasteExcel sheet.xaml (5.0 KB)

Regards
Gokul

This one works for tabled excels. Problem is, there I have many excel files that aren’t table where the datas needed are in an exact cell location. Some Sheets are also in pictures so read ranging does not work.

Hi @Archie ,

As suggested by @nikhil.girish ,you can use a Read Range Activity to copy the desired range, and then for the sheet name, you can pass that value into the Write Range Activity which will then create a new sheet with the desired name.

Could you give that a try and let us know?

Kind Regards,
Ashwin A.K

Kindly share the sample input and out file here to look

You can try with Send Hotkey activity as in mention in the above post @Archie

Regards
Gokul

Hey @Archie both @nikhil.girish and @ashwin.ashok suggestion is correct.
I’m also suggest the same why you are tell that it is not working? Have check the suggestion

Regards
Gokul

Order 1123553.xlsx (11.5 KB)

This is the file, I’m trying to read range all but they are formatted like this. The Receipt Tab is also needed if the order process did not fail, but that sheet only contains the picture of the recepit

Your suggestion works. Sorry that I forgot to mention the sheet format and the picture. That is where the part that it does not work.

HI @Archie

Have a look on the workflow

Temp CopypasteExcel sheet.xaml (31.7 KB)

Regards
Gokul

Thanks, I’ll try this

Do you know how to delete the extra sheets? Like after copying the Order Sheet and Renaming it. I’ll have to delete the Original “Orders” Sheet?

Hi @Archie

Try with Send HotKey Activity

Alt+h,d,s

image

Use another send hotkey

Indicate the popup in the screen

enter

Regards
Gokul

1 Like

Hi @Archie

Another Method

Go to → Manage package → Install (Balareva.Excel and Easytext)

Try with Delete sheet activity

Regards
Gokul

1 Like

Hi @Archie,

You can also try with VB script, in a invoke vba activity to achieve the desired requirements.

Thanks

For what step?

Thanks. This worked on mine.