# 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...s-recursively-to-text-files/17362688#17362688

https://stackoverflow.com/questions...s-programmatically-from-a-cls-or-similar-file

https://stackoverflow.com/questions...s-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
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
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 "rn"))
}

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### 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
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
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
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
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!

Replies
7
Views
199
Replies
3
Views
187
Replies
23
Views
426
Replies
9
Views
291
Replies
2
Views
120

1,123,388
Messages
5,601,373
Members
414,447
Latest member
CRAVIN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back