CODEWITHSUNDEEP
office365office-jsexcel-addins
Manoj Pendyala
Manoj Pendyala

Reputation: 1

TableCollection.add throws 'InvalidArgument' error when the sheet name has special characters

We have built a Excel Task Pane add-in that primarily works with Tables. We have some code that executes TableCollection.add(rangeAddress, true). This call fails when the sheet name in the 'rangeAddress' contain special characters.

For example - 

ctx.workbook.tables.add(Sheet!A1:E4, true)

works fine but,

ctx.workbook.tables.add(Sheet-Name!A1:E4, true)

throws error 

{
"name": "OfficeExtension.Error",
"code": "InvalidArgument",
"message": "The argument is invalid or missing or has an incorrect format.",
"traceMessages": [],
"debugInfo": {
    "errorLocation": "TableCollection.add"
}

}

Please note that the second example address has special character '-' in its sheet name. Is there a way to support special characters in sheet name?

Upvotes: 0

Views: 1293

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

The issue is not with the APIs... per se. Nor is it with tables.

In Excel, if you create a cross-sheet reference, and if the sheet name has non alpha-numeric characters (or something of that sort), you will see that the formula gets written as ='Some-Hyphenated-Sheet'!C27 Note that the sheet name is surrounded with single quotes.

The API is just a reflection of native Excel functionality, so you would need to single-quote the sheet name as well. That is:

ctx.workbook.tables.add("'Sheet-Name'!A1:E4", true)

Upvotes: 1

Related Questions