Results 1 to 3 of 3
  1. #1
    ioMatt is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2010
    Posts
    1

    Saving Module Code to Text Files

    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

  2. #2
    Colin Hume is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2011
    Posts
    1

    Saving Module Code to Text Files

    I've simplified things and reduced the size of the code.
    Code:
    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

  3. #3
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    why write more than one little loop to do this? This is all it takes guys:

    Code:
    Function OutputModules()
    Dim i As Long
    Dim Last As Long
    
    Last = Application.CurrentProject.AllModules.Count - 1
    
    For i = 0 To Last
       DoCmd.OutputTo acOutputModule, CurrentProject.AllModules(i).Name, "txt", "c:\" & Cstr(i) & ".txt"
    Next i
    
    End Function
    It doesn't get any more complicated than that!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import multiple text files automatically
    By instructorTek in forum Import/Export Data
    Replies: 30
    Last Post: 10-20-2012, 04:50 PM
  2. need help with import Module
    By marubal21 in forum Modules
    Replies: 1
    Last Post: 07-22-2010, 12:23 AM
  3. Field returns error when I import Text Files to Access
    By geng in forum Import/Export Data
    Replies: 3
    Last Post: 06-01-2010, 02:20 PM
  4. Replies: 2
    Last Post: 05-25-2010, 02:45 PM
  5. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums