When I write from data table into excel worksheet the numbers are stored as text. I need them to be numbers so I can put in formulas and have the calculations work properly. I have tried several things.
Changed format of all cells to ‘number’ (didn’t work).
Made sure I was using .xlsx and not .csv file.
Changed column type on data table to ‘numbers’ and then did write from data table (didn’t work).
Turned off the error checking in Excel Options (didn’t work).
What am I doing wrong?? How do I fix?
Page 1 / 1
Hi @Ken 3236 ,
You can follow any of the below methods to resolve this issue.
Try after disabling Excel Auto checking option. Follow the below steps to disable the same.
Under Excel menu, go to the Options.
Select Formulas.
Uncheck the Enable background error checking option.
Before the changes
After the changes
Or
The easiest way is to have an empty excel with the name temp.xlsx and set the column formats as required in your output excel. Now simply write range your Data Table in it and rename it.
For e.g. if you want to preserve your first column as number then in temp.xlsx set it as number and when you write the data table to it, your format will be preserved.
Hope this helps.
None of that worked for me. I did find out that if I use a data table I can loop through and set cell by cell which preserves the number format. Appreciate the help!
Try using Get Multiple Cells to get all the data. Make sure in this action you are reading cell values and NOT visible text in cell.
Try using Get Multiple Cells to get all the data. Make sure in this action you are reading cell values and NOT visible text in cell.
Sorry, misunderstood. You’re going OUT to excel not in from excel...