HOWTO: Use Automation to Modify the Office Menu
ID: Q180625
|
The information in this article applies to:
-
Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
-
The Microsoft Foundation Classes (MFC)
-
Microsoft Visual Studio 97
-
Microsoft Office 97 for Windows
-
Microsoft Excel 97 for Windows
-
Microsoft Office 2000 Developer
SUMMARY
This article discusses an approach to using the Microsoft Foundation Class
(MFC) Library installed with Microsoft Visual C++ versions 5.0 and 6.0 to
manage and modify the menu bar of member applications of Microsoft Office.
Applications that are members of Microsoft Office share a common menu
system. Office applications include Microsoft Word , Microsoft Excel, Microsoft Access, Microsoft PowerPoint, and Microsoft Outlook.
Each application needs a customized menu to fit its functionality and
features. Each member loads its own menu from the components that are
available to it from the Office "Command Bars" collection.
This article demonstrates many elements of the Office CommandBars object
model; it discusses the properties and methods of CommandBars, and
illustrates sample modifications. This specific demonstration uses
Microsoft Excel.
MORE INFORMATION
You can modify the menu for an Office application through Automation,
either permanently or on a temporary basis where the modifications of any
session are reset at the end of the session. Some can be reset
automatically by the system, and some must be reset by code in the
Automation program.
With adaptation, the VC++ code in the article can be used in your
application, but the purpose of the article is to help you learn, both by
walking through the code and by running the program.
Steps to Create the Project
- Follow steps 1 through 12 in the following Microsoft Knowledge Base
article to create a sample project that uses the IDispatch interfaces
and member functions defined the Excel8.olb type library:
Q178749
HOWTO: Create an Automation Project Using MFC and a Type Library
- Repeat steps 8, 9, and 10 of the above article to add the typelib for
Microsoft Office 2000 to the project. The typelib for Microsoft Office
is in the file Mso97.dll and the typelib for Microsoft Office 2000 is Mso9.dl. The default location is
C:\Program Files\Microsoft Office\mso.dll.
NOTE: Select all of the components of that typelib. You will generate
and add to your project the files Mso.h and Mso.cpp.
When you do that you will get many duplicates in the COleDispatchDriver
wrapper classes. These result from the duplication of IDispatch names
in Microsoft Excel 2000 and Microsoft 2000 Office. For example, both have an
IDispatch named _Application.
- To resolve those duplications, for this exercise, use the "namespace"
facility provided by VC++. At the very top of the Excel9.h header file,
insert the line:
namespace XL { // that's an opening brace.
At the very bottom of that same file, add a line that contains only
the closing brace and a semi-colon (specifically "};" without
the quotation marks.)
- At the beginning of the Excel9.cpp file, on a new line just after the
compiler directive "#endif", add the following line:
using namespace XL;
- Add the following lines to the #include statements at the top of the
AutoProjectDlg.cpp program file:
#include "excel9.h"
#include "mso.h"
- Add the following code to the CAutoProjectDlg::OnRun() event handler
in the AutoProjectDlg.cpp file:
Sample Code
// Common OLE-variants. Easy variants to use for calling arguments.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
HRESULT hr;
DISPID dispID = 0;
LPDISPATCH lpDisp = 0;
VARIANT vResult; // A Struct with a pdispVal member
long cCount = 0;
char buf[2048]; // General purpose message buffers
OLECHAR *strCBs = L"CommandBars";
// Object declarations.
XL::_Application app; // The XL prefix specifies the namerange.
XL::Workbooks oWorkbooks;
XL::_Workbook oBook;
XL::Worksheets oWorksheets;
XL::_Worksheet oSheet;
XL::Shapes oShapes;
XL::Shape oShape;
// More objects will be declared throughout the program.
// Create and show instance of Excel.
if(!app.CreateDispatch("Excel.Application.9"))
{
AfxMessageBox("Failed to create Excel.Application.9");
return;
}
// Excel visibility makes Office Menu Bar visible.
app.SetVisible(TRUE);
oWorkbooks = app.GetWorkbooks();
oBook = oWorkbooks.Add(covOptional);
oWorksheets = oBook.GetWorksheets();
oSheet = oWorksheets.GetItem(COleVariant((short)3));
oSheet.Activate(); // Make the sheet selection work
// Find &strCBs, i.e. L"CommandBars" and put it in dispID.
hr = app.m_lpDispatch->GetIDsOfNames(IID_NULL, &strCBs, 1,
LOCALE_SYSTEM_DEFAULT,
&dispID);
if(FAILED(hr))
{
sprintf(buf,"Failed to GetIDsOfNames() :(... Error = %08lx",
(long)hr);
AfxMessageBox(buf,MB_SETFOREGROUND);
}
// Get a dispatch pointer to CommandBars! Use that of running
// application's (Excel) existing menu configuration.
// "vResult" is a VARIANT. It's declared above.
app.InvokeHelper(dispID, DISPATCH_METHOD | DISPATCH_PROPERTYGET,
VT_VARIANT, (void*)&vResult, NULL);
sprintf(buf,
"CommandBars dispID = %ld \n"
"CommandBars IDispatch pointer is %08lx",
(long) dispID, (long)vResult.pdispVal);
AfxMessageBox(buf, MB_SETFOREGROUND);
CommandBars cbs(vResult.pdispVal); // Construct the CommandBars
// object and attach the
// IDispatch pointer to it.
cCount = cbs.GetCount(); // 85 - 86 for Excel, Word has 92!!??
// MSOffice reconfigures for each
// user-application.
sprintf(buf, "Count of CommandBars is %d", cCount);
AfxMessageBox(buf, MB_SETFOREGROUND);
vResult.pdispVal = cbs.GetActiveMenuBar(); // Returns a LPDISPATCH
// pointer of the CommandBar object that
// represents the active menu bar in the
// container application; that is, MS Office's
// Excel Menu Bar Configuration.
sprintf( buf, "dispatch pointer to the ActiveMenuBar is %08lx",
(long)vResult.pdispVal);
AfxMessageBox(buf, MB_SETFOREGROUND);
CommandBar oBar(vResult.pdispVal); // Construct a CommandBar object
// & attach the LPDispatch
// of the active menu bar.
CString cBarName = oBar.GetName(); // "Menu Bar"
sprintf(buf, "Name of the menu bar is %s", (LPCTSTR)cBarName);
AfxMessageBox(buf, MB_SETFOREGROUND);
CString cBarNameLocal = oBar.GetNameLocal(); // "Menu Bar"
sprintf(buf, "Local language's name of the menu bar is %s",
(LPCTSTR)cBarNameLocal);
AfxMessageBox(buf, MB_SETFOREGROUND);
long iMenuBarType = oBar.GetType(); // 1
sprintf(buf, "Type of Menu Bar is %d,", (long)iMenuBarType);
AfxMessageBox(buf, MB_SETFOREGROUND);
vResult.pdispVal = oBar.GetControls(); // CommandBarControls
// IDispatch pointer
// Construct a CommandBarControls object, and attach the IDispatch
// pointer for CommandBarControls to that oBarcontrols object.
CommandBarControls oBarcontrols(vResult.pdispVal);
// Construct a CommandBarControl for the 6th item in the
// ComandBarControls collection,
// and attach a IDispatch pointer to it.
CommandBarControl cbCtl=oBarcontrols.GetItem(COleVariant((short)6)
);
CString ccCaption = cbCtl.GetCaption(); // "&Tools"
long iiType = cbCtl.GetType(); // = 10
long iiIndex = cbCtl.GetIndex(); // 10
long iiId = cbCtl.GetId(); // 30007
CString ccTag = cbCtl.GetTag(); // blank
sprintf(buf,
"Caption of Control # 6 is %s\n"
"'Type' property of Control # 6 is %d\n"
"'Index' property of Control # 6 is %d\n"
"'Id' property of Control # 6 is %d\n",
(LPCTSTR)ccCaption, iiType, iiIndex, iiId);
if("" == ccTag)
strcat(buf, "Control #6 has noTag property");
else
sprintf(buf + strlen(buf), "Tag of Control #6 is %s",
(LPCTSTR)ccTag);
AfxMessageBox(buf, MB_SETFOREGROUND);
// Get a pointer for CommandBarPopup object with the ID of 30005.
vResult.pdispVal = oBar.FindControl(
COleVariant((short)10),
// msoControlPopup type
COleVariant((long)30005),
covOptional,
covOptional,
covOptional);
sprintf(buf, "IDispatch pointer of the msoControlPopup is %08lx",
(long)vResult.pdispVal);
AfxMessageBox(buf,MB_SETFOREGROUND);
// Construct a Popup Control object and
// Attach the IDispatch pointer of CommandBarPopup
// to that new control object.
CommandBarControl cbPop(vResult.pdispVal);
ccCaption = cbPop.GetCaption();
iiType = cbPop.GetType();
iiIndex = cbPop.GetIndex();
iiId = cbPop.GetId();
sprintf(buf,
"Caption of ControlPopup is %s\n"
"'Type' property of ControlPopup is %d\n"
"'Index' property of ControlPopup is %d\n"
"'Id' property of ControlPopup is %d\n",
(LPCTSTR)ccCaption, iiType, iiIndex, iiId);
AfxMessageBox(buf,MB_SETFOREGROUND);
sprintf(buf, "Watch the %s menu pad disappear.",
(LPCTSTR)ccCaption);
AfxMessageBox(buf, MB_SETFOREGROUND);
cbPop.Delete(covTrue);
oShapes = oSheet.GetShapes(); // Shapes collection,
// is empty at first.
AfxMessageBox("Adding a SmileyFace to be used in testing");
// It's a msoShapesSmileyFace AutoShape
oShape = oShapes.AddShape( 17, 10.0, 10.0, 40.0, 40.0);
CommandBar iBar;
// Use the count of commandbars in the CommandBars object.
for (int i = 1; i <= cCount; i++)
{
iBar = cbs.GetItem(COleVariant((short)i));
// To see the names of all the command bars,
// uncomment the next 2 lines:
// sprintf(buf, "Name of CommandBar is %s", iBar.GetName());
// AfxMessageBox(buf, MB_SETFOREGROUND);
if( "Shapes" == iBar.GetName())
{
iBar.SetEnabled(TRUE);
long lType = iBar.GetType();
sprintf(buf,
"For CommandBars(%d), the 'Shapes' shortcut, "
"Type property is %d",
i, lType);
AfxMessageBox(buf, MB_SETFOREGROUND);
break;
}
} // End of For loop.
// Construct and attach IDispatch pointer to
// CommandBarControls object.
CommandBarControls oCBCs = iBar.GetControls();
CommandBarControl oCBC = oCBCs.Add(
COleVariant((short)1),
// msoControlButton const
// VARIANT& Type,
covOptional, //const VARIANT& Id,
covOptional, //const VARIANT&
// Parameter, to pass with OnAction
covOptional, //const VARIANT&
// Before,
// Location on popup before item #
covTrue //const VARIANT& Temporary,
// (delete when app quits).
);
oCBC.SetCaption("Run Macro #1");
oCBC.SetVisible(TRUE);
oCBC.SetEnabled(TRUE);
// oCBC.SetOnAction("Macro1");
// You'd uncomment the line above and substitute
// the correct name of the desired Excel macro.
sprintf(buf,
"You've just added a CommandBarButtonControl "
"to the shortcut menu for SmileyFace.\n"
"Right-click on SmileyFace to see the new "
"line at the bottom of the context menu.\n\n"
" - it says 'Run Macro #1'.\n\n"
"Save the worksheet and close Excel when "
"you're through examining the change.\n\n"
"Then, reload Excel and open the worksheet "
"from the MRU list. \n"
"The new CommandBarButtonControl was "
"temporary, so it's gone.");
AfxMessageBox(buf, MB_SETFOREGROUND);
return;
Additional query words:
ole word8 Word 8.0 access8 Access excel8 Excel menus popup 9 Word9 access9
Keywords : kbcode kbinterop kbole kbAutomation kbExcel kbMFC kbVC500 kbVC600 kbVS97
Version : WINDOWS:97; winnt:5.0,6.0; :
Platform : WINDOWS winnt
Issue type : kbhowto
|