Microsoft KB Archive/180625

= How to use Automation to modify the Office menu =

Article ID: 180625

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Visual C++ 5.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 5.0 Professional Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition
 * Microsoft Foundation Class Library 4.2
 * Microsoft Office XP Developer Edition
 * Microsoft Office 2000 Developer Edition
 * Microsoft Office 97 Standard Edition
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q180625



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 Excel type library:

178749 How To 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 to the project. The typelib for Microsoft Office 97 is in the file Mso97.dll. The typelib for Microsoft Office 2000 is Mso9.dll. The default location is C:\Program Files\Microsoft Office\mso.dll. The typelib for Microsoft Office 2002 is Mso.dll. The default location is C:\Program Files\Common Files\Microsoft Shared\Office10.

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 or 2002 and Microsoft Office 2000 or Microsoft Office XP. 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 Excel.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 Excel.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" // for Excel 2002, include excel.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")) {        AfxMessageBox("Failed to create Excel.Application"); 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);  // Pre Office XP      _CommandBars cbs(vResult.pdispVal);  // Construct the CommandBars // object and attach the // IDispatch pointer to it.

cCount = cbs.GetCount;  // 114 for Excel, Word has more!!?? // 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; // "Worksheet Menu Bar" sprintf(buf, "Name of the menu bar is %s", (LPCTSTR)cBarName); AfxMessageBox(buf, MB_SETFOREGROUND);

CString cBarNameLocal = oBar.GetNameLocal; // "Worksheet 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 a handle of the 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; </ol>

<div class="references_section">