Knowledge Base

How To Add and Run a VBA Macro Using Automation from MFC

Article ID: 194906

Article Last Modified on 3/21/2005

APPLIES TO

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

This article was previously published under Q194906

SUMMARY

Developers occasionally need to call an Automation client, such as Microsoft Excel, from another client, such as Microsoft Word, when automating Word from a Visual C++ client application.

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 add a VBA macro to Word from a Visual C++ client. The program also adds two CommandBar Control buttons to Word's CommandBars: one to run the macro, instantiating Excel, and one to close Excel. The program also removes the macro from Word, and closes Word.

MORE INFORMATION

With adaptation, the Visual C++ 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.

For a more complete discussion of modifying the Office CommandBars object, please see the following article in the Microsoft Knowledge Base:

180625 How To Use Automation to Modify the Office 97 Menu

Steps to Create the Project

Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project, but change the type library used in step 9 to MSWord8.olb for Word 97, MSWord9.olb for Word 2000, and MSWord.olb for Word 2002:

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 (default location is as follows):

C:\Program Files\Microsoft Office\mso97.dll.

The typelib for Microsoft Office 2000 is in the file Mso9.dll. The typelib for Microsoft Office XP is located (by default) at C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.dll.

NOTE: Select all of the components of that typelib. You will generate and add to your project the files Mso97.h and Mso97.cpp or the appropriate name for the version.
Next, repeat steps 8, 9, and 10 of the above article to add the typelib for the VBE Editor (Visual Basic for Applications Extensibility Library) to the project. That typelib is vbeext1.olb, and its default location is as follows:

C:\Program Files\Common Files\Microsoft Shared\VBA

NOTE: Select all of the components of that typelib. You will generate and add to your project the files vbeext1.h and vbeext1.cpp.

As a result, you will get many duplicates in the COleDispatchDriver wrapper classes. These result from the duplication of IDispatch names in Microsoft Word, Microsoft Office, and the VBE Editor.
To resolve those duplications, for this exercise, use the "namespace" facility provided by Visual C++. These steps will resolve the duplications.
1. At the very top of the Mswordx.h header file, insert the line:
```
            namespace word {  // that's an opening brace.
       
                            
```
2. 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 Mswordx.cpp file, on a new line just after the compiler directive "#endif" and before the line that reads:

      LPDISPATCH _Application:GetApplication

add the following line:

      using namespace word;

Search for the line reading:

      LPDISPATCH Shapes::GetApplication()

Just before that line insert the following line:

      using namespace word;

