How to find and to list Access VBA procedures by using MFC (266387)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
The following steps demonstrate how to build a sample program that lists the VBA procedures in the Northwind.mdb Access database:

1. Start Microsoft Visual C++, and then start a new MFC AppWizard .exe project named NameProcedures. In step 1 of the wizard, select the Dialog based application type, and click Finish to accept all other defaults.
2. The new dialog box should be displayed in the edit area of the project. If it is not, click the ResourceView tab of the workspace, expand the Dialog folder, and then double-click IDD_NAMEPROCEDURES_DIALOG to open the dialog box
3. If the Controls tool box is not visible, right-click on a blank area of the Visual Studio menu bar, and choose Controls from the drop-down menu.
4. Click the List Box control on the Controls tool box and then drag the list box to the desired shape (approximately 1.5" x 2.5") and location on your dialog box. Right-click the List box control and choose Properties. Examine the ID to be sure it is IDC_LIST1.
5. Right-click the OK button on the dialog box, and choose Properties from the context menu. Change the button's ID to IDRUN and its caption to List VBA Procedures.
6. Double-click the List VBA Procedures button. When you are prompted to add a new member function for the BN_CLICKED message, click OK and an empty CNameProceduresDlg::OnRun() function is added to NameProceduresDlg.cpp.
7. On the View menu, click ClassWizard (or press the CTRL+W keyboard combination).
8. Select the Member Variables tab of the ClassWizard. Verify that the selected project is NameProcedure and that the selected class is CNameProceduresDlg. In the list of control IDs, select IDC_LIST1, and then click Add Variable. When you are prompted, specify m_iList for the variable name, Control for the category, and CListBox for the variable type. Click OK to add the new member variable.
9. Select the Automation tab of the ClassWizard. Click Add Class and then choose From a type library. Browse to locate the Microsoft Access object library (Msacc8.olb for Access 97, Msacc9.olb for Access 2000, or Msacc.olb for Access 2002 or Office Access 2003). Select all of the interfaces shown in the list and click OK.
   
   NOTE: The default folder for the Access .olb file is:

   C:\Program Files\Microsoft Office\Office\
   
   -or-
   
   C:\Program Files\Microsoft Office\Office10 for Access 2002.
   
   -or-
   
   C:\Program Files\Microsoft Office\Office11 for Office Access 2003.

10. Repeat the procedure for adding Automation classes for DAO.
    
    NOTE: Use the version of DAO that works with your version of Access (DAO 3.5 for Access 97 or DAO 3.6 for Access 2000). The default folder for DAO is:

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

11. Click OK to close the ClassWizard.
12. For each of the interfaces of the two object libraries, the ClassWizard creates COleDispatchDriver wrapper classes, which you can see displayed on the Class View page of the Visual Studio workspace. The Access and DAO type libraries each have an interface called Properties. To avoid a conflict between these two interfaces, use the namespace keyword for the Access wrapper classes:
    • Open the header file msacc9.h (msacc.h, or msacc8.h) and add the following code at the top of the file:

      namespace Access{
      						

      At the end of the file, add a closing curly brace ( } ).
      
      
    • Open the source file msacc9.cpp (msacc.cpp, or msacc8.cpp) and add the following code after the last #include:

      using namespace Access;
      						

13. Locate CNameProceduresApp::InitInstance() in NameProcedures.cpp and add the following code to the beginning of that function:

    if(!AfxOleInit())
        {
            AfxMessageBox("Unable to initialize COM");
            return FALSE;
        }
    					

14. In NameProceduresDlg.cpp, add the following code after the last include.
    
    For Access 2002 and Office Access 2003, add:

    #include "msacc.h"
    #include "dao360.h"
    					

    For Access 2000, add:

    #include "msacc9.h"
    #include "dao360.h"
    					

    For Access 97, add:

    #include "msacc8.h"
    #include "dao350.h"
    					

