VBA to Import Updated Forms in Text Files

AlexB123

Board Regular
Hi all,

I have an ongoing need to update every form in a database by replacing the production file paths to folders, reports and whatnot with file paths to testing environment folders, reports, etc. I am constantly updating this database, and have been copying my changes from a text editor into every form in a production version of my database. I have been using the text editor's find and replace all function for these strings. My goal is to automate this process, and I have created code that exports all the forms and queries to text files, as well as a PowerShell script that automatically performs the regex replacements I need.

However, my import procedure is not working. Each time it breaks on the .LoadAsText line. I am posting the links I used to create this code. Any help identifying my problem is appreciated.

Note: I also attempted to just write a VBA code within Access that would perform this path replacement for me, but cannot think of an equivalent regex / replace function. I am open to the easiest approach.

Thanks

https://stackoverflow.com/questions/16948215/exporting-ms-access-forms-and-class-modules-recursively-to-text-files/17362688#17362688

https://stackoverflow.com/questions/31596339/importing-a-module-into-access-programmatically-from-a-cls-or-similar-file

https://stackoverflow.com/questions/31596339/importing-a-module-into-access-programmatically-from-a-cls-or-similar-file

And here is my attempt to import:

Code:
         Option Compare Database
         Option Explicit
  Private Const VB_MODULE               As Integer = 1
  Private Const VB_CLASS                As Integer = 2
  Private Const VB_FORM                 As Integer = 100
  Private Const EXT_TABLE               As String = ".tbl"
  Private Const EXT_QUERY               As String = ".qry"
  Private Const EXT_MODULE              As String = ".bas"
  Private Const EXT_CLASS               As String = ".cls"
  Private Const EXT_FORM                As String = ".frm"
  'Private Const EXT_FORM                As String = ".vb"
  Private Const CODE_FLD                As String = "code"
  Private Const mblnSave                As Boolean = True               ' False: just generate the script
'
'
Public Sub importAllAsText()
            Dim oTable                  As TableDef
            Dim oQuery                  As QueryDef
            Dim oCont                   As Container
            Dim oForm                   As Document
            Dim oModule                 As Object
            Dim FSO                     As Object
            Dim strPath                 As String
            Dim frmPath                As String
    
            Dim strName                 As String
            Dim frmName                As String
            
            Dim strFileName             As String
            Dim frmFileName             As String
            Dim tmpName As String
            
            Dim dlgForms                As FileDialog
            
            Dim I As Integer: I = 0           ' iterator for file name array
            Dim vFileList() As String ' array for file names
            Dim db As DAO.Database
            
            
