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

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:

Steps to Create the Project

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

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 (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.
3. 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.
   
   
4. 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).
5. 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;
    
   					

6. Search for the line reading:

         LPDISPATCH Shapes::GetApplication()
    
   					

   Just before that line insert the following line:

   
         using namespace word;
    
   					

7. 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).
8. In the Vbeext1.cpp file, search for the line reading:

         LPDISPATCH Window::GetVbe()
    
   					

   Just before that line, insert the following line:

         using namespace ext;
    
   					

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

10. 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: