This article describes by example how you can create an MFC Automation client to retrieve a list of Visual Basic for Applications (VBA) macros (Sub and Function procedures) from a Microsoft Office document.

To obtain a listing of VBA procedures in a Microsoft Excel workbook, a Microsoft PowerPoint presentation, or a Microsoft Word document, you can use the classes and functions exposed by the Microsoft Visual Basic for Applications Extensibility library in conjunction with the object model of the application. When the document is opened with Automation, you can access the VBProject object of the document to iterate the collection of VBComponents; the VBComponents collection includes standard modules as well as class modules contained within the project. Once you have a reference to a VBComponent, you can retrieve its properties (including the code) and manipulate the component as you need to.

Note Special Consideration for Microsoft Access: Microsoft Access is an exception in that the Visual Basic for Applications Extensibility library is not required to access the code in the database project. The Microsoft Access object model exposes methods or properties that allow you to access code modules directly. One warning about retrieving or manipulating the code with a module in an Access database is that the module must be opened first.

The following steps demonstrate how you can create an MFC Automation client that retrieves a list of Sub and Function procedures from an Office document. The sample code illustrates using class wrappers for the Office application type libraries and the Visual Basic for Applications Extensibility library; the sample also provides a special case consideration for Microsoft Access databases.

Sample Project

1. In Visual C++, create a new MFC AppWizard EXE project named ListMacros. Select Dialog-based as the type, and then accept all other defaults.
2. On the View menu, click ClassWizard. In the ClassWizard dialog box, click the Automation tab, and then do the following:
   • Click Add Class and choose From a type library.
   • Browse to locate the Microsoft Visual Basic for Applications Extensibility type library (VBE6EXT.olb), and then click Open.
     
     Note The default folder for VBE6EXT.olb is C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6.
   • Select all classes in the type library, and then click OK to create the class wrapper.
3. Repeat the preceding steps to create class wrappers for the Microsoft Excel, Microsoft PowerPoint, Microsoft Access, and Microsoft Word type libraries. Choose the correct type library for the version of Office you are automating. See the "References" section later in this article for information about finding the correct type library.
4. To avoid class name conflicts, add a namespace for each of the class wrappers by doing the following:
   • Add the following code to the beginning of Excel9.h:

     namespace Excel{ 
     

     and then add a closing brace (}) at the end of the file.
   • Add the following code to the beginning of Excel9.cpp, just below the line that reads "#endif" (without the quotes), and before the first class definition:

     using namespace Excel;
     

5. Repeat these steps to create the remaining namespaces: MSWord, PPT, MSAccess, and VBAExt.
6. Click the ResourceView and open the IDD_LISTMACROS_DIALOG dialog box. Remove any controls that are drawn on the dialog box and replace them with the following controls:

      Control Type     Properties
      ------------     -------------------------
   
      Button           ID:           ID_Run
                       Caption:      Run
   
      Edit Box         ID:           IDC_FILENAME
   
      List Box         ID:           IDC_MACROLIST
                       Use Tabstops: Checked     
   					

7. On the View menu, click ClassWizard, select the Member Variables tab, select CListMacrosDlg in the Class Name drop-down list box, and then do the following:
   • On the list of controls, double-click IDC_FILENAME. In the Add Member Variable dialog box, supply the variable name m_sFilename, and then click OK.
   • On the list of controls, double-click IDC_MACROLIST. In the Add Member Variable dialog box, supply the variable name m_MacroList, change the Category to Control, and then click OK.