'**
    'On Error GoTo errHandler
    On Error GoTo 0
    
    Set db = CurrentDb()
    strPath = CurrentProject.Path
    'Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set dlgForms = Application.FileDialog(msoFileDialogFolderPicker)
        With dlgForms
            .Title = "Forms Folder"
            .Show
            frmPath = .SelectedItems(1)
        End With
    
    vFileList = GetFileList(frmPath)
    
  
    Set oCont = db.Containers("Forms")
    For Each oForm In oCont.Documents
        strName = oForm.Name
        'strFileName = strPath & "\" & strName & EXT_FORM
        
        For I = LBound(vFileList) To UBound(vFileList)
            
            If StrComp(strName, Left(vFileList(I), Len(vFileList(I)) - 4), vbTextCompare) = 0 Then
            
            tmpName = Left(vFileList(I), Len(vFileList(I)) - 4)
            frmFileName = frmPath & "\" & vFileList(I)
        
                If mblnSave Then Application.LoadFromText acForm, tmpName, frmFileName
                Debug.Print "Application.LoadFromText acForm, """ & vFileList(I) & """, """ & frmFileName & """"
                'GoTo EndLoop
            End If
            
            I = I + 1
'EndLoop:
        Next
    Next
    'If mblnSave Then MsgBox "Files saved in  " & strPath, vbOKOnly, "Export Complete"
    MsgBox "Complete!!"
    
Exit Sub
errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf
    Resume Next
End Sub

Function GetFileList(pDirPath As String) As Variant
On Error GoTo GetFileList_err
    ' Local constants / variables
    Const cProcName = "GetFileList"
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim c As Double           ' upper bound for file name array
    Dim I As Double           ' iterator for file name array
    Dim vFileList() As String ' array for file names
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(pDirPath)
    c = objFolder.Files.Count
    I = 0
    ReDim vFileList(1 To c)  ' set bounds on file array now we know count
    'Loop through the Files collection
    For Each objFile In objFolder.Files
        'Debug.Print objFile.Name
        I = I + 1
        vFileList(I) = objFile.Name
    Next
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    GetFileList = vFileList
GetFileList_exit:
    Exit Function
GetFileList_err:
    Debug.Print "Error in ", cProcName, " Err no: ", Err.Number, vbCrLf, "Err Description: ", Err.Description
    Resume Next
End Function
And here is what I've used to export the code to text. I was exporting with the ending ".vb" so my text editor could open up the file with specific formatting. But in this case, I tried both exporting with no file type id as well as ".frm".

Code:
Option Explicit
Option Compare Database
Public Sub SaveToFile()                  'Save the code for all modules to files in currentDatabaseDir\Code
On Error GoTo 0
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)
    Dim formPath As String
    Dim dlgForms As FileDialog
    Set dlgForms = Application.FileDialog(msoFileDialogFolderPicker)
    With dlgForms
        .Title = "Forms Folder"
        .Show
        formPath = .SelectedItems(1)
    End With
'--- 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 = formPath & "\" & Name & ".vb"
      
            If (Dir(FileName) <> "") Then
                Kill FileName                      'Delete previous version
            End If
        'Save current version
        F = FreeFile
        Open FileName For Output Access Write As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] , Access.Modules(Name).Lines(1, LineCount)
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] 
            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 = formPath & "" & Name & ".vb"
            If (Dir(FileName) <> "") Then
                Kill FileName
            End If
        F = FreeFile
        Open FileName For Output Access Write As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] , Access.Forms(Name).Module.Lines(1, LineCount)
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F]#F[/URL] 
            If Not WasOpen Then
                DoCmd.Close acForm, Name
            End If
    Next
'--- SAVE THE QUERIES CODE ---
    Dim queryPath As String
    Dim dlgQry As FileDialog
    Dim db As Database
    Dim Qry As QueryDef
    Dim QryNames As String
    Dim QryText As String
    Dim QryCount As Integer
    
        Set dlgQry = Application.FileDialog(msoFileDialogFolderPicker)
            With dlgQry
                .Title = "Queries Folder"
                .Show
                queryPath = .SelectedItems(1)
            End With
    
    Set db = CurrentDb
        
        QryCount = FreeFile()
        
            For Each Qry In db.QueryDefs
                        
                QryNames = Qry.Name
                QryText = Qry.SQL
                    
                    FileName = queryPath & "" & QryNames & ".sql"
                    Open FileName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=QryCount]#QryCount[/URL] 
         
                    Debug.Print QryNames, QryText
                    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=QryCount]#QryCount[/URL] , QryNames, vbNewLine, QryText
                    
                Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=QryCount]#QryCount[/URL] 
            Next
            
    db.Close
    Set db = Nothing
MsgBox "Created source files in " & formPath
MsgBox "Created query files in " & queryPath
End Sub
And here is the powershell code, in case it changes the formatting or encoding of the text files:

Code:
$OldString = 'C:\TEST_DB\EXAMPLE\'
 $NewString = 'C:\TEST_DB\NAME_CHANGE\'
 
 Get-ChildItem C:\DESKTOP\FORMS_PROD\* -recurse |
    Foreach-Object {
        $c = ($_ | Get-Content) 
        $c = $c -replace [RegEx]::Escape($OldString),$NewString
        [IO.File]::WriteAllText($_.FullName, ($c -join "`r`n"))
    }
 

welshgasman

Well-known Member
I can't help with the import, something I have never done myself.

However although most people recommend not to use mapped drives, this is a good reason to I believe?

If you map P: to the production path, you can just change P: to point to your testing path.

Alternatively I'd be looking in storing them in some way in a table.?
 

xenou

MrExcel MVP, Moderator
In general you want to have these paths stored somewhere - in a table, config file, or something like that. Then you can pull the path you need based on test or prod. It should be unnecessary to edit the code directly. In some cases test and prod environments are identical (ideally, or at least nearly the same, such as C:\Prod\myFile.txt and C:\Test\myFile.txt). Then you only need to change the relative starting point of the file.
 

AlexB123

Board Regular
In general you want to have these paths stored somewhere - in a table, config file, or something like that. Then you can pull the path you need based on test or prod. It should be unnecessary to edit the code directly. In some cases test and prod environments are identical (ideally, or at least nearly the same, such as C:\Prod\myFile.txt and C:\Test\myFile.txt). Then you only need to change the relative starting point of the file.
So what would that look like? Perhaps a hidden startup form, where as Admin I choose which path I want to use in a combo box, and have these paths stored in a bound table? I assume that the paths in the forms would just be a variable, but how would I set the variable across all forms?

So far, I have worked to standardize the pathname variables across all my forms, and I only need to switch the base directory ... is it possible to set a single global variable that every form references? Open to any ideas ... thanks!
 
Last edited:

AlexB123

Board Regular
I can't help with the import, something I have never done myself.

However although most people recommend not to use mapped drives, this is a good reason to I believe?

If you map P: to the production path, you can just change P: to point to your testing path.

Alternatively I'd be looking in storing them in some way in a table.?
I don't think that would be possible in my work environment ... but maybe I just don't understand enough about computers?

I think storing them in a table seems to be the way to go ... but I'm not sure how the form path variables will be set.
 

xenou

MrExcel MVP, Moderator
if the pathnames are all standardized then you can just use a path building function. Base path (different for prod or test) and target path.

for example:

PSEUDOCODE
Code:
func getpath(relativePath)
    if (environment == "PROD") 
        return "C:\myprodpath\" + relativePath
    else
        return "C:\mytestpath\" + relativePath
You could also put all your paths into a table:

ID FileName EnvironmentName FullPath
1 abc.txt Prod C:\somepath\abc.txt
1 abc.txt Test C:\someotherpath\abc.txt
2 def.txt Prod C:\somepath\foo\def.txt
2 def.txt Test C:\someotherpath\bar\def.txt


Then you just lookup the data as needed right from the table (DLOOKUP), or put it into a dictionary or list or what have you.

I'm sure there are other solutions. One of them being going through a process of editing the source code and doing replacements is one of course. Which is what you were proposing in post 1. But that seems dangerous enough - now you'd have to maintain the code for updating the code, and it would be hard to test and keep up to date (plus this is a process that somehow seems like overkill - its very unusual to have to do anything like what you are suggesting on a regular basis, that I've heard of anyway, albeit possible to do).
 
Last edited:

AlexB123

Board Regular
I took your advice and created a form that can only be seen by an admin, where a combo box is populated by a sql statement to table with the stored paths for "TEST" and "PROD". I also added a query that sets a flag for which path is the "environment" variable.

I want the database to determine the environment for the paths of "BaseDir" at startup, so I declared "BaseDir" as a Public variable in a standard module.

Code:
Option Compare Database
'Declares BaseDir as Public Global Variable for all forms
Public BaseDir As String

'Public Function SetEnv()
'BaseDir = DLookup("[strNM]", "tblDIRS", "strVAR = 1")  
'EndFunction
Originally I tried to use DLookup to set the variable on startup, but that didn't work. I do have a user logon form that launches for users as soon as the DB starts, so I added the following to "Form_Open":

Code:
Private Sub Form_Open(Cancel As Integer)
    'Attempt to implement path changing form
    BaseDir = DLookup("[strNM]", "tblDIRS", "strVAR = 1")    
End Sub
It worked when I was logged in with the backend open as an admin, but when I opened it as a normal user, the startup form broke on Form_Open. So I changed it to:

Code:
Private Sub Form_Load()
    BaseDir = DLookup("[strNM]", "tblDIRS", "strVAR = 1")
    DoCmd.Maximize
End Sub
This worked, I was able to change all the paths used by each separate form. Generally, I build the path like:

Code:
Dim exDir1 As String: exDir1 = BaseDir & "FOLDER_1\"
Dim exDir2 As String: exDir2 = exDir1 & "FOLDER_2\"
I'm going to work on a better function for a later release ... But for now do you see any problem with this approach? The startup form where users login closes and then moves to a navigation form for users. This is a split database over a shared network structure (although tblDIRS is a local table) ... so there are at times the usual failures, etc. Would a loss of connectivity, or anything like that break the setting of the global variable? Is there a way I could create and call a function at startup that refers the tblDIRS?

Thanks,
 
Last edited:

xenou

MrExcel MVP, Moderator
This is fine although I'm not a big fan of global variables. I would probably prefer a function that uses the DLookup directly when needed:

Code:
Private Function GetBaseDirectory() as String
    GetBaseDirectory = DLookup("[strNM]", "tblDIRS", "strVAR = 1")
   
End Sub
Can't vouch for my VBA syntax so consider the above pseudocode - its getting rusty. You can then use it almost the same way as the global variables:
Code:
Dim exDir1 As String: exDir1 = GetBaseDirectory() & "FOLDER_1\"
Dim exDir2 As String: exDir2 = exDir1 & "FOLDER_2\"
It might be a little slower but probably not noticeably. tblDIRS should be small. I would also index the records in that table to disallow duplicates (create a primary key or unique index to ensure the data remains as it should).
 

xenou

MrExcel MVP, Moderator
Another idea for a "best of both worlds approach" is to use a static variable in your function so that it can store the result without needing to do lookups on each call - should be better on some milliseconds scale.

Code:
Public Function GetBaseDirectory()
Static base_directory
        
    If IsEmpty(base_directory) Then
        base_directory = DLookup("[strNM]", "tblDIRS", "strVAR = 1")
    End If
    
    GetBaseDirectory = base_directory

End Function


Sub CheckItOut()
Dim s
Dim t As String

    s = GetBaseDirectory()
    t = GetBaseDirectory()
    Debug.Print s
    Debug.Print t

End Sub

The down side of the above and the global variable approach is the the database (or all front end databases!) would probably need to be shut down and restarted to capture changes in the variable value. If you always do the lookup then you will get the correct value from the table as soon as the table is updated.

Also note that it is possible for global variables to get wiped out (to answer an earlier question). In my experience that happens when an uncaught error occurs and the user hits the "reset" button when the error dialog pops up (what else can they do - most users cannot go into the debugger, and probably shouldn't either). Hopefully any possibly errors are being caught. But it would be confusing to a user if they needed to restart for some reason (especially if they don't know what to do).
 
Last edited:

AlexB123

Board Regular
@xenou

Many, many thanks!

I think I'm going to go with this last approach ... I think the function idea is more reliable than the global variable approach, and the creation of a static variable addresses my concerns about repeated lookups.

Thank you for all the options!
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top