Free Support Forum -

Apostrophe in formula tab name

I'm having a problem with formulas with an apostrophe in the tab name. My understanding is that the standard way in Excel for dealing with an apostrophe is to use a double apostrophe escape sequence. When I do this the formula seems to be fine, but when I open the Excel doc, the formula has an added apostrophe at the beginning and end. So ='Sheet''s Name'!A1 becomes =''Sheet''s Name'!A1'()

Aspose.Excel v

//x is a valid Aspose.Excel instance
Worksheet ws = x.Worksheets["Sheet's Name"];
Worksheet anotherWS = x.Worksheets["Another Worksheet"];
string formula = "=\'" + ws.Name.Replace("'", "\'\'") + "\'!" + "A1";
//or: string formula = "='" + ws.Name.Replace("'", "''") + "'!" + "A1";
//produces: "='Sheet''s Name'!A1"

anotherWS.Cells[10, 10].Formula = formula;
//but this is in formula when the worksheet is opened
//=''SHEET''S NAME'!A1'()

Please try this attached fix.

Thank you. This fixed the apostrophe issue as well as the SUMPRODUCT formula issue.

There still seems to be issues with other special characters in formulas. I can't use "(" in a formula at all

Aspose.Excel.ExcelException: Invalid formula:\"='Sheets(1) Name'!A1\".

Not able to reproduce this:
and when I use "&" combined with a double apostrophe "''"
Aspose.Excel.ExcelException: Invalid formula:\"='Sheet''s & Name'!A1\".

Not sure about any other special characters, but I will let you know if I run across additional issues.

Please try this attached fix.