8. Click OK to close the ClassWizard dialog box.
9. Double-click the Run button on the dialog box. Click OK to add the member function OnRun. Add the following code to CListMacrosDlg::OnRun():

   void CListMacrosDlg::OnRun() 
   {
      USES_CONVERSION;
   
      UpdateData(TRUE);
   
   
      COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR); //for Optional args
   
      //Determine the PROGID for the filename supplied
      CLSID clsid;
      HRESULT hr = GetClassFile(T2OLE(m_sFilename), &clsid);
      if (FAILED(hr))
      {
         CString sMsg;
         sMsg.Format("Unable to determine progid for file: %s", m_sFilename);
         AfxMessageBox(sMsg);
         return;
      }
   
      LPOLESTR lpOleStr;
      ProgIDFromCLSID(clsid, &lpOleStr);
      CString sProgID = CString(lpOleStr);
      sProgID.MakeUpper();
      
      //If the version independent progid is EXCEL.SHEET, WORD.DOCUMENT, or
      //POWERPOINT.SHOW, then open the file in its associated application.
      //Once the file is open, call GetMacros to place the list of macro
      //names in the list box.
   
      if(sProgID.Find("EXCEL.SHEET",0)>=0)  //Microsoft Excel Workbook
      {
         Excel::_Application oApp;
         oApp.CreateDispatch("Excel.Application");
         Excel::Workbooks oBooks = oApp.GetWorkbooks();
         Excel::_Workbook oBook = oBooks.Open(m_sFilename, vOpt, vOpt, vOpt, 
                                              vOpt, vOpt, vOpt, vOpt, vOpt, 
                                              vOpt, vOpt, vOpt, vOpt);
         LPDISPATCH lpDisp = oBook.GetVBProject();
         GetMacros(lpDisp);
         oBook.Close(COleVariant((short)FALSE), vOpt, vOpt);
         oApp.Quit();
      }
   
      else if(sProgID.Find("WORD.DOCUMENT",0)>=0)    //Microsoft Word Document
      {
         MSWord::_Application oApp;
         oApp.CreateDispatch("Word.Application");
         MSWord::Documents oDocs = oApp.GetDocuments();
         MSWord::_Document oDoc = oDocs.Open(COleVariant(m_sFilename), vOpt,
                                             vOpt, vOpt, vOpt, vOpt, vOpt, 
                                             vOpt, vOpt, vOpt, vOpt, vOpt);
   	   // For Office Word 2003 and Office Word 2007, 16 parameters are required.
       /*  MSWord::_Document oDoc = oDocs.Open(COleVariant(m_sFilename), vOpt,
                                             vOpt, vOpt, vOpt, vOpt, vOpt, 
                                             vOpt, vOpt, vOpt, vOpt, vOpt,
   		                              								  vOpt,vOpt,vOpt,vOpt);
       */
         LPDISPATCH lpDisp = oDoc.GetVBProject();
         GetMacros(lpDisp);
         oDoc.Close(COleVariant((short)FALSE), vOpt, vOpt);
         oApp.Quit(vOpt, vOpt, vOpt);
      }
   
      else if(sProgID.Find("POWERPOINT.SHOW",0)>=0)  //Microsoft PowerPoint
   
                                                     //Presentation
      {
         PPT::_Application oApp;
         oApp.CreateDispatch("Powerpoint.Application");
         PPT::Presentations oAllPres = oApp.GetPresentations();
         PPT::_Presentation oPres = oAllPres.Open(m_sFilename, 0, 0, 0);
         LPDISPATCH lpDisp = oPres.GetVBProject();
         GetMacros(lpDisp);
         oPres.Close();
         oApp.Quit();
      }
   
      else if(sProgID.Find("ACCESS.APPLICATION", 0)>=0)  //Microsoft Access DB
   
      {   //** Special Consideration for Access Code Project
   
          MSAccess::_Application oApp;
          oApp.CreateDispatch("Access.Application");
          MSAccess::DoCmd oDoCmd = oApp.GetDoCmd();
          oApp.OpenCurrentDatabase(m_sFilename, FALSE);
          // For Office Access 2003 and Office Access 2007, 3 parameters are required.
          /*oApp.OpenCurrentDatabase(m_sFilename, FALSE,NULL);*/
   
          MSAccess::_CurrentProject oProj = oApp.GetCurrentProject();
          MSAccess::AllObjects oObjs = oProj.GetAllModules();
   
          CString sModName, sProcName, sItem;
          long lProcKind = 0;
          for(long i=0;i<=oObjs.GetCount()-1;i++)
          {
             MSAccess::AccessObject oObj = oObjs.GetItem(COleVariant(i));
             sModName = oObj.GetName();
             //Module must be open:
             oDoCmd.OpenModule(COleVariant(sModName), vOpt);
             MSAccess::Modules oMods = oApp.GetModules();
             MSAccess::Module oMod = oMods.GetItem(COleVariant(sModName));
             long lLineCount = oMod.GetCountOfLines();
             long j=1;
             while(j<lLineCount)
             {
                sProcName = oMod.GetProcOfLine(j, &lProcKind);
                if(!sProcName.IsEmpty()){
                   sItem.Format("%s\t\t%s", sModName, sProcName);
                   m_MacroList.AddString(sItem);
                   j = j + oMod.GetProcCountLines(sProcName, lProcKind); }
                else {
                   j++;    }
             }
          }
   
          oApp.Quit(0);
      }
   
      else  //Other...
      {
         CString sMsg;
         sMsg.Format("Unable to extract macro names for files with progid %s",
                     sProgID);
         AfxMessageBox(sMsg);
      }
   
   }
   
   					

