-1

Looking for a way to convert a column from "General" to "Number" format in excel using VBScript in Power Automate Desktop

I have an excel workbook (xlsx) that can be populated by end users in one of two ways.

  1. They have formulas setup to populate the values
  2. They manually enter it

In both cases there tends to be an issue with the values they are populating for a specific column. The values are in a funky exponential format

Example:

Valid Number= 149300952822
Copied Result= 1.49301E+11

I'm looking for a way to update a specific columns format to be number format without decimal places,after the users have entered their values.

I tried a few different things, but can't seem to get it to work.

Here is what I tried, any help would be greatly appreciated Note: The length of the number value to be entered into this column varies, so I don't want to set a specific number of digits. Just want to have it formatted as a number without decimal places (I'm sure I am way off in what I tried below)

'Opens the Excel file'

    Set objExcel = CreateObject("Excel.Application")

    Set objWorkbook = objExcel.Workbooks.Open("%TempFile%")

    Set INVSheet = objWorkbook.Sheet("INV")

    objExcel.Application.Visible = True

'Change format from text to number

    objWorkbook.INVSheet.Columns("P:P").NumberFormat ="0"

'Saves and closes the Excel file'

    objWorkbook.Save

    objWorkbook.Close SaveChanges = True
ckatz
  • 23
  • 8
  • try ("P:P").NumberFormat = " #,##0;" – xShen Mar 20 '23 at 19:22
  • Just one word, as we do not know how your current code is failing to change the format, If the number is stored as text, then no `NumberFormat` will change it. You will first need to change it to a number, but realize that the number will be truncated to the significant digits you "see" as the text did not store anymore sig digits. – Scott Craner Mar 20 '23 at 19:35
  • Thank You both, I figured out a different solution – ckatz Mar 21 '23 at 13:39

1 Answers1

-2

Found an alternate solution by converting it to JSON and it somehow takes care of the issue

ckatz
  • 23
  • 8