I found some code on an ancient message board that nicely exports all of the VBA code from classes, modules and forms (see below):
Option Explicit
Option Compare Database
Function SaveToFile() 'Save the code for all modules to files in currentDatabaseDir\Code
Dim Name As String
Dim WasOpen As Boolean
Dim Last As Integer
Dim I As Integer
Dim TopDir As String, Path As String, FileName As String
Dim F As Long 'File for saving code
Dim LineCount As Long 'Line count of current module
I = InStrRev(CurrentDb.Name, "\")
TopDir = VBA.Left(CurrentDb.Name, I - 1)
Path = TopDir & "\" & "Code" 'Path where the files will be written
If (Dir(Path, vbDirectory) = "") Then
MkDir Path 'Ensure this exists
End If
'--- SAVE THE STANDARD MODULES CODE ---
Last = Application.CurrentProject.AllModules.Count - 1
For I = 0 To Last
Name = CurrentProject.AllModules(I).Name
WasOpen = True 'Assume already open
If Not CurrentProject.AllModules(I).IsLoaded Then
WasOpen = False 'Not currently open
DoCmd.OpenModule Name 'So open it
End If
LineCount = Access.Modules(Name).CountOfLines
FileName = Path & "\" & Name & ".vba"
If (Dir(FileName) <> "") Then
Kill FileName 'Delete previous version
End If
'Save current version
F = FreeFile
Open FileName For Output Access Write As #F
Print #F, Access.Modules(Name).Lines(1, LineCount)
Close #F
If Not WasOpen Then
DoCmd.Close acModule, Name 'It wasn't open, so close it again
End If
Next
'--- SAVE FORMS MODULES CODE ---
Last = Application.CurrentProject.AllForms.Count - 1
For I = 0 To Last
Name = CurrentProject.AllForms(I).Name
WasOpen = True
If Not CurrentProject.AllForms(I).IsLoaded Then
WasOpen = False
DoCmd.OpenForm Name, acDesign
End If
LineCount = Access.Forms(Name).Module.CountOfLines
FileName = Path & "\" & Name & ".vba"
If (Dir(FileName) <> "") Then
Kill FileName
End If
F = FreeFile
Open FileName For Output Access Write As #F
Print #F, Access.Forms(Name).Module.Lines(1, LineCount)
Close #F
If Not WasOpen Then
DoCmd.Close acForm, Name
End If
Next
MsgBox "Created source files in " & Path
End Function
However, this code does not solve my problem since I have 110 ms-access *.mdb's that I need to export the vba from into text files suitable for grepping.
The paths to the 110 files I'm interested in are already stored in a table, and my code already gained this information recursively (along with some other filtering)...so the recursive part is done.
Most of these files are opened by a single access user security file, an .mdw and I have tried several methods of opening them. ADO and ADOX worked great when I was searching for linked tables in these directories...but the code above involves being inside the database you are exporting the data from, and I want to be able to do this from a separate database that opens all of the mdbs and performs the export on each of them.
One of my attempts at this involved using the PrivDBEngine class to connect to the databases externally, but it doesn't allow me to access the Application object which is what the export code above requires.
Private Sub exportToFile(db_path As String, db_id As String, loginInfo As AuthInfoz, errFile As Variant)
Dim pdbeNew As PrivDBEngine
Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rst As DAO.Recordset
Dim cn As ADODB.Connection ' ADODB.Connection
Dim rs As ADODB.Recordset ' ADODB.Recordset
Dim strConnect As String
Dim blnReturn As Boolean
Dim Doc As Document
Dim mdl As Module
Dim lngCount As Long
Dim strForm As String
Dim strOneLine As String
Dim sPtr As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set exportFile = fso.CreateTextFile("E:\Tickets\CSN1006218\vbacode\" & db_id & ".txt", ForAppending)
' Export stuff...
On Error GoTo errorOut
Set pdbeNew = New PrivDBEngine
With pdbeNew
.SystemDB = loginInfo.workgroup
.DefaultUser = loginInfo.username
.DefaultPassword = loginInfo.password
End With
Set ws = pdbeNew.Workspaces(0)
Set db = ws.OpenDatabase(db_path)
For Each Doc In db.Containers("Modules").Documents
DoCmd.OpenModule Doc.Name
Set mdl = Modules(Doc.Name)
exportFile.WriteLine ("---------------------")
exportFile.WriteLine ("Module Name: " & Doc.Name)
exportFile.WriteLine ("Module Type: " & mdl.Type)
exportFile.WriteLine ("---------------------")
lngCount = lngCount + mdl.CountOfLines
'For i = 1 To lngCount
' strOneLine = mdl.Lines(i, 1)
' exportFile.WriteLine (strOneLine)
'Next i
Set mdl = Nothing
DoCmd.Close acModule, Doc.Name
Next Doc
Close_n_exit:
If Not (db Is Nothing) Then
Call wk.Close
Set wk = Nothing
Call db.Close
End If
Call exportFile.Close
Set exportFile = Nothing
Set fso = Nothing
Exit Sub
errorOut:
Debug.Print "----------------"
Debug.Print "BEGIN: Err"
If err.Number <> 0 Then
Msg = "Error # " & Str(err.Number) & " was generated by " _
& err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & err.Description
'MsgBox Msg, , "Error", err.HelpFile, err.HelpContext
Debug.Print Msg
End If
Resume Close_n_exit
End Sub
Is there anyway to access the application object from a PrivDBEngine? I have alot of modules that need grepping.
You can also try this code. It will preserve the items' filetypes (.bas, .cls, .frm) Remember to refer to / Check the Microsoft Visual Basic For Applications Extensibility Library in VBE > Tools > References
Public Sub ExportAllCode()
Dim c As VBComponent
Dim Sfx As String
For Each c In Application.VBE.VBProjects(1).VBComponents
Select Case c.Type
Case vbext_ct_ClassModule, vbext_ct_Document
Sfx = ".cls"
Case vbext_ct_MSForm
Sfx = ".frm"
Case vbext_ct_StdModule
Sfx = ".bas"
Case Else
Sfx = ""
End Select
If Sfx <> "" Then
c.Export _
Filename:=CurrentProject.Path & "\" & _
c.Name & Sfx
End If
Next c
End Sub