15. Locate the empty CNameProceduresDlg::OnRun() function in NameProceduresDlg.cpp. Replace that entire procedure with the following code:

    HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
       // Begin variable-argument list...
       va_list marker;
       va_start(marker, cArgs);
       
       if(!pDisp) {
          MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
          _exit(0);
       }
       
       // Variables used...
    
       DISPPARAMS dp = { NULL, NULL, 0, 0 };
       DISPID dispidNamed = DISPID_PROPERTYPUT;
       DISPID dispID;
       HRESULT hr;
       char buf[200];
       char szName[200];
       
       // Convert down to ANSI
       WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
       
       // Get DISPID for name passed...
       hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
       if(FAILED(hr)) {
          sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
          MessageBox(NULL, buf, "AutoWrap()", 0x10010);
          _exit(0);
          return hr;
       }
       
       // Allocate memory for arguments...
       VARIANT *pArgs = new VARIANT[cArgs+1];
       // Extract arguments...
       for(int i=0; i<cArgs; i++) {
          pArgs[i] = va_arg(marker, VARIANT);
       }
       
       // Build DISPPARAMS
       dp.cArgs = cArgs;
       dp.rgvarg = pArgs;
       
       // Handle special-case for property-puts!
       if(autoType & DISPATCH_PROPERTYPUT) {
          dp.cNamedArgs = 1;
          dp.rgdispidNamedArgs = &dispidNamed;
       }
       
       // Make the call!
       hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
       if(FAILED(hr)) {
          sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
          MessageBox(NULL, buf, "AutoWrap()", 0x10010);
          _exit(0);
          return hr;
       }
       // End variable-argument section...
       va_end(marker);
       
       delete [] pArgs;
       
       return hr;
    }
    
    void CNameProceduresDlg::OnRun() 
    {
    
       using namespace Access;
    
       //The path to the northwind database.  Be sure to modify this to point to
       //the northwind.mdb file (or nwind.mdb file) on your system
       char* pszDatabasePath = "D:\\MSOffice\\Office\\Samples\\northwind.mdb";
    
       //These variables use the Class-Wizard generated wrapper classes to automate 
       //Access and DAO.
       _Application app;
       Database db;
       Containers containers;
       Container container;
    
       Documents documents;
       Document document;
       long documentcount;
       long i;
       Modules modules;
       Module module;
       CString documentname;
    
       //General purpose buffer
       char buf[512];
    
       //Not all arguments are required for automation methods.  This COleVariant 
       //is useful for filling in optional parameters.
       COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
       
       // Start Access and get Application object...
       if(!app.CreateDispatch("Access.Application"))
       {
    
          AfxMessageBox("Couldn't start Access.");
          return;
       }
    
       //Open a database
    
       app.OpenCurrentDatabase(pszDatabasePath, true);
       // app.OpenCurrentDatabase(pszDatabasePath, true, ""); // Access 2002 and Office Access 2003
                                                              // requires
                                                              // password argument
    
    
       //Get a reference to the containers collection of the open database
       db = app.CurrentDb();         
       containers = db.GetContainers();
       
       //Code to get the LPDISPATCH for the "Modules" item assigned to an Access 
       //Container object variable 
       {
          VARIANT rVal = {0};
          VARIANT name = {0};
          name.vt = VT_BSTR;
          name.bstrVal = ::SysAllocString(L"Modules");
          
          //Occassionally, a method cannot be accessed through the wrapper classes 
          //generated by the Class-Wizard.  In these cases, we can use COM's 
          //IDispatch interface to access the method or property.
          AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &rVal, 
             containers.m_lpDispatch, L"item", 1, name);
          
          container.AttachDispatch(rVal.pdispVal);
          VariantClear(&name);
       }
       
       //Get the count of "documents" or pages of code, in the database
       documents = container.GetDocuments();
       documentcount = documents.GetCount();
    
       if( 0 != documentcount)
       {
          //Loop through the documents
          for( i = 0; i < documentcount; i++)
          {
             {
                // Code to assign the loop-counter to the "Item()" property of the 
                //Documents collection
                VARIANT rVal = {0};
                VARIANT name = {0};
                name.vt = VT_I4;
                name.lVal = i;
                
                AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &rVal, 
                   documents.m_lpDispatch, L"item", 1, name);
                
                document.AttachDispatch(rVal.pdispVal);
             }
             
             //Get the name of the document
             documentname = document.GetName();
             
             // Module must be open before we can see its code.
             DoCmd docmd = app.GetDoCmd(); //Access2000 has the DoCmd interface
             //IDoCmd docmd = app.GetDoCmd(); //Access97 has the IDoCmd interface
             docmd.OpenModule(COleVariant(documentname), covOptional); 
    
             //Get the number of lines in the module we want
             modules = app.GetModules();
             module = modules.GetItem(COleVariant(documentname));
             long linecount = module.GetCountOfLines();
    
             //STRATEGY: There is no list of procedures in a module, but we can 
             //find out which procedure a given line belongs to, and we can find
             //out how many lines a given procedure has, so we can work through a
             //file to find all the procedure names.
    
             // set counter for next line if not a sub or function
             for ( long linenumber = 1; linenumber <= linecount; linenumber++)   
             {
                long  vbext_pk_Proc = 0;  // 0 = Sub or Function
    
                //assign address of long containing type to the pointer
                long* pprockind = &vbext_pk_Proc;  
                CString procname = module.GetProcOfLine(linenumber, pprockind); 
    
                //If IsEmpty is false, then this is a real procedure
                if (false == procname.IsEmpty()) 
                {
                   //Write the name of the procedure into a buffer, along with the 
                   //module that contains it.
                   sprintf(buf,"%s.%s", documentname, procname);
    
                   //Add the procedure name to the list box
    
                   m_iList.AddString(buf);
    
                   //Move the line number to the end of the procedure
                   linenumber = --linenumber + module.GetProcCountLines(procname, 0);
                } // end if
             }  // end for
          } // end for ( e = 0; i < documentcount;...
       }  // end if(0!= documentcount...
       //Shut down the instance of Access we launched
       app.Quit(0);
    } // end OnRun function 

    NOTE: Modify the code to reflect the correct path for Northwind.mdb for your Office installation. The code is written for compatibility with Access 2000, Access 2002 and Office Access 2003; if you are using Access 97, see the remarks in the code for using IDoCmd instead of DoCmd.
16. Build the project and run it. Click List VBA Procedures and note that the sample code launches Access, quickly enumerates through its modules, and then closes. Also note that there is a list of VBA macros in the list box in your application.