Use Automation to Create Office Command Bars and Controls 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 demonstrates how to automate Excel from Visual C++ .NET to create a command bar that contains buttons, drop-down list boxes, combo boxes, and pop-up menus.

The Visual C++ .NET application catches and responds to the Click and Change events that the various command bar controls fire. Although this sample uses Excel as the host application, the command bar code will work in each of the Office applications.

Create a 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 4, select the Excel type library. The default location for Excel 2000 is C:\Program Files\Microsoft Office\Office\excel9.olb. The default location for Excel 2002 is C:\Program Files\Microsoft Office\Office10\excel.exe. The default location for Excel 2003 is C:\Program Files\Microsoft Office\Office11\excel.exe. Select the following Excel interfaces:
   
   
   • _Application
   • _Workbook
   • Workbooks
   After you add the Excel interfaces, repeat step 4 for the Office type library. The default location for Office 2000 is C:\Program Files\Microsoft Office\Office\mso9.dll. The default location for Office 2002 is C:\Program Files\Common Files\Microsoft Shared\Office10\mso.dll. The default location for Office 2003 is C:\ProgramFiles\Common Files\Microsoft Shared\Office11\mso.dll. Select the following Office interfaces:
   
   
   • _CommandBarButton
   • _CommandBarComboBox
   • _CommandBars
   • CommandBar
   • CommandBarControls
   • CommandBarPopup
   In step 6, add the following #include statements to the top of the Autoprojectdlg.h file under the #pragma once directive:

   #include "CApplication.h"
   #include "CWorkbook.h"
   #include "CWorkbooks.h"
   #include "CCommandBarButton.h"
   #include "CCommandBars.h"
   #include "CCommandBarPopup.h"
   #include "CCommandBar0.h"
   #include "CCommandBarControls.h"
   #include "CCommandBarComboBox.h"
   					

