Error on numberFormatLocal on Office Scripts

Question

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.

taller · Accepted Answer · 2024-11-21 07:28:05Z

0

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\\.");

edited Nov 21, 2024 at 7:28

answered Nov 18, 2024 at 18:14

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pascal Over a year ago

Thx for the suggestion, but I get still the same error: The argument is invalid, missing, or in incorrect format.

taller Over a year ago

Please check the cell format after running the VBA. Screenshot. I'm not sure if it is related to the Windows region settings. I've tested the OfficeScript without issure.

Pascal Over a year ago

Hi taller Thanks a lot for Your suggestions! The formating in the cell is described the following way: ## \S\tk\.

Pascal Over a year ago

Screenshot: postimg.cc/zykjTgDK

taller Over a year ago

I've update my answer. Pls try the 2nd code line.

|

Collectives™ on Stack Overflow

Error on numberFormatLocal on Office Scripts

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related