Availability of a Workbook ID property similar to Worksheet ID?

[louwill]

Hi Govert,

I'm moving a lot of heavy/high memory functions out of the Excel process and want to pass information identifying the caller from my UDF into my out of process function.  Ideally, I'd like to pass some type of ID associated with the calling workbook to my out of process function that will then be used for cache lookups.

I know I can pull the calling sheet ID using XlCall.Excel(XlCall.xlfCaller) via the SheetId property, but I don't need worksheet-level caching.. just workbook.  Anything that I can use in the Excel Workbook object to help facilitate this?  I could use the Path property, but want something a bit more 'static' if that makes sense.

Thanks,

Will

[Naju Mancheril]

Hi Will,

Can you clarify exactly what purpose the workbook ID serves in this model? If you have some function f(x,y,z), then I would think that your external process (service) should return the same result for a given choice of x, y, and z, and that the result should not depend on who called the function. This model has several advantages, such as the easy ability to insert a cache between the caller and the service. Also, by making the service agnostic as to whether it is serving one workbook or N workbooks, it gives you the flexibility to move the service onto another machine, or use a single service on the network to serve multiple clients.

That said, if you really want to pass some kind of workbook ID to the service (perhaps for debugging), what do you want the lifetime of the ID to be? Does it need to carry over from one Excel process to another (e.g. if you close and reopen the workbook on a later date?). If so, then you need to make the ID be part of the state (or derivable from the state) of the workbook. For example, you may be able to generate/save a GUID as a workbook property and reuse it. On the other hand, if you just need the ID to live as long as the workbook is open, you can generate the GUID in memory within your addin code, stash it in a static property, and use that for each call. The next time you open the workbook, the property will be reinitialized. If you take this route, I would store these IDs in a Dictionary<string, ID> where string is the workbook name, and be sure to clear out the entry when the workbook is closed.

Hope this helps,

Naju

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/392e6f94-f3e7-435a-ace3-59cc80a5fbban%40googlegroups.com.

[louwill]

Hi Naju,

Thanks for the reply.  The purpose of the workbook ID is to match open workbook(s) to cached data models.  The user may have multiple workbooks open simultaneously linked to different versions of the cached data model.  When the UDF is evaluated, I need to pass this ID/key associated with the calling workbook to my out of proc service to know which cached data model to use and retrieve data from based on the version of the data model that was linked in my Excel workbook.

Ideally, I'd like this workbook ID to live as long as the workbook is open.  It does not need to carry over between workbook close and workbook open--I can reestablish the connection at workbook open.  I think I found a solution that will work for my needs.. let me know what you think.  Basically, I need to get the calling reference to get the calling sheet name.  Once I have the calling sheet name, I can get the calling workbook using XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName).  workbookName2 won't work in this case because that only returns the active worksheet name.  This won't work if the user does a global recalculation (i.e. CTRL+ALT+F9).  Once I know the calling workbook name, find that name in my static dictionary to get my workbook ID I created on workbook open.  Since there can only be one workbook open at the same time with the same name, there won't be a chance of a collision.

// get calling excel reference

if (XlCall.Excel(XlCall.xlfCaller) is ExcelReference reference)

{

    // get calling sheet name

    var sheetName = XlCall.Excel(XlCall.xlSheetNm, reference) as string;

    // get calling workbook name (will work because this returns the calling workbook based on the calling sheet)

    var workbookName1 = XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName) as string;

    // get calling workbook name (won't work because this returns the active workbook as opposed to the calling workbook)

    var workbookName2 = XlCall.Excel(XlCall.xlfGetWorkbook, 16) as string;

                

    System.Diagnostics.Debug.WriteLine($"workbookName1: {workbookName1} / workbookName2: {workbookName2}");

    // todo: lookup workbook name in static dictionary to retrieve workbook id created at workbook open

}

Thanks,

Will

[Govert van Drimmelen]

I would suggest parsing the string returned from xlSheetNm directly.

It contains the process-unique workbook name and the sheet name, so it’s easy to pull out the workbook name only.

A disadvantage of calling the xlfGetXXX function from a UDF is that you have to mark the function as IsMacroType=true, which may have other side effects.

 

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c0b70ebc-c641-46dd-9bf9-7309f3db063en%40googlegroups.com.

[louwill]

Got it.  I was wondering if there were downsides to having to mark as IsMacroType.  Thanks for your help.

Will