2. Add the following code under the #include files from the above step:

   class CClearButton;
   class CPopupButton;
   class CComboEvent;
   
   static const GUID DIID__CommandBarButtonEvents = {0x000C0351,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
   static const GUID DIID__CommandBarComboBoxEvents = {0x000C0354,0x0000,0x0000,{0xC0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};
   
   struct EventInfo {
   	DWORD dwCookie;
   	IConnectionPoint *pCP;
   };
   					

3. Add the following public members to the CAutoProjectDlg class:

   void SetupConnection(IDispatch *pDisp, EventInfo *pEventInfo, IUnknown *pUnk, REFIID EventIID);
   CCommandBarComboBox oEdit, oCombo, oDrop;
   CClearButton *m_pEightBallButton;
   CPopupButton *m_pBreakButton;
   CComboEvent *m_pComboEvent;
   EventInfo m_EventInfo[5];
   					

4. Paste the following code directly under the CAutoProjectDlg class:

   class CEventDispatch : public IDispatch
   {
   	public:
   	ULONG uCount;
   	CAutoProjectDlg *pExcelDlg;
   
   	CEventDispatch::CEventDispatch() {
   		uCount = 0;
   	}
   
   	CEventDispatch::~CEventDispatch() {
   	}
   
   	STDMETHODIMP QueryInterface(REFIID riid, void **ppv) {
   		if (NULL == ppv) return E_POINTER;
   		*ppv = NULL;
   		HRESULT hr = S_OK;
   		if ((DIID__CommandBarButtonEvents == riid) ||
                           (DIID__CommandBarComboBoxEvents == riid) ||
   			(IID_IUnknown == riid) || (IID_IDispatch == riid))
   			*ppv = static_cast<IDispatch*>(this);
   		else
   			hr = E_NOINTERFACE;
   		if (NULL != *ppv)
   			reinterpret_cast<IUnknown*>(*ppv)->AddRef();
   		return hr;
   	}
   
   	ULONG __stdcall AddRef(void) {
   		return uCount++;
   	}
   
   	ULONG __stdcall Release(void) {
   		return uCount--;
   	}
   
   	// IDispatch methods.
   	STDMETHODIMP GetTypeInfoCount(UINT *pctinfo) {
   		if(pctinfo) *pctinfo = 0;
   		return E_NOTIMPL;
   	}
   
   	STDMETHODIMP GetTypeInfo(UINT iTInfo, LCID lcid, ITypeInfo **ppTInfo) {
   		return E_NOTIMPL;
   	}
   
   	STDMETHODIMP GetIDsOfNames(REFIID riid, LPOLESTR *rgszNames, UINT cNames, LCID lcid, DISPID *rgDispId) {
   		return E_NOTIMPL;
   	}
   
   	virtual STDMETHODIMP Invoke(DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
   		DISPPARAMS *pDispParams, VARIANT *pVarResult,EXCEPINFO *pExcepInfo, UINT *puArgErr) = 0;
   
   };
   
   class CClearButton : public CEventDispatch {
      	STDMETHODIMP Invoke(DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
      		DISPPARAMS *pDispParams, VARIANT *pVarResult, EXCEPINFO *pExcepInfo, UINT *puArgErr) 
      	{
      		if (dispIdMember == 1) {
      			TRACE("New game button clicked\r\n");
      			// Reset all values.
      			pExcelDlg->oEdit.put_Text("");
      			pExcelDlg->oDrop.put_ListIndex(1);
      			pExcelDlg->oCombo.put_Text("");
      		}
      		return S_OK;
      	}
   };
   
   class CPopupButton : public CEventDispatch {
      	STDMETHODIMP Invoke(DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
      		DISPPARAMS *pDispParams, VARIANT *pVarResult, EXCEPINFO *pExcepInfo, UINT *puArgErr) 
      {
        if (dispIdMember == 1) {
      	    TRACE("Popup Button Clicked\r\n");
      	    // Get a random number.
      	   int iRand = rand();
                  // Show a message box to the user.
      	    CString sMsg = "Game: " + pExcelDlg->oDrop.get_Text() 
                  + "\r\n\r\nName: " + pExcelDlg->oEdit.get_Text() +
                  "\r\nOpponent: " + pExcelDlg->oCombo.get_Text() + 
      	    "\r\n\r\nWinner: " + ((iRand > (RAND_MAX/2)) ?
                            pExcelDlg->oEdit.get_Text() 
                            : pExcelDlg->oCombo.get_Text());
      	    MessageBox(NULL,sMsg,NULL,MB_SETFOREGROUND);
        }
        return S_OK;
      }
   };
   
   class CComboEvent : public CEventDispatch {
      	STDMETHODIMP Invoke(DISPID dispIdMember, REFIID riid, LCID lcid, WORD wFlags,
      		DISPPARAMS *pDispParams, VARIANT *pVarResult,EXCEPINFO *pExcepInfo, UINT *puArgErr) 
      {  
        if ((dispIdMember == 1) && (pDispParams->cArgs == 1)) {
      		CCommandBarComboBox oComboBox;
      		oComboBox = pDispParams->rgvarg[0].pdispVal;
      		pDispParams->rgvarg[0].pdispVal->AddRef();
      		TRACE1("Change event fired -- New selection = %s\r\n",oComboBox.get_Text());
   	 }
        return S_OK;
      	}
   };
   					

5. In the IDD_AUTOPROJECT_DIALOG dialog box, right-click Run, and then select Add event handler from the drop-down list box. In the Event Handler Wizard, select the BN_CLICKED message type, and then click Add and Edit.
6. In Autoprojectdlg.cpp, replace the following code

   void CAutoProjectDlg::OnBnClickedRun()
   {
   	// TODO: Add your control notification handler code here.
   }
   					

   with:

   void CAutoProjectDlg::OnBnClickedRun()
   {
   	// Declare variables.
   	CApplication oExcel;
   	CWorkbooks oBooks;
   	CWorkbook oBook;
   	CCommandBars oCommandBars;
   	CCommandBar0 oCommandBar;
   	CCommandBarControls oControls;
   
   	CCommandBarButton oButton, oPopupButton;
   	CCommandBarPopup oPopup;
   
   	int iConn = 0;
   	COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);
   
   	ZeroMemory(m_EventInfo,sizeof(EventInfo)*5);
   
   	// Start Excel.
   	if (!oExcel.CreateDispatch("Excel.Application")) {
   		AfxMessageBox("Couldn't create Excel",MB_SETFOREGROUND,NULL);
   		return;
   	}
   
   	m_pEightBallButton = new CClearButton();
   	m_pEightBallButton->pExcelDlg = this;
   	m_pBreakButton = new CPopupButton();
   	m_pBreakButton->pExcelDlg = this;
   	m_pComboEvent = new CComboEvent();
   	m_pComboEvent->pExcelDlg = this;
   
   	// Show Excel and set UserControl.
   	oExcel.put_Visible(TRUE);
   	oExcel.put_UserControl(TRUE);
   	// Add a new workbook
   	oBooks = oExcel.get_Workbooks();
   	oBook = oBooks.Add(covOptional);
   
        // Get the command bars collection.
        oCommandBars = oExcel.get_CommandBars();
      try {
   	// Check to see if the command bar exists.
   	oCommandBar = oCommandBars.get_Item(COleVariant("Billiards Sample"));
          } 
      catch(...) 
          {
   	TRACE("Billiards command bar doesn't exist.  Creating it now.\r\n");
   	oCommandBar = oCommandBars.Add(COleVariant("Billiards Sample"),covOptional,covOptional,COleVariant((short)true));
          }
   
   
   	// Show the command bar to the user.
   	oCommandBar.put_Visible(TRUE);
   
   
   	// Add a button to the command bar.
   	oControls = oCommandBar.get_Controls();
   	// 1 = Office.MsoControlType.msoControlButton
   	oButton = oControls.Add(COleVariant((short)1),covOptional,covOptional,covOptional,covOptional);
   	// Set the caption and face ID.
   	oButton.put_Caption("New game");
   	oButton.put_FaceId(1845);
   
   
   	SetupConnection(oButton.m_lpDispatch,&m_EventInfo[iConn],(IUnknown *)m_pEightBallButton,DIID__CommandBarButtonEvents);
   	iConn++;
   
   
   	// Add an edit box to the command bar.
   	// 2 = Office.MsoControlType.msoControlEdit
   	oEdit = oControls.Add(COleVariant((short)2),covOptional,covOptional,covOptional,covOptional);
   	// Show a vertical separator.
   	oEdit.put_BeginGroup(TRUE);
   	// Clear the text and show a caption.
   	oEdit.put_Text("");
   	oEdit.put_Caption("Enter your name:");
   	oEdit.put_Style(1); // 1 = Office.MsoComboStyle.msoComboLabel;
   
   	SetupConnection(oEdit.m_lpDispatch,&m_EventInfo[iConn],(IUnknown *)m_pComboEvent,DIID__CommandBarComboBoxEvents);
   	iConn++;
   
   
   
   	// Add a combo box to the command bar.
   	// 4 = Office.MsoControlType.msoControlComboBox
   	oCombo = oControls.Add(COleVariant((short)4),covOptional,covOptional,covOptional,covOptional);
   	// Add items to the combo box.
   	oCombo.AddItem("Sharky",covOptional);
   	oCombo.AddItem("Cash",covOptional);
   	oCombo.AddItem("Lucky",covOptional);
   	// Set the caption and style.
   	oCombo.put_Caption("Choose your opponent:");
   	oCombo.put_Style(1); // 1 = Office.MsoComboStyle.msoComboLabel;
   
   	SetupConnection(oCombo.m_lpDispatch,&m_EventInfo[iConn],(IUnknown *)m_pComboEvent,DIID__CommandBarComboBoxEvents);
   	iConn++;
   
   	// Add a drop-down list box to the command bar.
   	// 3 = Office.MsoControlType.msoControlDropdown
   	oDrop = oControls.Add(COleVariant((short)3),covOptional,covOptional,covOptional,covOptional);
   	// Add items to the list box.
   	oDrop.AddItem("8 Ball",covOptional);
   	oDrop.AddItem("9 Ball",covOptional);
   	oDrop.AddItem("Straight Pool",covOptional);
   	oDrop.AddItem("Bowlliards",covOptional);
   	oDrop.AddItem("Snooker",covOptional);
   	// Set the value to the first in the list.
   	oDrop.put_ListIndex(1);
   	// Set the caption and style.
   	oDrop.put_Caption("Choose your game:");
   	oDrop.put_Style(1); // 1 = Office.MsoComboStyle.msoComboLabel;
   
   	SetupConnection(oDrop.m_lpDispatch,&m_EventInfo[iConn],(IUnknown *)m_pComboEvent,DIID__CommandBarComboBoxEvents);
   	iConn++;
   
   	// Add a pop-up menu to the command bar.
   	// 10 = Office.MsoControlType.msoControlPopup
   	oPopup = oControls.Add(COleVariant((short)10),covOptional,covOptional,covOptional,covOptional);
   	// Add a separator before the pop-up button.
   	oPopup.put_BeginGroup(TRUE);
   	// Set the caption.
   	oPopup.put_Caption("Rack 'em Up!");
   
   	// Add a button to the pop-up.
   	// 1 = Office.MsoControlType.msoControlButton
   	oControls = oPopup.get_Controls();
   	oPopupButton = oControls.Add(COleVariant((short)1),covOptional,covOptional,covOptional,covOptional);
   	// Change the face ID and caption for the button.
   	oPopupButton.put_FaceId(643);
   	oPopupButton.put_Caption("Break!");
   
   	SetupConnection(oPopupButton.m_lpDispatch,&m_EventInfo[iConn],(IUnknown *)m_pBreakButton,DIID__CommandBarButtonEvents);
   } 
   					

7. Paste the following code under the CAutoProjectDlg::OnBnClickedRun method:

   void CAutoProjectDlg::SetupConnection(IDispatch *pDisp, EventInfo *pEventInfo, IUnknown *pUnk, REFIID EventIID)
   {
   	IConnectionPointContainer *pCPC = 0;
   
   
   	// Set up the connection and call Advise.
   	if (FAILED(pDisp->QueryInterface(IID_IConnectionPointContainer,(void **)&pCPC))) return;
   	if (FAILED(pCPC->FindConnectionPoint(EventIID,&(pEventInfo->pCP)))) return;
   	if (FAILED(pEventInfo->pCP->Advise(pUnk,&(pEventInfo->dwCookie)))) return;
   	pCPC->Release();
   }
   					

8. Add a new button to the IDD_AUTOPROJECT_DIALOG dialog box. Change the ID of the button to IDUNADVISE and the caption to Unadvise. Right-click Unadvise and then select Add event handler from the drop-down list box. In the Event Handler Wizard, select the BN_CLICKED message type, and then click Add and Edit.
9. In AutoProjectDlg.cpp, replace the following code

   void CAutoProjectDlg::OnBnClickedUnadvise() 
   {
   	//TODO: Add your contol notification handler code here.
   }
   					

   with:

   void CAutoProjectDlg::OnBnClickedUnadvise() 
   {
   	HRESULT hr;
   	// Unadvise all your connections and release them.
   	try {
   	for (int i = 0; i < 5; i++)
   	{
   		if (m_EventInfo[i].pCP) {
   			hr = m_EventInfo[i].pCP->Unadvise(m_EventInfo[i].dwCookie);
   			if (hr == 0x800706ba) {
   			  // The server has shut down -- clear EventInfo.
   			  ZeroMemory(m_EventInfo,sizeof(EventInfo)*5);
   			  break;
   			}
   			m_EventInfo[i].pCP->Release();
   		}
   	}
   	// Free memory.
   	delete m_pEightBallButton;
   	delete m_pBreakButton;
   	delete m_pComboEvent;
   	m_pEightBallButton = NULL;
   	m_pBreakButton = NULL;
   	m_pComboEvent = NULL;
   	} catch(...) {
   		TRACE("An error occured");
   	}
   } 
   					

Test the Automation Client

1. Press F5 to build and run the Automation client.
2. Click the Run button on the AutoProject dialog box to start Excel and build a new command bar named Billiards Sample.
3. Try to fire the event handlers in the Automation client. To do this, follow these steps:
   1. Click the New Game button on the Billiards Sample command bar. The CClearButton class that you implented in CAutoProjectDlg.h handles the Click event for the control.
   2. Type your name in the box that is provided on the Billiards Sample command bar.
   3. Choose your component and your game in the combo box controls that are provided on the Billiards Sample command bar. The CComboEvent class that you implemented in CAutoProjectDlg.h handles the Change events for the combo box controls.
   4. Click the Rack 'em Up pop-up button on the Billiards Sample command bar and select Break. The CPopupButton class that you implemented in CAutoProjectDlg.h handles the Click event for the pop-up button.
   5. Events are traced in the Visual Studio .NET Output window when they are fired. To examine the traces, activate the Visual Studio .NET window, click Other Windows on the View menu, and then click Output Window.
4. Click the Unadvise button on the Automation Client dialog box to disconnect the event handlers.
5. Quit Excel and quit the Automation client to end the demonstration.

Troubleshooting

Office XP applications have a security option to allow programmatic access to the Visual Basic for Applications (VBA) object model. If this setting is set to off (the default), you may receive an error message when you run the sample code. For additional information on this setting and how to correct the error, click the article number below to view the article in the Microsoft Knowledge Base:

For more information on Office Automation, see the following Microsoft Developer Network (MSDN) Web site: