Problem copying data from an Excel sheet to another

Hi guys ! I’m back with another problem ! :sweat_smile:

Ok so I have two Excel docs, let’s call them Excel1 and Excel2.

  • Excel1 is a file with the up-to-date data concerning the financial operations of the month.
  • Excel2 is a big Excel file gathering all the financial operations of the year.

Excel1 has only one sheet : Sheet1.
Excel has 2 sheets : FinancialSynthesis and CurrentMonthOperations.

I’m supposed to copy datas from Sheet1 (from Excel1) to CurrentMonthOperations (from Excel2).
I first tried to delete the CurrentMonthOperations sheet, copy Sheet1 to Excel2 and rename it « CurrentMonthOperations ».
It worked BUT the problem is that the FinancialSynthesis sheet is using a lot of formulas connected to the CurrentMonthOperations sheet. And after I’ve done this, all the formula are displaying errors.

So, I tried to simply copy Sheet1 as a DataTable then write this DataTable in the correct cell of CurrentMonthOperations with ReadRange activity and WriteRange activity (from Worbook activities).
But I have a strange error of range ; it’s not doing what I want at all….

I checked that I was able to make a simply copy/paste manually from Excel1 to Excel2 and it was working well. So I guess I’m just not using the good activites of UiPath.

I’ll be sooo happy if you can help me ! I have to finish this part for tomorrow … ^^

Thanks :smiley:

Hi @Camille361 !

Yeah it’s always complex when we want to paste data in a file that has formula…
Personally in the write range, i write only the columns, specifying coordinates, when I encounter formula to only write on the cells that do not have formulas (instead of write the whole datatable).

Well let’s try this first, it might help: to read range inside an excel application scope, check “preserve format”:

Then use the usual write range.
Does it make any difference ?

Oh no wait, did read too quickly, your input data are not formulas as well. Ignore my post, then let’s move to the other solutions :joy:

But I don’t think that you can copy on another workbook :confused:

The last solution that I have is to write the columns with specific coordinates.
To do so, once you read range you have a DT (datatable).
Then with write range instead of putting your whole DT you can specify one or several columns, and specify the coordinate where to write. To convert a column into a DT : DT.DefaultView.ToTable(false,“nameOfColumn”)

1 Like

have a look on following link:

the approach maybe can help you on treating a worksheet in Excel like a template worksheet (formats, Formulas) and with the approach from above it will be duplicated,renamed and can be filled with a write range.


Thank you so much Hiba ! I’ve been inspired by your solutions and I finally found one to solve my problem !! :heart_eyes:

Here is my solution :

  • In an excel scope of Excel1 file : I copy the Sheet1 from Excel1 to Excel2 as a new sheet called Sheet2 (with Copy Sheet activity)
  • In another excel scope of Excel2 file : I use the activity Copy Paste Range to copy data from Sheet2 to CurrentMonthOperations. And now that became possible because both sheets are in the same Excel now.
  • After CurrentMonthOperations is updated then I delete the sheet Sheet2.

With this method, the formulas on the FinancialSynthesis sheet are still working well.
I hope it is clear enought ^^

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.