10. Add the following includes to ListMacrosDlg.cpp:

    #include <afxpriv.h>
    #include "Excel9.h" // for Office Excel 2003 and later, use #include "Excel.h"
    #include "MSWord9.h" // for Office Word 2003 and later, use #include "MSWord.h"
    #include "MSPPT9.h"  // for Office Powerpoint 2003 and later, use #include "msppt.h"
    #include "MSAcc9.h" // for Office Access 2003 and later, use #include "msacc.h"
    #include "VBE6Ext.h"
    					

11. Click the ClassView tab. Right-click CListMacrosDlg and choose Add Member Function. Supply void for the Function Type, GetMacros(LPDISPATCH lpDisp) for the Function Declaration, select Private, and then click OK. Add the following code for CListMacrosDlg::GetMacros:

    void CListMacrosDlg::GetMacros(LPDISPATCH lpDisp)
    {
       long i, j; //counters
    
       //Clear the list box and set the tab stops
       j=m_MacroList.GetCount();
       for (i=j-1;i>=0;i--){
          m_MacroList.DeleteString(i);}
       m_MacroList.SetTabStops(50);
    
       //Iterate the collection of components in the VBA project
       //referenced by lpDisp.
       VBAExt::_VBProject oVBProj;
       oVBProj.AttachDispatch(lpDisp, TRUE);
       VBAExt::_VBComponents oVBComps = oVBProj.GetVBComponents();
       long lCompCount = oVBComps.GetCount();
       long lLineCount;
       long lProcKind = 0;
       VBAExt::_VBComponent oVBComp;
       VBAExt::_CodeModule oCode;
    
       for(i=1; i<=lCompCount; i++)
       {
          oVBComp = oVBComps.Item(COleVariant(i));
          oCode = oVBComp.GetCodeModule();
    
          //If the component contains any lines of code, then
          //retrieve the name of each procedure (Functions and Subs)
          //add it to the list box along with the module name.
          CString sProcName, sItem;
          lLineCount = oCode.GetCountOfLines();
          j=1;
          while(j<lLineCount)
          {
             sProcName = oCode.GetProcOfLine(j, &lProcKind);
             if(!sProcName.IsEmpty()){
                sItem.Format("%s\t\t%s", oVBComp.GetName(), sProcName);
                m_MacroList.AddString(sItem);
                j = j + oCode.GetProcCountLines(sProcName, lProcKind); }
             else {
                j++;    }
          }
       }
    }
    

12. Add the following line of code to the beginning of CListMacrosApp::InitInstance() in ListMacros.cpp:

    AfxOleInit();
    

13. Build the project and run it. Select a file (.xls, .doc, .mdb, or .ppt) that contains macros, and then click Run. The list of macros appears in the list box.

Additional Notes

• The code illustrated in this sample retrieves only Sub and Function procedures. If you want to retrieve Property Get, Property Let, or Property Set procedures, you must modify the code. The second argument of the ProcOfLine can be one of the following (the code sample uses 0, which is equivalent to vbext_pk_Proc):

  
  Constant        Value     Description
  -------------   -----     ------------------------------------------------
  vbext_pk_Get    3         Procedure that returns the value of a property 
  vbext_pk_Let    1         Procedure that assigns a value to a property
  
  vbext_pk_Set    2         Procedure that sets a reference to an object 
  vbext_pk_Proc   0         All procedures other than property procedures
  					

• If the VBA module is password-protected, the dialog box that asks for the password is displayed. If you do not know the password, the program cannot list the procedures.
• The Open (and OpenCurrentDatabase) methods in the sample above are used with the Office 2000 type library. The Office XP type library has additional parameters that need to be passed to these methods. Please see the "References" section for additional information.

(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Notes for Automating Microsoft Office XP

Some methods and properties have changed for Microsoft Office XP. For additional information about using the sample code described in this article with theMicrosoft Word 2002 type library, click the following article number to view the article in the Microsoft Knowledge Base: 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 above. For additional information about this setting and how you can correct the error, click the following article number to view the article in the Microsoft Knowledge Base: For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: