HOWTO: Add and Run a VBA Macro Using Automation from MFC

ID: Q194906

The information in this article applies to:

Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0

SUMMARY

Developers occasionally need to call an Automation client, such as Microsoft Excel 97, from another client, such as Microsoft Word 97, 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 97 CommandBars object, please see the following article in the Microsoft Knowledge Base:

Q180625 HOWTO: 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:
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 97 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.
NOTE: Select all of the components of that typelib. You will generate and add to your project the files Mso97.h and Mso97.cpp.

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 97, Microsoft Office 97 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 Msword8.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 Msword8.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"
      #include "vbeext1.h"
      #include "mso97.h" 
```

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

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 97.
      // 2. Injects a new macro named "TestMacro" into Word.
      // 3. Places a commandbar toolbar on the default toolbar group
      //    of MSWord 97.
      // 4. When clicked, the toolbar's left-hand button calls TestMacro,
      //    which
      // 5. Starts Microsoft Excel 97.
      // 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.
                           ));

      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.
      _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.

      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**

REFERENCES

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

Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library

Q180625 HOWTO: Use Automation to Modify the Office 97 Menu

These referenced articles can be found at this Web site:

http://support.microsoft.com/support/visualc/atlmfc/oleatmfc.asp

Additional query words: vc++


Keywords          : kbinterop kbole kbAutomation kbExcel kbMFC kbVC500 kbVC600 kbWord kbCodeSam kbMFC600 
Version           : WINNT:5.0,6.0
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: July 8, 1999