Excel Copy sheet is not formatting correctly, new sheet is not adding to the end

I am editing an excel that contains many sheet names, sheet names are in month year format ex:June 2020, July 2020, August 2020 etc

My requirement is to create a new sheet then copy the data from last sheet and paste it there, new sheet name should be the next month and year after the last sheet. Ex: if the current last sheet is called “August 2020”, Copy that sheet , create new sheet and name it “September 2020” and paste everything that is copied from “August 2020” to “September 2020” just like if we would have done it manualy using windows copy paste.

Issues i am having

My code/setup works as far as creating a new sheet and copying data but

  1. The new sheet is not creating at the end thus becoming the new last sheet , So with our example, after the code runs , “September 2020” should be the last sheet, but its created as the second last, see image: image

  2. The data that was copied are all there on the new sheet including highlight colors but the spacing is wrong, on the new sheet all data is shrunk, this wont happen when we do copy paste using windows copy paste method: see image below
    Original:

copied data:
image

thanks for the help in advance
Here is a sample test file:test.xaml (8.4 KB)

1 Like

Have you tested to replace the Write Cell and Copy Paste Range activities with a Copy Sheet activity instead?

image

yup tried them both, same result. Also tried write range instead of write cell for sheet creation . same deal

OK, that’s strange. I removed Write Cell and Copy Paste Range from your test workflow and added a Copy Sheet and it was working fine for me.

See the attached file if you want to test for yourself:
test.xaml (8.1 KB)

Before:
image

After:
image

Apologize my previous response was not right, i didn’t use copy sheet because of an excel error

“Copy Sheet: Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.Office.Interop.Excel._Workbook’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{000208DA-0000-0000-C000-000000000046}’ failed due to the following error: The requested object does not exist. (Exception from HRESULT: 0x80010114).”

so i did the copy paste range the code worked and ran all the way through except the original question errors

what version of studio and packages are you using?

Mine is this:

“UiPath.Excel.Activities”: “[2.7.2]”,
“UiPath.Mail.Activities”: “[1.7.2]”,
“UiPath.System.Activities”: “[19.10.1]”,
“UiPath.UIAutomation.Activities”: “[19.11.1]”

“studioVersion”: “19.10.3.0”,

1 Like

I’m using Studio Pro 2020.10.0-beta.1008 - 10/5/2020.

image

image

Hi @Jay_Chacko ,

To copy the sheet inside the excel, you can use this activity. “Copy to WorkBook”

Here is the example.

Thank you
Balamurugan.S

ya we are using the enterprise edition and the 2020.10.0 version is still in beta and will become available later this month, i will update once and try and will give an update in this discussion

1 Like