HOW TO: Handle Events for Excel by Using Visual C++ .NET

Note Microsoft Visual C++ .NET (2002) supports both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code.

This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual C++ .NET and Microsoft Foundation Classes (MFC).

Overview of Event Handling

Visual C++ .NET uses the traditional IConnectionPoint Component Object Model (COM) interface to handle COM events. When you use IConnectionPoint, you must have a class that implements the sink interface for the connection point to which you want to attach. This sample provides the CAppEventListener class to fulfill this requirement.

Create the Visual C++ .NET Automation Client

1. Follow the steps in the "Create an Automation Client" section of the following Microsoft Knowledge Base article to create a basic Automation client:
   307473  (http://support.microsoft.com/kb/307473/EN-US/ ) HOWTO: Use a Type Library for Office Automation from Visual C++ .NET
   In step 1, type MFCExcelEvents for the name of the project.
   
   In step 3, change the ID of the first button to ID_STARTSINK and the caption to Start Event Sink. Add a second button, and then change the ID of the second button to ID_STOPSINK and the caption to Stop Event Sink.
   
   In step 4, select the following Excel interfaces:
   • _Application
   • _Workbook
   • Range
   • Workbooks
2. On the Project menu, click Add Class. Select the Generic C++ class in the list of templates, and then click Open.
3. In the Generic C++ Class Wizard dialog box, type CAppEventListener for the class name, type IDispatch for the base class, and then click Finish.
4. Replace all of the code in Appeventlistener.h with the following:

   #pragma once
   #include "oaidl.h"
   #include "CApplication.h"
   #include "CWorkbooks.h"
   #include "CWorkbook.h"
   #include "CRange.h"
   
   //00024413-0000-0000-C000-000000000046
   const IID IID_ApplicationEvents  = 
   {0x00024413,0x0000,0x0000,{0xc0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
   
   class CAppEventListener : public IDispatch
   {
   protected:
      int m_refCount;
      IConnectionPoint* m_pConnectionPoint;
      DWORD m_dwConnection;
   
   public:
      //Constructor.
      CAppEventListener();
      //Destructor.
      ~CAppEventListener();
      
      /***** IUnknown Methods *****/ 
      STDMETHODIMP QueryInterface(REFIID riid, void ** ppvObj);
      STDMETHODIMP_(ULONG) AddRef();
      STDMETHODIMP_(ULONG) Release();
      
      /***** IDispatch Methods *****/ 
      STDMETHODIMP GetTypeInfoCount(UINT *iTInfo);
      STDMETHODIMP GetTypeInfo(UINT iTInfo, LCID lcid, 
         ITypeInfo **ppTInfo);
      STDMETHODIMP GetIDsOfNames(REFIID riid,  
         OLECHAR **rgszNames, 
         UINT cNames,  LCID lcid,
         DISPID *rgDispId);
      STDMETHODIMP Invoke(DISPID dispIdMember, REFIID riid, LCID lcid,
         WORD wFlags, DISPPARAMS* pDispParams,
         VARIANT* pVarResult, EXCEPINFO* pExcepInfo,
         UINT* puArgErr);
   
      /**** Click Handler *****/ 
      STDMETHODIMP HandleBeforeWorkbookClose( IDispatch* xlBook, 
         VARIANT_BOOL* fCancel );
      STDMETHODIMP HandleSheetChange( IDispatch* xlSheet, IDispatch* xlRange);
   
      /**** Attach/Detach from event source *****/ 
      STDMETHODIMP AttachToSource( IUnknown* pEventSource );
      STDMETHODIMP DetachFromSource();
   };
   						

   Note The value for the IID_IApplicationEvents variable is derived from the Excel object library. For information about how to verify the universally unique identifier (UUID) for a set of Excel events, see the "Excel Application Events" section.
5. Replace all of the code in Appeventlistener.cpp with the following code:

   #include "stdafx.h"
   #include "AppEventListener.h"
   
   //Constructor.
   CAppEventListener::CAppEventListener() :
   m_pConnectionPoint(NULL),
   m_dwConnection(0)
   {
      m_refCount = 0;
   }
   
   //Destructor.
   CAppEventListener::~CAppEventListener()
   {}
   
   /******************************************************************************
   *   IUnknown Interfaces -- All COM objects must implement, either 
   *  directly or indirectly, the IUnknown interface.
   ******************************************************************************/ 
   
   /******************************************************************************
   *  QueryInterface -- Determines if this component supports the 
   *  requested interface, places a pointer to that interface in ppvObj if it is 
   *  available, and returns S_OK.  If not, sets ppvObj to NULL and returns 
   *  E_NOINTERFACE.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::QueryInterface(REFIID riid, void ** ppvObj)
   {
      if (riid == IID_IUnknown){
         *ppvObj = static_cast<IUnknown*>(this);
      }
      
      else if (riid == IID_IDispatch){
         *ppvObj = static_cast<IDispatch*>(this);
      }
   
      else if (riid == IID_ApplicationEvents){
         *ppvObj = static_cast<IDispatch*>(this);
      }
   
      else{
         *ppvObj = NULL;
         return E_NOINTERFACE;
      }
      
      static_cast<IUnknown*>(*ppvObj)->AddRef();
      return S_OK;
   }
   
   /******************************************************************************
   *  AddRef() -- In order to allow an object to delete itself when 
   *  it is no longer needed, it is necessary to maintain a count of all 
   *  references to this object. When a new reference is created, this function 
   *  increments the count.
   ******************************************************************************/ 
   STDMETHODIMP_(ULONG) CAppEventListener::AddRef()
   {
      return ++m_refCount;
   }
   
   /******************************************************************************
   *  Release() -- When a reference to this object is removed, this 
   *  function decrements the reference count. If the reference count is 0, then 
   *  this function deletes this object and returns 0.
   ******************************************************************************/ 
   STDMETHODIMP_(ULONG) CAppEventListener::Release()
   {
      m_refCount--;
   
      if (m_refCount == 0)
      {
         delete this;
         return 0;
      }
      return m_refCount;
   }
   
   /******************************************************************************
   *   IDispatch Interface -- This interface allows this class to be used as an
   *   automation server, allowing its functions to be called by other COM
   *   objects.
   ******************************************************************************/ 
   
   /******************************************************************************
   *   GetTypeInfoCount -- This function determines if the class supports type 
   *   information interfaces or not. It places 1 in iTInfo if the class supports
   *   type information and 0 if it does not.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::GetTypeInfoCount(UINT *iTInfo)
   {
      *iTInfo = 0;
      return S_OK;
   }
   
   /******************************************************************************
   *   GetTypeInfo -- Returns the type information for the class. For classes 
   *   that do not support type information, this function returns E_NOTIMPL;
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::GetTypeInfo(UINT iTInfo, LCID lcid, 
                                          ITypeInfo **ppTInfo)
   {
      return E_NOTIMPL;
   }
   
   /******************************************************************************
   *   GetIDsOfNames -- Takes an array of strings and returns an array of DISPIDs
   *   that correspond to the methods or properties indicated. If the name is not 
   *   recognized, returns DISP_E_UNKNOWNNAME.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::GetIDsOfNames(REFIID riid,  
                                            OLECHAR **rgszNames, 
                                            UINT cNames,  LCID lcid,
                                            DISPID *rgDispId)
   {
      return E_NOTIMPL;
   }
   
   /******************************************************************************
   *   Invoke -- Takes a dispid and uses it to call another of this class's 
   *   methods. Returns S_OK if the call was successful.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::Invoke(DISPID dispIdMember, REFIID riid, LCID lcid,
                                     WORD wFlags, DISPPARAMS* pDispParams,
                                     VARIANT* pVarResult, EXCEPINFO* pExcepInfo,
                                     UINT* puArgErr)
   {
      switch(dispIdMember){
      case 0x00622:
         if(pDispParams->cArgs !=2)
            return E_INVALIDARG;
         else
         {
            if(pDispParams->rgvarg[1].vt & VT_BYREF)
            {
               HandleBeforeWorkbookClose( // Call the function.
                  *(pDispParams->rgvarg[1].ppdispVal),
                  pDispParams->rgvarg[0].pboolVal);
            }
            else
            {
               HandleBeforeWorkbookClose(  // Call the function.
                  (pDispParams->rgvarg[1].pdispVal),
                  pDispParams->rgvarg[0].pboolVal);
            }
         }
   
      case 0x0061c:
         {
            if(pDispParams->rgvarg[1].vt & VT_BYREF)
            {
               HandleSheetChange( // Call the function.
                  *(pDispParams->rgvarg[1].ppdispVal),
                  *(pDispParams->rgvarg[0].ppdispVal));
            }
            else
            {
               HandleSheetChange(  // Call the function.
                  pDispParams->rgvarg[1].pdispVal,
                  pDispParams->rgvarg[0].pdispVal);
            }
         }
         break;
      }
      return S_OK;
   }
   
   /******************************************************************************
   *  HandleBeforeWorkbookClose -- This method processes the BeforeWorkbookClose
   *  event for the application attached to this event handler.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::HandleBeforeWorkbookClose( IDispatch* xlBook, 
                                                     VARIANT_BOOL* fCancel )
   {
      OutputDebugString("HandleBeforeWorkbookClose\n");
      HRESULT hr = S_OK;
      return hr;
   }
   
   /******************************************************************************
   *  HandleSheetChange -- This method processes the SheetChange event for the 
   *  application attached to this event handler.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::HandleSheetChange( IDispatch* xlSheet, 
                                                     IDispatch* xlRange)
   {
      OutputDebugString("HandleSheetChange\n");
      HRESULT hr = S_OK;
      return hr;
   }
   
   /******************************************************************************
   *  AttachToSource -- This method attaches to an event source.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::AttachToSource( IUnknown* pEventSource )
   {
      HRESULT hr = S_OK;
   
      IConnectionPointContainer* pCPC = NULL;
      hr = pEventSource->QueryInterface( IID_IConnectionPointContainer, 
         (void**)&pCPC );
      if (SUCCEEDED(hr)){
         
         hr = pCPC->FindConnectionPoint( IID_ApplicationEvents, 
            &m_pConnectionPoint );
         if (SUCCEEDED(hr)){
            
            hr = m_pConnectionPoint->Advise( this, &m_dwConnection );
         }
         pCPC->Release();
      }
   
      return hr;
   }
   
   /******************************************************************************
   *  DetachFromSource -- This method detaches from an event source.
   ******************************************************************************/ 
   STDMETHODIMP CAppEventListener::DetachFromSource()
   {
      HRESULT hr = S_OK;
      if (m_pConnectionPoint != NULL){
         m_pConnectionPoint->Unadvise( m_dwConnection );
         m_pConnectionPoint = NULL;
      }
      return hr;
   }
   					

6. Double-click the ID_STARTSINK control on your dialog box and add the following code to CMFCExcelEventsDlg::OnBnClickedStartsink:

   COleVariant
   covTrue((short)TRUE),
   covFalse((short)FALSE),
   covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   void CMFCExcelEventsDlg::OnBnClickedStartsink()
   {
      CWorkbooks xlBooks;
      CWorkbook xlBook;
   
      try{
         //Start Excel and create a new workbook.
         if (m_ExcelApplication.CreateDispatch( "Excel.Application" ) == 0)
         {
            AfxMessageBox( "Can't launch Excel!" );
            return;
         }
         xlBooks = m_ExcelApplication.get_Workbooks();
         xlBook = xlBooks.Add( covOptional );
   
         //Add an event handler for the Application object.
         m_pAppEventListener = new CAppEventListener();
         m_pAppEventListener->AddRef();
         m_pAppEventListener->AttachToSource( m_ExcelApplication.m_lpDispatch );
   
         //Make Excel visible and give the user control.
         m_ExcelApplication.put_Visible(true);
         m_ExcelApplication.put_UserControl(true);
   
      }
      catch(COleException *e)
      {
         e->ReportError();
         e->Delete();
         return;
      }
   }
   					

7. Double-click the ID_STOPSINK control on your dialog box and add the following code to CMFCExcelEventsDlg::OnBnClickedStopsink:

   void CMFCExcelEventsDlg::OnBnClickedStopsink()
   {
      try{
         if (m_pAppEventListener != NULL){
            m_pAppEventListener->DetachFromSource();
            m_pAppEventListener->Release();
            m_pAppEventListener = NULL;
         }
   
         m_ExcelApplication.Quit();
         m_ExcelApplication.ReleaseDispatch();
      }
   
      catch(COleException *e)
      {
         e->ReportError();
         e->Delete();
         return;
      }
   }
   					

8. Append the following code to CMFCExcelEventsDlg::OnInitDialog in Mfcexceleventsdlg.cpp:

       m_pAppEventListener = NULL;
   					

9. Add the following #include to Mfcexceleventsdlg.h:

   #include "AppEventListener.h"
   					

10. In the Mfcexceleventsdlg.h file, add the following to the list of protected variables in the //Implementation section of CMFCExcelEventsDlg:

       CApplication m_ExcelApplication;
       CAppEventListener* m_pAppEventListener;
    					

Test the Sample

1. Press CTRL+ALT+O to display the Output window.
2. Press F5 to build and run the program.
   
   Note If you receive a compile error, see the "Troubleshooting" section.
3. On the form, click Use Start Event Sink. The program starts Excel and creates a new workbook.
4. Add any data to cells on the worksheets. Examine the Output window in Microsoft Visual Studio to verify that the event handlers are called.
5. Click Close on the File menu to close the workbook. Click No to close without saving changes. Verify that the WorkbookBeforeClose event was received in the Output window in Visual Studio.
6. On the form, click Stop Event Sink. This disconnects the CAppEventListener event sink and closes Excel.
7. Close the form to end the debug session.

Troubleshooting

• If you add class wrappers for the Excel object library by using the File option in the Add Class From TypeLib wizard, you may receive an error message when you browse to the object library. To avoid this problem, type the full path and file name for the object library instead of browsing to the file.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
  311408  (http://support.microsoft.com/kb/311408/EN-US/ ) BUG: 'Read-Only' Warning When Adding an MFC Class From a Type Library
• If you receive the following error message when you build your sample application, change "Variant DialogBox" in CRange.h to "Variant _DialogBox":
  warning C4003: not enough actual parameters for macro 'DialogBoxA'
  For more information on the cause of this error, see the following Knowledge Base article:
  311407  (http://support.microsoft.com/kb/311407/EN-US/ ) BUG: MFC Class Wizard Does Not Resolve Naming Conflicts Between Windows APIs and COM Interface Methods

Excel Application Events

The following steps describe how to verify the universally unique identifier (UUID) for a set of Excel events.

1. On the Tools menu in Visual Studio .NET, click OLE/COM Object Viewer.
2. Expand the Type Libraries node.
3. Double-click Microsoft Excel your version Object Library in the type libraries list.
4. When the ITypeLib Viewer opens with the Excel object library displayed, expand the node for coClass Application. Under the coClass Application node, you see ApplicationEvents.
5. Double-click ApplicationEvents.
6. In the right pane of the ITypeLib Viewer, locate the following UUID:

   uuid(00024413-0000-0000-C000-000000000046)

   This UUID corresponds to the IID_ApplicationEvents variable that you declared in Appeventlistener.h.

For more information, see the following Microsoft Developer Network (MSDN) Web site: For more information about creating sink interfaces, and simplifying the connection process, see the following Knowledge Base article: For more information and a general example of connection points, see the Connpts.exe sample that is described in the following Knowledge Base article: