Run VBA Code in Excel to obtain VBA in Access Databases

Question

I would like to create a program in Excel that loops through a list of Access databases and writes the VBA that exists in the Access modules. I have found some code that I can run from Access which writes the VBA that exists in the Access modules. I am trying to figure out how to reference the database files from Excel and run the program on each database file. I will probably be able to figure out how to loop through the database files. I just need help with referencing the database file in the below code.

I can open the database with something like this:

Dim cstrDbFile As String = "C:\Database51.accdb"
Dim objShell As Object
Set objShell = CreateObject("WScript.Shell")
objShell.Run cstrDbFile

I also tried to set up a reference to Access like this:

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("C:\Database51.accdb")

I need to figure out how to refer to the Access database in:

Application.VBE.ActiveVBProject.VBComponents

I probably need to figure out how to create a reference to replace ActiveVBProject.

Below is some code I found which writes the contents of VBA modules. I don't remember where I found it.

For Each Component In Application.VBE.ActiveVBProject.VBComponents
    With Component.CodeModule

        'The Declarations
        For Index = 1 To .CountOfDeclarationLines
            Debug.Print .Lines(Index, 1)
        Next Index

        'The Procedures
        For Index = .CountOfDeclarationLines + 1 To .CountOfLines
            Debug.Print .Lines(Index, 1)
        Next Index

    End With

Next Component

VbComponent has an export method that will be much faster than looping through each line in each module. https://christopherjmcclellan.wordpress.com/2014/10/10/vba-and-git/ — RubberDuck, Dec 07 '14 at 14:02

score 2 · Answer 1 · answered Dec 06 '14 at 19:52

The following code will let you see Access database objects, but I don't know how to export the code (DoCmd not in Excel?). Your task would be VERY simple to do from Access, so I would reconsider...

Option Explicit

' Add a reference to the DAO Object Library

Sub Read_Access_VBA()
    Dim dbs         As DAO.Database
    Dim ctr         As DAO.Container
    Dim doc         As DAO.Document
    Dim iC          As Integer
    Dim iD          As Integer
    Dim i           As Integer
    Dim mdl         As Module

    Set dbs = DBEngine.OpenDatabase("c:\TEMP\106thRoster.mdb", False, False, _
                                        "MS Access;")
    Debug.Print "----------------------------------------"

    For iC = 0 To dbs.Containers.Count - 1
        Debug.Print "Container: " & dbs.Containers(iC).Name
        If dbs.Containers(iC).Documents.Count > 0 Then
            For iD = 0 To dbs.Containers(iC).Documents.Count - 1
                Debug.Print vbTab & "Doc: " & dbs.Containers(iC).Documents(iD).Name
            Next iD
        Else
            Debug.Print "    No Documents..."
        End If
    Next iC

    'Set ctr = dbs.Containers!Modules

    dbs.Close
    Set doc = Nothing
    Set ctr = Nothing
    Set dbs = Nothing
End Sub

score 0 · Answer 2 · edited May 23 '17 at 10:33

I was able to find some code that will assist me with my final goal: Exporting MS Access Forms and Class / Modules Recursively to text files?

Below are the most significant lines that will allow me to make progress with the project.

LineCount = oApp.Forms(Name).Module.CountOfLines
  FileName = Path & "\" & Name & ".vba"

F = FreeFile
  Open FileName For Output Access Write As #F
  Print #F, oApp.Forms(Name).Module.Lines(1, LineCount)
  Close #F

Run VBA Code in Excel to obtain VBA in Access Databases

2 Answers2