Unanswered

How to write datatable in excel using power automate desktop while preventing large numbers to be converted to an exponent?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I am having an issue when writing a data table to power automate desktop, the issue occurs when a cell has a large number, since it converts the data to exponent. Below is how I am writing the data table to excel.

flow

The data table as it appears on PAD before pasting it in excel.

Data table

This is how the data appears in excel

Excel Excel

How do I prevent PAD from reformatting the data before pasting as now its distorting a reference which is creating an issue in referencing.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @Anonymous

I tried below flow

I used a vbscript to change the format of column, in my case column is at location A.
vbsript used :

'Create an Excel application object Set objExcel = CreateObject("Excel.Application") 'Open the workbook Set objWorkbook = objExcel.Workbooks.Open("%FilePath%") 'Select the worksheet Set objWorksheet = objWorkbook.Worksheets("Sheet1") 'Select the column you want to format and set the format to number objWorksheet.Range("A:A").NumberFormat = "0" 'Save and close the workbook objWorkbook.Save objWorkbook.Close False objExcel.Quit 'Release the object variables Set objColumn = Nothing Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing
Here i have used A:A means first column (based on column letter) After writing the data, i have used resized rows or column action to resize like below

Hope this helps
Mark it as solution if it resolves your query 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Nived_Nambiar
I am launching a black excel document so using %FilePath% will not work for my case, is there a way to write a vb script for a blank document.

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

Then use vbscript at last step of the process, after close excel action.

Hope that would helps 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Nived_Nambiar

Tried that, the problem is that the value already changed, so the Reference is invalid. i.e. for the data above, when I convert to text at the end from the exponent, data is as below;

30932101251012510 is converted to 30932101251012500.

Was this reply helpful? Yes No
Nived_Nambiar 18,138 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous

After some tryouts, i was able to figure out why excel is writing 30932101251012510 as 30932101251012500, the reason is excel can have maximum 15 significant digits while for 30932101251012510 has significant digit >15 so it converts to nearest number of lesser significant digits.

To resolve this, i have tried this way.

in write cell, along with number, i have specified apostophe at starting like below

So excel writes the data like this

Even reading the data from excel using PAD, it will read as 30932101251012510

Hope this helps 🙂

Mark it as solution if it resolves your query 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Nived_Nambiar

Thanks for the much needed assistance, the solution was formatting an excel file column as text before pasting data to it, so created a file with formatted columns then pasted the datatable and save it with a different file name so that the formatted file remains as an input.

Was this reply helpful? Yes No
Riyaz_riz11 4,150 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

Better you can use template file make the column format as a TEXT FORMAT.
Once done Launch template excel and save that into output folder.

Regards
Ahammad Riyaz
--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Was this reply helpful? Yes No