How To Use MFC to Retrieve a List of Macro Names in an Office Document (274680)


The information in this article applies to:

  • Microsoft Office Word 2003
  • Microsoft Word 2002
  • Microsoft Word 2000
  • Microsoft Office PowerPoint 2003
  • Microsoft PowerPoint 2002
  • Microsoft PowerPoint 2000
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000
  • Microsoft Visual C++, 32-bit Professional Edition 6.0
  • The Microsoft Foundation Classes (MFC)
This article was previously published under Q274680

SUMMARY

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

MORE INFORMATION

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, Total 16 parameters 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, 3 parameters 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, use #include "Excel.h"
#include "MSWord9.h" // for Office Word 2003, use #include "MSWord.h"
#include "MSPPT9.h"  // for Office Powerpoint 2003, use #include "msppt.h"
#include "MSAcc9.h" // for Office Access 2003, 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.

REFERENCES

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:

224925 INFO: Type Libraries for Office May Change with New Release

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:

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

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

266387 How To Find and List Access VBA Procedures by Using MFC

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

Modification Type:MajorLast Reviewed:3/23/2006
Keywords:kbAutomation kbhowto KB274680
©2004 Microsoft Corporation. All rights reserved.