Format Excel output to change numbers to text and display as currency
Karen asked 9 months ago
My map output is an Excel file. The columns are all displaying as Text. I need to convert some of the columns to numbers, and set the number format to currency.
I found some tips in Sherry Whitten’s 7/20/17 post (https://www.eonesolutions.com/discussion/formatting-an-exported-excel-spreadsheet/), but I’m not sure how/where to apply them. Here are the tips she shared:
‘Format columns with correct number format
xlWorkSheet.Columns(“Q:V”).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
xlWorkSheet.Columns(“N:O”).NumberFormat = “#,##0_);[Red](#,##0)”
‘Format numbers stored as text to numbers
xlWorkSheet.Columns(“Q:V”).Value = xlWorksheet.Columns(“Q:V”).Value
xlWorkSheet.Columns(“N:O”).Value = xlWorksheet.Columns(“N:O”).Value
I found some tips in Sherry Whitten’s 7/20/17 post (https://www.eonesolutions.com/discussion/formatting-an-exported-excel-spreadsheet/), but I’m not sure how/where to apply them. Here are the tips she shared:
‘Format columns with correct number format
xlWorkSheet.Columns(“Q:V”).NumberFormat = “#,##0.00_);[Red](#,##0.00)”
xlWorkSheet.Columns(“N:O”).NumberFormat = “#,##0_);[Red](#,##0)”
‘Format numbers stored as text to numbers
xlWorkSheet.Columns(“Q:V”).Value = xlWorksheet.Columns(“Q:V”).Value
xlWorkSheet.Columns(“N:O”).Value = xlWorksheet.Columns(“N:O”).Value
Answers
Lorren Zemke Staff answered 8 months ago
Karen,
The formatting process would be a Post document Script task that uses the Excel Interop code to open the file and basically run the steps Sherry shared. It’s like running a macro but it happens within the script logic that is written.
Lorren
The formatting process would be a Post document Script task that uses the Excel Interop code to open the file and basically run the steps Sherry shared. It’s like running a macro but it happens within the script logic that is written.
Lorren