Get Hashcode for Excel Workbook in VSTO to enable buttons based on state

Question

I am creating a VSTO Ribbon AddIn for excel, and I am storing some workbook state information in my application that I use to update visual Buttons Enabled. Considering there can be multiple workbooks I am storing this state object in a dictionary in the ThisAddIn class. My problem is that I don't know how to get a unique Hash/Key/Guid for the workbook because all I get is a COM wrapper that continually changes the hash. fair enough, I totally understand that.

One solution I've used for a long time has been to create a guid and store it in the CustomDocumentProperties for the workbook, and to map the state based on that as a key. This at least works, but it fails if I create a copy of the workbook and open that in the same Application instance and have multiple workbooks with the same guid now..

I just had an idea now that I suppose I could refresh this Guid on the Workbook_Open event. But still this seems like a dodgy solution.

The second solution I found here: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/04efa74d-83bd-434d-ab07-36742fd8410e/

So I used that guys code and created this:

public static class WorkbookExtensions
{
    public static IntPtr GetHashery(this msExcel.Workbook workbook)
    {
        IntPtr punk = IntPtr.Zero;
        try
        {
            punk = Marshal.GetIUnknownForObject(workbook);
            return punk;
        }
        finally
        {
            //Release to decrease ref count
            Marshal.Release(punk);
        }
    }
}

It works very well for a few minutes, until it starts giving me the infamous error "COM object that has been separated from its underlying RCW cannot be used" when accessing the Application.ActiveWorkbook.

Is this a safe way of referencing the Workbook COM object? What if I had two ribbon applications both using this method to get a single workbooks GUID? What if one of those applications runs the garbage collector on my state object, which calls a finalizer to call Marshal.FinalReleaseComObject(workbook)? Is there any way I can get the Ref Count for a Workbook so that I don't call FinalRelease before other Ribbon Apps have finished with them? What are some best practices for cleaning up Workbook COM objects in VSTO to keep playing fair with these other apps?

Surely I'm not the first person to want to have buttons enabled based on Workbook state, how does everyone else do this? I've looked at a few other articles here on Stack Overflow but none quite help me with the Workbook Guid solution.

I am using the Ribbon Designer, and hooking up to the Workbook Load and Deactivate events.

Thanks in advance, hope ive included all the details.

COM object that has been separated from its underlying RCW cannot be used - yeah I hate this error its a PITA, read this: jake.ginnivan.net/vsto-com-interop — Jeremy Thompson
Why do you release the IUnknown pointer in the same function? Try to keep it and release it only when the workbook has been closed or something like that. — Simon Mourier
Brilliant, Thanks @JeremyThompson! There's a lot to learn from that article. The VSTOContrib looks like it would be great for my next project, but it doesn't solve my immediate problems. @SimonMourier - I'm not very knowledgeable with native code, but I suppose if I don't release the pointer here then I'll lose track of it to release later? — stuzor

stuzor stuzor · Accepted Answer · 2014-02-06T16:32:29

I ended up solving this by simply casting the IntPtr to an long, and then the disposal of the IntPtr doesn't affect me. I don't need to preserve the IntPtr because all I really need is something unique about the workbook.

The following code allows me to store Workbook-specific state information, so I can update the visual state of the buttons in my ribbon based on a custom object workbook state. You can store any information you'd like in your custom WorkbookState class, but typically it would be session-specific information you don't want to persist in the spreadsheet itself.

Separate Workbook extensions:

public static class WorkbookExtensions
{
    public static long GetHashery(this msExcel.Workbook workbook)
    {
        if (workbook == null)
        {
            throw new ArgumentNullException("workbook");
        }

        IntPtr pUnknown = IntPtr.Zero;
        try
        {
            pUnknown = Marshal.GetIUnknownForObject(workbook);
            return pUnknown.ToInt64();
        }
        finally
        {
            // GetIUnknownForObject causes AddRef.
            if (pUnknown != IntPtr.Zero)
            {
                Marshal.Release(pUnknown);
            }
        }
    }
}

Then in my VSTO/ExcelDna ThisAddIn class I store a map of all workbook states with the above method to find a unique workbook hash key:

private Dictionary<long, WorkbookState> _workbookStates = new Dictionary<long, WorkbookState>();
public WorkbookState WorkbookState
{
    get
    {
        long hash = Application.ActiveWorkbook.GetHashery();
        WorkbookState state;
        if (!_workbookStates.TryGetValue(hash, out state))
        {
            state = _workbookStates[hash] = new WorkbookState();
        }
        return state;
    }
}

And of course now I can access my WorkbookState from anywhere in my ribbon application by simply calling ThisAddIn.WorkbookState

Get Hashcode for Excel Workbook in VSTO to enable buttons based on state

2 Answers