How to work around iManage limitation in Excel VSTO addin

Question

We have developed a VSTO addin for Excel, which pulls excel sheets from a web server, and allows users to manipulate the data on the sheet. It works with a local copy of the file but we don't really care about that copy. But some of our clients have "iManage Integration for Office" installed as well, and these clients experience odd behaviour. In this environment we are not able to cancel the Close event.

More specifically, if a user opens one of our files and makes changes, then closes the file, our event handler fires and prompts them to save changes (to the data on the server). If they choose Cancel, or if they choose Save and the save fails for some reason, we set Cancel = true in an attempt to keep the file open. Ordinarily this works perfectly.

For those clients with iManage, the file closes anyway. If our code has saved the local copy of the file, then the file just closes. If the local copy has not been saved, the user gets another prompt after our prompt, from iManage, asking if they want to save the file. From here, the user could click Cancel and the file remains open. But the users are reporting that it's confusing to see the second prompt after clicking Cancel on the first. And they are not willing to disable the iManage addin. We would like to be able to keep the file open in this case, preferably without seeing the iManage prompt.

Working in a test environment a client set up for me (Excel 2010), I have tried a few things:

• I tried setting Cancel on the workbook level BeforeClose and the applicationlevel WorkbookBeforeClose; neither one worked (file still closes whenever iManage is enabled)
• I tried to make sure my handler for WorkbookBeforeClose is registered last, by registering it in the workbook level event handler; no change
• I can use the iManage ribbon to manually switch to Local Mode, which makes everything work, but I don't know whether/how I can make that change through code.
• I can find the iManage addin in Globals.ThisAddIn.Application.COMAddIns; I tried setting its Connect = false, but this gives an error to the effect that only an administrator can connect/disconnect the addin.
• I can save the local file during the Closing event, then do SaveAs to create a second copy; the second copy is now active and iManage closes it; then I reopen the original local file. It looks pretty good to the user, but then I have a bunch of COM references to cells on the old file, and these are all garbage. I can probably loop through and serialize them and recreate them with the new file, but it will be time consuming to code and to run, so I'm looking for other ideas first.

Is there any way I can keep the file open, without making the users do anything extra?

UPDATE

Using iManage 9.3.0.0 and Excel 2010, I created a VBA macro with a reference to Worksite Integration Interfaces Library(Ex). The event fires, but the file still closes. If I don't set the Saved property, and I type on the grid, I always get a prompt from iManage asking if I want to save.

Private WithEvents oWS As iManageExtensibility

Private Sub oWS_DocumentBeforeClose2(ByVal Doc As Variant, IgnoreIManageClose As Boolean, Cancel As Boolean)
    IgnoreIManageClose = True
    Cancel = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set oWS = Application.COMAddIns("WorkSiteOffice2007Addins.Connect").Object
    Cancel = True
    ActiveWorkbook.Saved = True
End Sub

Answer 1

You do not need to disable the iManage integration add-in for Office. Your application should detect the presence of the iManage integration add-in and then handle that add-in's own equivalent Close event

To do that, first get an instance of the iManage add-in by examining the Excel Application.COMAddins collection and fetching back the COM add-in instance with a ProgID of either WorkSiteOffice2003Addins.Connect (Excel 2003 or earlier) or WorkSiteOffice2007Addins.Connect (Excel 2007 or later).

Actually there should also be a so-called "backwards compatibility" add-in installed also registered with a ProgID of oUR02k.Connect which you may reference instead. Again, whether that is installed or not depends somewhat on the version of FileSite/DeskSite that is installed on the machine.

That all may seem confusing to you however it's because the add-ins have changed over the years, so it rather depends on how old the iManage client is (i.e. FileSite, DeskSite) as well as the version of Office you're targeting. You may need to compensate for different Excel/iManage client versions in your code

Once you have the right COM add-in reference, examine the COMAddin.Object property. This value represents an instance of the iManage Office integration add-in

From there you can cast that object to the strongly typed COM interface of iManageExtensibility

You're then able to hook into all the Office application events that iManage have hijacked (much like you have done in your application) and respond to those events rather than the native Excel ones.

In your case you will need to monitor the DocumentBeforeClose2 event. Note the character '2' at the end. There is also a legacy event named DocumentBeforeClose but the newer DocumentBeforeClose2 has the following method signature:

DocumentBeforeClose2(object doc, ref bool ignoreimanageclose, ref bool cancel)

Finally, in your DocumentBeforeClose2 event handler add your business logic for cancelling the close event and/or the iManage close event. You do that by setting the ignoreimanageclose and/or cancel Booleans to true/false as appropriate. The doc parameter in this case will be the Excel workbook instance, so you may safely cast it to the Excel.Workbook interface if you wish

PS: if you are developing against the iManage APIs and require support you should consider purchasing the iManage SDK. That SDK contains help on various APIs, code samples, and crucially I believe it gives you access to some dev support.