Office365.Activities.Excel.WriteRange - numbers are changed, no PreserveFormat option

Help Studio
, , , , ,
1

Hello team,
I am trying to copy data from an Excel workbook into the Excel workbook on Sharepoint using the MicrosoftOffice365.Activities.Excel.WriteRange.

For some reason the formatting changes and data is inserted incorrectly, for example:

In my Excel workbook, in one of the cells I have a number: 230924500000233
When it is inserted into the Sharepoint, the number changes to: 230925000000000

Any idea how I can prevent Excel from changing the value of the number? There is no ‘Preserve Format’ option in Office365…

2

Hi @dbukk

In the source Excel workbook, format the cells containing the numbers as “Text” before copying the data to SharePoint.
Similarly, in the destination Excel workbook in SharePoint, ensure that the target cells are also formatted as “Text” to maintain the original data format.
Use the “Excel Application Scope” activity in UiPath to open and manipulate the source Excel workbook.
Ensure that there are no formulas in the source Excel workbook that might be altering the data.
Consider converting the data to a string format before writing it to SharePoint to ensure that Excel doesn’t alter the numeric values.
In SharePoint, make sure that the cells you are writing data to are set up with the desired format.
Check if there are any data validation rules or conditional formatting rules in either the source or destination workbook that might be affecting the data.

Cheers…!

3

While reading itself with READ RANGE activity you can have format preserved
Enable PRESERVE FORMAT property in READ RANGE activity and give a try

Cheers @dbukk

4

Hello @Palaniyappan

Thanks for replying. I tried this, but the problem lies withing writing range to the Sharepoint Excel Spreadsheet. You are not able to Preserve Format when inside the Office365 scope.

5

Include a single quote before to all the value in that column so that format remains same

Refer this

Cheers @dbukk

6

Good point, tried that as well. Although it does work in Excel desktop, it does not work when copying from Excel Desktop and pasting to Excel Sharepoint. The apostrophes are visible and spoil the datatable values.

For example:
In Excel desktop I have a value: 2123+2 which is roughly 212312342
When I add an apostrope to it and paste it to Excel Sharepoint, it has a value: '2123+2

which is not something I’m trying to achieve :frowning: