I want to automize some format work in one of my excel files. Therefore I used macros for a long time - now it's time to move to scripts I thought.

In the old world the cutsom style formating was done the following way:

    With Range(SB_TABLE_NAME + "[Menge Geliefert]")
        .NumberFormat = "## \Stk\."
    End With

With scripts its nearly the same

    cellColumn = sbOrderTable.getColumnByName("Menge Geliefert").getRange()
    cellColumn.setNumberFormatLocal("## \S\tk\.");

I also used the recording to be sure the number format is correct (!) but as soon as I do run the script I get the following error: Range setNumberFormatLocal: The argument is invalid, missing, or in incorrect format.

Do anyone has an idea why the recording is storing the user defined format but the running script failes with it? I also used the getNumberFormatLocal() to get the current formating - result: "## \S\tk." - so I get a little confused...

As described I wanted to set a range of cells (a column in a table) to a specifig fomrat.

I want to automize some format work in one of my excel files. Therefore I used macros for a long time - now it's time to move to scripts I thought.

In the old world the cutsom style formating was done the following way:

    With Range(SB_TABLE_NAME + "[Menge Geliefert]")
        .NumberFormat = "## \Stk\."
    End With

With scripts its nearly the same

    cellColumn = sbOrderTable.getColumnByName("Menge Geliefert").getRange()
    cellColumn.setNumberFormatLocal("## \S\tk\.");

I also used the recording to be sure the number format is correct (!) but as soon as I do run the script I get the following error: Range setNumberFormatLocal: The argument is invalid, missing, or in incorrect format.

Do anyone has an idea why the recording is storing the user defined format but the running script failes with it? I also used the getNumberFormatLocal() to get the current formating - result: "## \S\tk." - so I get a little confused...

As described I wanted to set a range of cells (a column in a table) to a specifig fomrat.

• office-scripts

1 Answer 1

The backslash in ## \Stk\. is an escape character. Pls change it to ! in OfficeScript.

cellColumn.setNumberFormatLocal("## !Stk!.");

Pls try below code, it creates same number format ## !Stk!. on my pc.

selectedSheet.getRange("a1").setNumberFormatLocal("## \\S\\tk\\.");