Likes Likes:  0
Results 1 to 7 of 7

Thread: VBA to Import Updated Forms in Text Files

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to Import Updated Forms in Text Files

    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/...62688#17362688

    https://stackoverflow.com/questions/...r-similar-file

    https://stackoverflow.com/questions/...r-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 #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 = formPath & "" & Name & ".vb"
                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
    '--- 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 #QryCount 
             
                        Debug.Print QryNames, QryText
                        Print #QryCount , QryNames, vbNewLine, QryText
                        
                    Close #QryCount 
                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"))
        }

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    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.?
    Excel 2007

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,170
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    Quote Originally Posted by xenou View Post
    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 by AlexB123; Feb 11th, 2019 at 11:06 AM.

  5. #5
    Board Regular
    Join Date
    Dec 2014
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    Quote Originally Posted by welshgasman View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,170
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    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 by xenou; Feb 11th, 2019 at 02:59 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Dec 2014
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Import Updated Forms in Text Files

    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 by AlexB123; Feb 14th, 2019 at 12:24 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •