Copy Excel Sheet and Preserver Formatting When Copying to New Sheet

Help Studio
, ,
1

Hello,

First post here but have been using the forum for help for a while now.

I have a template excel with some complicated formatting to include the print area set up for the right formatting. My RPA needs to create a new Excel for a dynamic amount of sheets. The sheet amount could vary anywhere from 1 to 15 but the template needs to be on each sheet created. This template comes from the template Excel file I have that I do not want edited.

I have no issues creating the dynamic amount of sheets based on the user input but the formatting the template onto the sheets is the issue. I have used Excel Application Scope and Read Range to copy the template excel into a DataTable variable and then used Excel Application Scope and Write Range with the DataTrable variable in Write Range. This method copies the text but 0 formatting (i.e. borders, column/row widths, merged cells, etc). (Note: I did check “PreserverFormat” in Read Range).

The other method I tried was to use Hot Keys and Ctrl-A, Crtl-C the template excel then Hot Keys again to Ctrl-V but the same result as above. The output excel only had the text and none of the formatting.

I have searched the forums a lot for an answer but cannot find a solution.

A possible further solution I have not got to work is to use the template excel and create as many sheets as the user desires based off copying the original sheet and then renaming them with a For Each loop but that is also giving me trouble.

Any help would be appreciated!

Thank you

2

Hi Micah - welcome to the community!

Depending on your familiarity with VB/C#, I would recommend using an Invoke Code activity to accomplish these tasks. Here’s an example of moving or copying a worksheet. NOTE: To make this .xaml file work, you’ll need the Microsoft.Office.Interop.Excel package in the nuget.org section of Manage Packages. Also, read the arguments tooltips to better understand how the workflow sequence uses one or more workbooks to move/copy worksheets. I’ve got other Invoke Code activities for pasting, renaming sheets, etc. if you think Invoke Code may be helpful for your use case.

Move or Copy Worksheet.xaml (13.3 KB)

3

Mark,

Thank you for the quick reply. I tried downloading the package but due to the fact my work computer has security restrictions I cannot download that package (or a lot of them for that matter). Is there any other solution you can think of to help with this issue?

I even tried taking my work computer home and off the office secure internet but the security profiles are installed on the actual computers.

Micah

4

Hey @Micah_Rutherford ,

You can simply do this in VBA and then use the invoke vba activity in UiPath. I can assist in writing the VBA code for you but I will need a sample excel file and instructions on what needs to happen.

5

Apparently new users cannot upload documents. Any idea what the wait period is before I can upload an attachment?