HOWTO: How do I use Excel's COM interface from an XLL?

How do I use Excel's COM interface from an XLL?

Issue

I want to use the Excel object model from an XLL. How do I do it?

Summary

1. Use #import statements to include the Excel type library and the libraries it depends on.
2. Get a handle to the Excel Application object using CXllApp::GetApplicationDispatch().
3. Use Excel object model pointers, in the Excel:: namespace, to convert the late-bound IDispatch objects returned by the Excel into typed references.
4. Only use the COM API from macro functions. If you use it in a worksheet function Excel may crash.
5. For details, see the sample project in q0044_sample.zip. XlComApi.sln is a Visual Studio .NET 2002 solution. XlComApi71.sln is a Visual Studio .NET 2002 solution. XlComApi8.sln is a Visual Studio 2005 solution.

#import

To include the Excel object model in your library, add the following lines near the top of your application header file:

// Import MSO
#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \
    rename("RGB", "RGB1") \
    rename("DocumentProperties", "DocumentProperties1")

// Import VBA
#import "libid:0002E157-0000-0000-C000-000000000046"

// Import Excel
using namespace Office;
#pragma warning(push)
#pragma warning(disable : 4192)
#import "libid:00020813-0000-0000-C000-000000000046" \
    rename("RGB", "RGB1") \
    rename("DialogBox", "DialogBox1") \
    rename("VBProject", "VBProject1") \
    rename("VBProjectPtr", "VBProjectPtr1") \
    rename("CopyFile", "CopyFile1") \
    rename("ReplaceText", "ReplaceText1")
#pragma warning(pop)

Alternatively, just include the header file which contains all the declarations above:

#include "ImpXlCom.h"

Excel Application object

Within your code, you can create a handle to the Excel Application object as follows:

Excel::_ApplicationPtr app(XllGetApp()->GetApplicationDispatch());

You can then use the methods and properties of the Application object, or you can get handles to other objects in the model, such as Workbooks, Worksheets or Ranges.

For eaxample, the code below hides the Formula Bar:

try
{
    Excel::_ApplicationPtr app(XllGetApp()->GetApplicationDispatch());
    app->DisplayFormulaBar[0] = VARIANT_FALSE;
}
catch(_com_error e)
{
    CXllApp::XlMessageBox(CString(_T("Failed to set Application.FormulaBar: ")) 
        + e.ErrorMessage(), XlMessageBoxTypeExclamation);
}

The COM wrapper code that is generated by the #import statement may throw an exception at any time, which must be caught, as shown above.

IDispatch pointers and typed pointers

Many Excel methods and properties return a generic IDispatch pointer, which you must convert to a typed smart pointer before use. This approach will also ensure that object handles are released appropriately.

(The Excel object model is designed for use with late-binding, a feature of Visual Basic which is not available in standard C++. So the code in C++ is generally much longer than its Visual Basic equivalent.)

The smart pointer types are usually named Excel::ThingPtr, where "Thing" is the Visual Basic name for the object, e.g.: Excel::SheetsPtr. Where naming conflicts require it, the name may be prefaced by an underscore, e.g.: Excel::_ApplicationPtr, Excel::_WorksheetPtr.

The code below shows how to add a button to the active worksheet:

try
{
    Excel::_ApplicationPtr app(XllGetApp()->GetApplicationDispatch());
    Excel::_WorksheetPtr ws(app->ActiveSheet);
    Excel::ButtonsPtr buttons(ws->Buttons(vtMissing, 0));
    Excel::ButtonPtr button(buttons->Add(48.0, 38.25, 96.0, 38.25));
    button->Caption = "MyButton";
    button->OnAction = "ToggleFormulaBar";
}
catch(_com_error e)
{
    XllGetApp()->XlMessageBox(CString("Failed to create button: ") 
        + e.ErrorMessage(), XlMessageBoxTypeExclamation);
}

A less verbose version would be:

try
{
    Excel::_ApplicationPtr app(XllGetApp()->GetApplicationDispatch());
    Excel::ButtonPtr button = Excel::ButtonsPtr(Excel::_WorksheetPtr(app->ActiveSheet)
        ->Buttons(vtMissing, 0))->Add(48.0, 38.25, 96.0, 38.25);
    button->Caption = "MyButton";
    button->OnAction = "ToggleFormulaBar";
}
catch(_com_error e)
{
    XllGetApp()->XlMessageBox(CString("Failed to create button: ") 
        + e.ErrorMessage(), XlMessageBoxTypeExclamation);
}

Sample

The sample project containing the code discussed above can be downloaded here.