Efficient way to having calling workbook path in custom function context

[odedb-datarails]

Hi,

What is the best way for having the workbook file path within the context of a single function call?

We have a class for custom functions, and during the process of one of the functions we require the filepath for the workbook in order to get certain information necessary its functionality. The current solution we have using XlCall is pretty expensive performance wise.

Does anyone have any tips for how to solve this without causing issues with multiple open workbooks?

Thanks

[govert]

xlfCaller is the best way to get the calling range for the function. From there, I'm not sure how you are getting the full path.
Maybe you're using xlfGetDocument(2, caller) in a function that is marked as MacroType=true.
If you're using some COM calls to get the full path, that might be a bit slower.

You should try to figure out whether xlfCaller is slow, of getting the path from there.
For the path, you might cache the information against the SheetId that you find in the ExcelReference you get back from xlfCaller.
I'm not sure what happens when the workbook path or name is changed with "Save As" - if that's likely then caching needs to takes ome care.

If any of this doesn't make sense, feel free to ask more.

[odedb-datarails]

This is our current flow:

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 = GetFileNameUsingApplication(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 GetFileNameUsingApplication(path2.ToString()); // path1 returns error. path2 is filename (book2) without extension.
}

Do you think we somehow do it with less calls?

[govert]

What does GetFileNameUsingApplication do?

[odedb-datarails]

Using our cache for current open workbooks to get the full path from the filename.

This is the case we need to solve, getting the correct filepath when we could have multiple workbooks open all with UDFs and we need to from which file its being called.

[govert]

I don't understand.
path1 will contain the directory (if the file has been saved) and path2 will contain the file name.
Apart from the OneDrive story, this might work:

    [ExcelFunction(IsMacroType = true)]
    public static object TryGetCallingFileName()
    {
        var caller = XlCall.Excel(XlCall.xlfCaller);
        var callerSheetName = XlCall.Excel(XlCall.xlSheetNm, caller);
        var callerPath = XlCall.Excel(XlCall.xlfGetDocument, 2, callerSheetName);
        var callerFileName = XlCall.Excel(XlCall.xlfGetDocument, 88, callerSheetName);
        return callerPath.Equals(ExcelError.ExcelErrorNA) ? callerFileName : Path.Combine((string)callerPath, (string)callerFileName);
    }