Description
I worked out a method to save all VBA code found in Standard Modules and Form Modules to text files. This code snippet has been tested on XP with Access 2000, where it runs in about 2 seconds on my PC. It was not tested on Class Modules. Its intended use is to backup and document VBA code used in an MS Access database. Feel free to use and modify the code to suit your needs. You should read through it first to see how it works. The code has good self documentation, and variables were named with reasonable clarity.
Detail and Use
The code is written as a Function named SaveToFileDBModulesCode, which is to be saved in a Standard Module. It can be called by viewing the code in the module, then using the Run Button from the Toolbar. Note that when the code is run it will open any closed modules very rapidly, which is normal, but you will see a flutter of windows opening. They are closed very rapidly after the code is read from each, then written to files.
Function Action
- The function will find all module names (including forms.modules).
- Any closed modules are opened so the VBA code can be read.
- A directory is created (in the current db dir) to save the files into, it is named: vba_modules_code
- Files are created for each module, based on the name of the module with an extension of .txt added to create the file name.
- The content of each module is written to its associated file.
- All modules and forms.modules that were closed before running the function are then closed again. Any modules that were open prior to using the function will remain open, so the active work state is saved.
And now the code:
Code:
Function SaveToFileDBModulesCode()
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Function action
'Save the code for all db modules to files.
'Save dir is: currentDatabaseDir\db_modules_code
'
'NOTES
'
'This code can be called directly using the Run button from the VBE.
'A bit of warning, all closed modules will be opened rapidly.
'They will flash open somewhat quickly on the screen.
'Then all modules that were closed, then opened, will be closed again.
'Only modules that were open before running this code will remain open.
'Any other windows will remain open and are unaffected.
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'VARS
'a current module name and count of all modules
Dim moduleName As String, modulesCount As Integer
'a current form name and count of all forms
Dim formName As String, formsCount As Integer
'last array index offset and index pointer
Dim lastIndex As Integer, i As Integer
'file path and name
Dim topDir As String, oPath As String, oFile As String, fName As String
'offset pointer
Dim ofs As Integer
'file ops
Dim fp1 As Long
'the count of all lines in a currently evaluated module
Dim lineCount As Long
'used when testing for an open module
Dim testValue As String
'set of modules to be closed
Dim closeModulesList As Collection: Set closeModulesList = New Collection
'set of form modules to be closed
Dim closeFormsModulesList As Collection: Set closeFormsModulesList = New Collection
'CODE
'# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
'set the path where the files will be written
ofs = InStrRev(CurrentDb.Name, "\")
topDir = Left(CurrentDb.Name, ofs - 1)
oPath = topDir & "\" & "db_modules_code"
'confirm the dst dir exists and if not then create it
If (Dir(oPath, vbDirectory) = "") Then
MkDir oPath
End If
'--- SAVE THE STANDARD MODULES CODE (SECTION START) ---
'get the count of modules
modulesCount = Application.CurrentProject.AllModules.Count
'-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --C5-mods
'proceed with a valid count
If (modulesCount > 0) Then
'set the last index offset of the built-in modules array
lastIndex = modulesCount - 1
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'walk the module names
For i = 0 To lastIndex
'-- start of for loop cycle --
'clear at the top of each loop
testValue = ""
'pop a module name from the built-in modules collection
moduleName = Application.CurrentProject.AllModules(i).Name
'test if the module is open
On Error Resume Next
testValue = Access.Modules(moduleName).CountOfLines
'actions for modules that were closed:
' store module name for closing it later
' and open the module so it can be accessed programmicably
If (testValue = "") Then
closeModulesList.Add moduleName
DoCmd.OpenModule moduleName
End If
'get the count of lines in the select module
lineCount = Access.Modules(moduleName).CountOfLines
'create a filename from the module name
fName = moduleName & ".txt"
'prepend the output dir to the filename
oFile = oPath & "\" & fName
'test for an existing file and delete any existing version
' only the most recent module code will be written to the file
' the DBA can archive the files externally if backups are needed
' or write supplemental code to create backup file versions
' this code simply overwrites the files
If (Dir(oFile) <> "") Then
Kill oFile
End If
'open a new file for writing the module code
fp1 = FreeFile
Open oFile For Output Access Write As #fp1
'print the module content to the file (which is saved on close)
Print #fp1, Access.Modules(moduleName).Lines(1, lineCount)
Close #fp1
'-- end of for loop cycle --
Next
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'now close all modules that were not open before
If (closeModulesList.Count > 0) Then
For i = 1 To closeModulesList.Count
moduleName = closeModulesList(i)
DoCmd.Close acModule, moduleName
Next
End If
End If
'--- SAVE THE STANDARD MODULES CODE (SECTION END) ---
'-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --C5-mods
'--- SAVE FORMS MODULES CODE (SECTION START) ---
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . --C5-forms
'get the code for all existing forms
'get a count of all forms
formsCount = Application.CurrentProject.AllForms.Count
'proceed with valid count
If (formsCount > 0) Then
'set the last offset index of the built-in forms array
lastIndex = formsCount - 1
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'walk the forms names
For i = 0 To lastIndex
'pop the form name
formName = Application.CurrentProject.AllForms(i).Name
'test if the form is open
On Error Resume Next
testValue = Access.Forms(formName).Module.CountOfLines
'actions for forms.modules that were closed:
' store form.module name for closing it later
' and open the form.module so it can be accessed programmicably
If (testValue = "") Then
closeFormsModulesList.Add formName
DoCmd.OpenModule formName
End If
'get the count of lines in the select module
lineCount = Access.Forms(formName).Module.CountOfLines
'create a filename from the form.module name
fName = formName & ".txt"
'prepend the output dir to the filename
oFile = oPath & "\" & fName
'test for an existing file and delete any existing version
' only the most recent form.module code will be written to the file
' the DBA can archive the files externally if backups are needed
' or write supplemental code to create backup file versions
' this code simply overwrites the files
If (Dir(oFile) <> "") Then
Kill oFile
End If
'open a new file for writing the module code
fp1 = FreeFile
Open oFile For Output Access Write As #fp1
'print the module content to the file (which is saved on close)
Print #fp1, Access.Forms(formName).Module.Lines(1, lineCount)
Close #fp1
Next
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'now close all form.modules that were not open before
If (closeFormsModulesList.Count > 0) Then
For i = 1 To closeFormsModulesList.Count
formName = closeFormsModulesList(i)
DoCmd.Close acModule, formName
Next
End If
End If
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . --C5-forms
'memory cleanup
Set closeModulesList = Nothing
Set closeFormsModulesList = Nothing
'msg to the immediate window for anyhoo interested
Debug.Print "Done. Printed contents of all modules and form.modules to files."
Debug.Print "See files in Dir: " & oPath
End Function