In the Vbeext1.h file, search for the line reading (make sure it's the Window class ONLY):
```
      class Window : public COleDispatchDriver
 
                    
```
Immediately before this line, insert the line reading:
```
      namespace ext {  // that's an opening brace.
 
                    
```
At the end of that class, just after the closing brace/semi-colon, add a closing brace/semi-colon for the namespace (this is about line 85 of the file).

In the Vbeext1.cpp file, search for the line reading:

      LPDISPATCH Window::GetVbe()

Just before that line, insert the following line:

      using namespace ext;

Add the following #include statements at the top of the AutoProjectDlg.cpp program file:

    #include "msword8.h" // or appropriate version's header
    #include "vbeext1.h"
    #include "mso97.h"  // or appropriate version's header

Add the following code to the CAutoProjectDlg::OnRun() event handler in the AutoProjectDlg.cpp file:

Sample Code

      // Sample Code
      // -----------
      // To use a commandbar of a Microsoft Office application, when
      // the commandbar is to be used to run an out-of-process
      // application (executable), that command bar can only call
      // a macro or IMSOCommandTarget in a module of its own (local)
      // application. It cannot start an out-of-process executable.
      // The solution is to use it to call a macro in the local
      // application that uses COM to start the out-of-process executable.
      // 
      // The code below does the following:
      // 1. Starts Microsoft Word.
      // 2. Injects a new macro named "TestMacro" into Word.
      // 3. Places a commandbar toolbar on the default toolbar group
      //    of MSWord.
      // 4. When clicked, the toolbar's left-hand button calls TestMacro,
      //    which
      // 5. Starts Microsoft Excel.
      // 6. When clicked, the toolbar's right-hand button closes Excel.
      // 
      // Programmable access to the facilities of the Visual Basic for
      // Applications Editor is through the IDispatch interfaces declared
      // in the type library for Microsoft Visual Basic for Applications
      // Extensibility, // Vbeext1.olb.
      // 
      // First, here is a VBA macro, defined as text. This belongs in this
      // application code at this point.


      #define FUNCTION_TEXT "Dim oXL as Object\n\r" \ 
       "Dim oBook as Object\n\r" \ 
       "Public Sub TestMacro()\n\r" \ 
       "Set oXL = CreateObject(\"excel.application\")\n\r" \ 
       "oXL.Visible = True\n\r"  \ 
       "Set oBook = oXL.Workbooks.Add()\n\r" \ 
       "End Sub\n\r" \ 
       "Public Sub CloseExcel()\n\r" \ 
       "oBook.Saved = True\n\r" \ 
       "Set oBook = Nothing\n\r" \ 
       "oXL.Quit\n\r" \ 

       "Set oXL = Nothing\n\r" \ 
       "End Sub\n\r"
       // Be sure there are no spaces following the
       // line continuation character (\) on each line above.

      // Declarations:
      word::_Application oWord;  // using namespace
                                 // for Word typelib's members
      HRESULT hr;
      DISPID dispID = 0;
      LPDISPATCH lpDisp = 0;
      VARIANT vResult;  // A Struct with a pdispVal member
      long lCount = 0;
      char buf[1024];   // General purpose message buffer
      OLECHAR *strCBs = L"CommandBars";


      word::Shapes oShapes; // using namespace
      word::Shape oShape;

      // Convenient values declared as ColeVariants.
      COleVariant covTrue((short)TRUE),
                  covFalse((short)FALSE),
                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


      // Get the Word IDispatch pointer and
      //  attach it to the objWord object.
      if (!oWord.CreateDispatch("Word.Application"))
      {
       AfxMessageBox("Couldn't get Word object.");
       return;
      }

      oWord.SetVisible(TRUE);  //This shows the application.

      word::Documents oDocs(oWord.GetDocuments());
      word::_Document oDoc;
      /*
      oDoc.AttachDispatch(oDocs.Add(
                           covOptional, // Template.
                           covFalse    // NewTemplate.
                           ));
                           /*  // End of Word 97 code


      /* Following is for Word 2002 */ 
      oDoc.AttachDispatch(oDocs.Add(
                           covOptional, // Template.
                           covFalse,    // NewTemplate. 
                       covOptional, // Document type
               covTrue      // Visible
                           ));

      VARIANT vIndex;
      vIndex.vt = VT_I4;
      vIndex.lVal = 2;

      // ********************* Load the macro ***********************
      // Create an empty VBA module for the document, then load
      // the text string defined as FUNCTION_TEXT into the module.
       ::MessageBox(NULL, "If you get the message \n\n"
       "\"Programmatic access to Visual Basic Project is not trusted\"\n\n"
       "adjust the security settings in Excel's Tools | Macro dialog.\n"
       "On the \"Trusted Sources\" tab check the\n"
       "\"Trust Access to Visual Basic Project\" property.",
       "VBA 2002 Advisory",327744);

      _VBProject oProject = oDoc.GetVBProject();  // Return Word's
                                                  //  VBProject object
      _VBComponents oComponents = oProject.GetVBComponents(); // This is
                                                              // defined in
      // the typelib for Microsoft Visual Basic for Applications
      //  Extensibility.
      _VBComponent oComponent = oComponents.Add(1);      // ditto
      _CodeModule oModule = oComponent.GetCodeModule();  // ditto

      // Create a temporary file with the VBA code in it.
      CString strTempFile = "c:\\MacroTempFile.txt";  // Name of the file
      CFile *myCFile = new CFile;  //Ptr to file object saving VBA macro
      myCFile->Open(strTempFile, CFile::modeCreate|CFile::modeWrite);
      myCFile->Write(FUNCTION_TEXT, strlen(FUNCTION_TEXT)); // That is the
                                                            //  macro
      myCFile->Close();

      // Load the macro from the file into the VBA module
                                   // of the Word document.
      oModule.AddFromFile(strTempFile); // Load the macro into
                                        // the _CodeModule.
      myCFile->Remove(strTempFile); // Remove the file holding the macro.
      delete myCFile;  // Free the heap space for the CFile object.

      AfxMessageBox("Click Word's Title Bar to select Word,\n"
                    "Press Alt+F11 to see Word's VBA editor\n"
                    "and observe - there's your TestMacro.\n\n"
                    "Click the VBE's File/Close and Return menu\n"
                    "to restore Word, and then\n"
                    "Click this OK to resume processing");

      // ************** Build a new commandbar for Word **************
      // Add the commandbar to Word's commandbars collection.
      // Find dispID of &strCBs, (i.e. = L"CommandBars",
      // declared & assigned above)
      hr = oWord.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 (Word's) existing menu configuration.
      // "vResult" is a VARIANT. It's declared above.
      oWord.InvokeHelper(dispID,   // "Command Bars" in this case
        DISPATCH_METHOD | DISPATCH_PROPERTYGET,
        VT_VARIANT,      // Type of return value
       (void*)&vResult,  // Address of var receiving IDispatch of CmdBrs
       NULL              // Pointer to parameters string
       );
      /*
      CommandBars cbs(vResult.pdispVal);  // Construct the CommandBars
                                          // object and attach the

                                          // IDispatch pointer to it.
                                          */ End of Office 97 code


     /* The following is for Office XP - Note the leading underscore */ 
      _CommandBars cbs(vResult.pdispVal); // Construct the CommandBars
                                              // object and attach the
                                              // IDispatch pointer to it.

      lCount = cbs.GetCount();   //  Word has 92!!??
                                 //  MSOffice reconfigures for each
                                 //  user-application.

      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
                            //  Word 8 Menu Bar Configuration.

      CommandBar oBar(vResult.pdispVal);  // Construct a new
                                          // CommandBar object
                                          // & attach the LPDispatch
                                          // of the active menu bar.

      VARIANT vName;
      vName.vt = VT_BSTR;
      vName.bstrVal = SysAllocString(L"MyNewCommandBar");
      // Variant for name of new bar

      VARIANT vPosition;
      vPosition.vt = VT_I2;
      vPosition.iVal = 1; // 4 = Floating;  0 = Left;
      // Variant for position of new bar

      AfxMessageBox("Now adding new bar to cbs collection");

      CommandBar oNewBar = cbs.Add(vName,  // const Variant Name =
                                           //  MyNewCommandBar
             vPosition, // const Variant Position = At top
             covFalse,  // const Variant (replace)MenuBar
             covTrue    // const Variant Temporary
             );

      oNewBar.SetVisible(TRUE);

      CommandBarControls oNewControls = oNewBar.GetControls();
                        // Object reference to collection

      VARIANT vType;
      vType.vt = VT_I4;
      vType.lVal = 1;
      // Control type is button

      CommandBarButton oNewButton2 =
             oNewControls.Add(vType,  // Type = msoControlButton
             covOptional,  // Id
             covOptional,  // Parameter
             covOptional,  // Before
             covTrue       // Temporary
             );
      oNewButton2.SetStyle(3);  // msoButtonIconAndCaption
      oNewButton2.SetCaption("EndXL");
      oNewButton2.SetTooltipText("Close Excel");
      oNewButton2.SetVisible(TRUE);
      oNewButton2.SetState(0);  // msoButtonUp
      oNewButton2.SetFaceId((long) 2186);
      oNewButton2.SetOnAction("CloseExcel");

      CommandBarButton oNewButton1 =
             oNewControls.Add(vType,   // Type = msoControlButton
             covOptional,  // Id
             covOptional,  // Parameter
             COleVariant((long)1),  // Before
             covTrue       // Temporary
             );
      oNewButton1.SetStyle(3);  // msoButtonIconAndCaption
      oNewButton1.SetCaption("Macro");
      oNewButton1.SetTooltipText("Run Macro");
      oNewButton1.SetVisible(TRUE);
      oNewButton1.SetState(0);  // msoButtonUp
      oNewButton1.SetFaceId((long) 186);  // commented for temporary test
      oNewButton1.SetOnAction("TestMacro");
      AfxMessageBox("Buttons in place. Click 'Macro' to start Excel");

      AfxMessageBox("Click the EndXL button to remove Excel,\n"
                    "Then Click OK here to remove macro.");
      oComponents.Remove(oComponent); // Remove the macro from the
                                      // document object.
      AfxMessageBox("Click Word's Title Bar to select Word,\n"
                    "Press Alt+F11 to see Word's VBA editor\n"
                    "and observe - there's no TestMacro.\n\n"
                    "Click the VBE's File/Close and Return menu\n"
                    "to restore Word, and then\n"
                    "Click this OK to resume processing");


      // Sanitation and cleanup
      oModule.ReleaseDispatch();

      oComponent.ReleaseDispatch();
      oComponents.ReleaseDispatch();
      oProject.ReleaseDispatch();
      oNewButton1.ReleaseDispatch();
      oNewButton2.ReleaseDispatch();
      oNewControls.ReleaseDispatch();
      oNewBar.ReleaseDispatch();
      oBar.ReleaseDispatch();
      cbs.ReleaseDispatch();

      oDoc.SetSaved(TRUE); // Avoid "Save As" dialog.
      oDoc.Close(covFalse, covOptional, covFalse);  // Close Word.
      oDoc.ReleaseDispatch();
      oWord.Quit(covFalse, covTrue, covFalse);
      oWord.ReleaseDispatch();

      //**End of Sample**

Additional Notes for Office XP

Office XP applications have a security option to allow programmatic access to the VBA object model. If this setting is "off" (the default), you may receive an error running the sample code. For more information about this setting and how you can correct the error, see the following article in the Microsoft Knowledge Base:

282830 PRB: Programmatic Access to Office XP VBA Project Is Denied

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

178749 How To Create an Automation Project Using MFC and a Type Library

180625 How To Use Automation to Modify the Office Menu

Keywords: kbhowto kbinterop kbautomation kbcode KB194906

Microsoft KB Archive/194906

Contents