How to use Automation to modify the Office menu

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.

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

1. 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
2. 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.
3. 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.)
   
   
4. 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;
   					

5. 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"
   					

6. 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;

For additional information about the Automation of Office applications, click the article number below to view the article in the Microsoft Knowledge Base: