How to determine if a workbook is open

Nov 19, 2013 at 4:24 PM
Hi,

I have an XLL Add-In am attempting to determine if a workbook is currently open when the user selects an item from the Add-In menu.

Currently, I am getting the application object as:
Microsoft.Office.Interop.Excel.Application excelApp =  ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;
Unfortunately, if no workbook is open then I am getting a System.InvalidOperationException with the message Excel API is unavailable - cannot retrieve Application object on the instantiation.

How should this check be done? I want to display a messagebox to the user if there is no ActiveWorkbook.

Thanks
Coordinator
Nov 19, 2013 at 5:13 PM
Hi Lee,

Getting hold of the Application object if there has never been a workbook open can be very hard - Excel only initializes the COM object model when the first workbook is opened. Excel-DNA tries a few things to work around this, and I'll have a look again at whether I can recreate and fix the case you describe.
What versions of Excel-DNA and Excel are you encountering this with?

I think you can assume that the most likely cause of an error when calling ExcelDnaUtil.Application is that there is no workbook open, so maybe catch the error and display your 'no workbook' message.

Regards,
Govert
Nov 19, 2013 at 5:21 PM
Hi Govert,

I am testing with Excel 2007 and am using version 0.3 of Excel-Dna. I thought about doing the exception handling the way you described but was curious if there was a different method.

I am working on the portion of my XLL that will display forms and interact with the workbook and want to make sure that a workbook is open.

Thanks,

Lee
Coordinator
Nov 19, 2013 at 5:24 PM
Hi Lee,

Could you perhaps check with the latest check-in (from https://exceldna.codeplex.com/SourceControl/latest)?

There have been some changes in how I try to get the Application object, so at least knowing whether the problem is still there present be a help.

Thanks,
Govert
Nov 19, 2013 at 5:53 PM
Hi Govert,

In order to integrate that into my current XLL, should I do a Nuget get within the project? I am not sure how to upgrade otherwise.

Thanks,

Lee
Coordinator
Nov 19, 2013 at 6:24 PM
I think it's a bit confusing to update if you've used NuGet to get Excel-DNA.
I've not figured out the whole NuGet prerelease story yet.

Maybe just download the latest check-in, then copy the stuff into packages\Excel-DNA... over the existing files.
I think that will work...?
(Maybe after making a backup, before we break everything forever.)

-Govert
Nov 19, 2013 at 9:53 PM
I downloaded and compiled the source. The files below are the latest version (replacing the prior 0.30.22.1 version from the packages\Excel-DNA.0.30.3\tools folder)

ExcelDna.Integration.dll v 0.31.11.1
ExcelDna.xll
ExcelDna64.xll
ExcelDnaPack.exe v 0.31.11.1

The exception is still being raised when I close the workbook and then attempt to access Add-In forms (via the add-in ribbon tab by selecting one of the buttons).

Lee
Coordinator
Nov 19, 2013 at 9:59 PM
Hi Lee,

OK - thank you for confirming.
I'll have a closer look again when I get a chance.

For now I suggest handling the exception, as we discussed.

Regards,
Govert
Nov 25, 2013 at 10:49 PM
Edited Nov 25, 2013 at 10:50 PM
Lee,

I think Govert's suggestion is prudent, and I would offer one other scenario in which this might occur: when an Excel VBA macro is run as a scheduled task and your addin is loaded without a workbook open. As I recall, the LogDisplayForm once complained in this scenario and I had to switch this feature "off".

-BT-