HOWTO: How do I call COM objects from an XLL?

How do I call COM objects from an XLL?

Issue

I want to use ADO (COM) objects from an XLL. How do I do it?

Summary

1. Use #import statements to include the libraries (or type libraries) that you need.
2. Declare any library-level COM objects (e.g. a database connection) in your application class. Initialize and terminate them in OnXllOpenEx() and OnXllClose()
3. Use _variant_t and bstr_t types to wrap up arguments passed to COM methods.
4. For details, see the sample project in q0043_sample.zip. AdoAddin.sln is a Visual Studio .NET 2003 solution. AdoAddin8.sln is a Visual Studio 2005 solution.
5. For more information about using ADO from C++, see Microsoft's web-site.

#import

To include ADO in your library, add the following line near the top of your application header file:

#import "C:\Program Files\Common Files\System\ado\msado15.dll"

Unfortunately, this leads to a compiler error, because of a conflict with stdio.h, so you must also add three more lines:

#ifdef EOF
#undef EOF
#endif
#import "C:\Program Files\Common Files\System\ado\msado15.dll"

Application-level objects

Within your application class, add the declaration of a COM pointer.

// Names
public:
    static LPCSTR m_pszDefName;

// Data
    ADODB::_ConnectionPtr m_conn;
    bool m_connectionIsOpen;

// Overrides

In your application class constructor, initialize the data members.

CAdoAddinApp::CAdoAddinApp()
: m_connectionIsOpen(false),
  m_conn("ADODB.Connection")
{
    ...
}

In your class's override of OnXllOpenEx(), start using any data members that will need to be available to add-in functions, eg:.

BOOL CAdoAddinApp::OnXllOpenEx()
{
    ...

    // Prepare arguments for ADODB::_Connection::Open
    bstr_t connectionString((LPCSTR)strConnection),
           userID(L"Admin"), 
           password(L"");
    long options = 0;
    try
    {
        m_conn->Open(connectionString, userID, password, options);
        m_connectionIsOpen = true;
    }
    catch(_com_error e)
    {
        XlMessageBox(CString("Failed to open connection: ") + e.ErrorMessage(), XlMessageBoxTypeExclamation);
        return FALSE;
    }
    return TRUE;
}

Note the error-trapping using try...catch, and also note that we report the error and return FALSE if the procedure fails.

Dispose of any application-level objects in OnXllClose(), e.g.:

void CAdoAddinApp::OnXllClose()
{
    if (m_connectionIsOpen)
    {
        m_conn->Close();
        m_connectionIsOpen = false;
    }
}

Add-in functions

In your add-in function, make sure that you wrap any IDispatch interfaces using _variant_t, and any strings using bstr_t, e.g.:

//{{XLP_SRC(GetNames)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(GetNames, "RC", "GetNames", "Filter", "Database"
    , "Get names from table", "Filter applied to selection\000",
    "\0appscope=1\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER GetNames(const char* Filter)
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    // Build query
    CString query = "SELECT Name From Table1";
    if (Filter[0] != 0)
        query += CString(" WHERE Name LIKE '") + Filter + CString("'");

    // Run query
    ADODB::_RecordsetPtr rs("ADODB.Recordset");
    _variant_t source((LPCTSTR)query);
    _variant_t conn((IDispatch*)(XllGetTypedApp()->m_conn), true);
    try
    {
        // Open recordset
        rs->Open(source, conn, ADODB::adOpenForwardOnly, ADODB::adLockOptimistic, 0);
        std::vector<CString> names;
        // Read each record
        while (!rs->EOF)
        {
            names.push_back((char*)(_bstr_t)rs->Fields->Item["Name"]->Value);
            rs->MoveNext();
        }
        rs->Close();
        xloResult = names;
    }
    catch(_com_error e)
    {
        xloResult.Format("#ERROR: %s", e.ErrorMessage());
    }
    return xloResult.Ret();
}

Sample

The sample project discussed above can be downloaded here.