Use Automation to Create Office Command Bars and Controls by Using Visual C++ .NET (312626)
The information in this article applies to:
- Microsoft Excel 2000
- Microsoft Visual C++ .NET (2002)
- Microsoft Access 2000
- Microsoft PowerPoint 2000
- Microsoft Word 2000
- Microsoft Access 2002
- Microsoft Excel 2002
- Microsoft Word 2002
- Microsoft PowerPoint 2002
- Microsoft Visual C++ .NET (2003)
- Microsoft Office Word 2003
- Microsoft Office Excel 2003
- Microsoft Office Access 2003
- Microsoft Office PowerPoint 2003
- Microsoft Office Professional Edition 2003
This article was previously published under Q312626 For a Microsoft C# .NET version of this article, see
303018. For a Microsoft Visual
Basic .NET version of this article, see
303017. 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. SUMMARY 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.
back to the top
Create a C++ .NET Automation Client- Follow the steps in the "Create an Automation Client"
section of the following Microsoft Knowledge Base article to create a basic
Automation client:
307473 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"
- 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;
};
- 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];
- 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;
}
};
- 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.
- 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);
}
- 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();
}
- 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.
- 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");
}
}
back to the top
Test the Automation Client- Press F5 to build and run the Automation client.
- Click the Run button on the AutoProject dialog box to start Excel and build a new command bar named
Billiards Sample.
- Try to fire the event handlers in the Automation client. To
do this, follow these steps:
- 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.
- Type your name in the box that is provided on the
Billiards Sample command bar.
- 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.
- 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.
- 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.
- Click the Unadvise button on the Automation Client dialog box to disconnect the event handlers.
- Quit Excel and quit the Automation client to end the
demonstration.
back to the top
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: 282830 PRB: Programmatic Access to Office XP VBA Project Is Denied
back to the top
REFERENCES For more information on Office Automation, see the
following Microsoft Developer Network (MSDN) Web site:
back to the top
Modification Type: | Major | Last Reviewed: | 1/19/2006 |
---|
Keywords: | kbHOWTOmaster KB312626 kbAudDeveloper |
---|
|