XlCall.Excel(XlCall.xlSheetNm, reference) replaces box brackets with round brackets

[shlomizeevy]

Hi there,

I created an Excel Addin using Excel.Dna.I am using ExcelDna.Integration version 1.6.0.
In a custom ExcelFunction function I am trying to get the calling file name using the following code:

` public string TryGetCallingFileName()
{
var reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
var sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference);

        var path1 = XlCall.Excel(XlCall.xlfGetDocument, 2, sheetName);
        var path2 = XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName);

        if (path1 is string && path2 is string)
        {
            var pStr1 = path1.ToString();
            var pStr2 = path2.ToString();
            if (FilesUtils.IsLocalFile(pStr1)) // if the path1 is directory -> it's a local file.
            {
                return Path.Combine(FilesUtils.NormalizeDriveLetter(pStr1), pStr2);
            }

            return $"{pStr1.TrimEnd('/')}/{pStr2}"; // one drive case.
        }

        return path2.ToString(); // path1 returns error. path2 is filename (book2) without extension.
    }`

The issue is that file names that contain box brackets, the brackets are replaced by round brackets.
For example, instead of 'book [2]' I'll get 'book (2)
The issue relies on (string)XlCall.Excel(XlCall.xlSheetNm, reference); which concatenates file name with sheet name.
So for sheet name 'Sheet1' I'll get '[book (2)]Sheet1'.

What is the best solution to overcome this?

Thanks,
Shlomi

[govert]

That's an interesting edge case!

It looks like you can take the workbook name you get back from xlSheetNm as an index into the Application.Workbooks collection, and then call FullName to get the real file name.

?Application.Workbooks("Book (3).xlsx").FullName
C:\Temp\Book [3].xlsx

[govert]

I should say the file name you get back from xlfGetDocument, 88 as an index.