Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How to use Automation to modify the Office menu


View products that this article applies to.

This article was previously published under Q180625

↑ Back to the top


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.

↑ Back to the top


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

  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;

↑ Back to the top


References

For additional information about the Automation of Office applications, click the article number below to view the article in the Microsoft Knowledge Base:
222101� How To Find and Use Office Object Model Documentation

↑ Back to the top


Keywords: kbautomation, kbhowto, kbinterop, KB180625

↑ Back to the top

Article Info
Article ID : 180625
Revision : 13
Created on : 11/21/2006
Published on : 11/21/2006
Exists online : False
Views : 529