Solved Is there a way to automate the manual replacement of multiple linked files?

[BlueFllame]

Hey guys,

I've built a DB process for our team which automates manual monthly reporting. The problem is that it uses 3 linked text files which live in our network drive, and each of them has to be replaced for every separate worksheet. The report in total generates 22 worksheets so after each one is done I close the database and updated the text files. This adds up in time especially because our network speed is slow.

I'm wondering if there's a way to point the DB to look at specific folder and loop through each file in it as the data source for one of the linked files. If this is possible then I'll create 3 separate folders to replace each linked file/table.

If anyone has any suggestions I would appreciate it. There might be a totally easier way to go about this, but I can't come up with anything else.

Thanks.

 

[GPGeorge]

Short answer is yes, it can be done. Long answer is that the VBA to do it is not simple. I suspect there are examples here and elsewhere. I would start with a search for "recursive directory" and variations of that phrase.

Perhaps the authors of some of them will see your request and provide a specific link.

 

[The_Doc_Man]

You can look at the FileSystemObject and its many included methods. That is the best starting point, or at least that's how I see it.

In general, you would identify a folder, then do a GetFolder, which gives you a Folder Object (within FSO), and then can step through the files in that folder using a "FOR EACH file-variable" type of loop.

To include FSO in what you do in VBA, you must have a reference to the Windows scripting library. In this link below, you will find the "top" of the help tree for FSO. The top sheet includes a bit of code that would make a run-time reference FSO in the correct library, but static references work just as well.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

Browse through the methods that you will find listed to the left of the text portion of that link and the properties. The file and folder objects are also listed in the left so you can click on them and learn how FSO treats them. The article also hyper-link-lists the functions that let you perform file operations as though you were on the keyboard of Windows Explorer.

 

[Isaac]

I would agree with GPGeorge's recommendation to google and throw the phase VBA DIR in there.

Although I love FSO, it is DIR which is actually made for recursion, so to speak, and recursive directory traversing is one of the only times I use it.

It can be a little mind-bending at first, but don't deploy any code you don't personally understand, take the time to understand what's happening ... then enjoy the results!

 

[Pat Hartman]

Why do you change the file names every month? If you want to save the old files, move them to an archive folder with the dates but leave the "current" files in the "current" folder with static names. As long as you don't change the names of the linked files, you don't need to do anything.

 

[BlueFllame]

Why do you change the file names every month? If you want to save the old files, move them to an archive folder with the dates but leave the "current" files in the "current" folder with static names. As long as you don't change the names of the linked files, you don't need to do anything.

Hi Pat. Sorry if I wasn't clear, but the issue is that the database runs once to produce one report/worksheet and then I have update the linked text files and run it again etc. until all 22 reports are done. I just did that today and it took me about 6 hours.

 

[MajP]

I'm wondering if there's a way to point the DB to look at specific folder and loop through each file in it as the data source for one of the linked files. If this is possible then I'll create 3 separate folders to replace each linked file/table

Can you explain your process very clearly? I am confused on where the files are located, what you are linking to.
Everyone keeps talking about spanning recursive subfolders, but I am not seeing that need

I interpret the above to mean each time you run the report you link to three text files. Then those three text files are replaced in three specific folders with the next three text files. I assume you rename these. You do this 22 times. Does that mean there are 66 files?
Can you link directly to the files? It seems you have to move them to specific folders. I am sure this can be automated. FSO can move, rename files. Or maybe you just need to know the file names and change the links.

 

[Pat Hartman]

but the issue is that the database runs once to produce one report/worksheet and then I have update the linked text files and run it again etc. until all 22 reports are done. I just did that today and it took me about 6 hours.

I hear what you are saying but I haven't a clue why you would be doing this. What are you trying to accomplish? Use business terms. Don't give us code to fix. Are you talking about relinking files to the exported spreadsheet? Why, where ae the 22 sets of files coming from? Why doesn't Access export the spreadsheet ready to go? Why are the files linked rather than included in the spreadsheet?

 

[BlueFllame]

Can you explain your process very clearly? I am confused on where the files are located, what you are linking to.
Everyone keeps talking about spanning recursive subfolders, but I am not seeing that need

I interpret the above to mean each time you run the report you link to three text files. Then those three text files are replaced in three specific folders with the next three text files. I assume you rename these. You do this 22 times. Does that mean there are 66 files?
Can you link directly to the files? It seems you have to move them to specific folders. I am sure this can be automated. FSO can move, rename files. Or maybe you just need to know the file names and change the links.

Oh sorry it looks like my wording confused everyone so let me try again. The database takes the information from 3 linked source files and manipulates it to create the final report. These files I manually grab from 3 different folders, because they are generated by another team, and then I put them in our "working" folder which is located on the network and this is the folder the DB uses for the linked files. I then open the DB and generate one Excel report which is let's say for one business segment. Then I close the database, grab another set of 3 files, overwrite the existing linked files, open the DB and generate the second report. So on and so forth until all 22 reports are generated. So overall yes 66 is the total number of source data files.

I guess another approach would be to spend the time to create links to all 66 files and then every month I would just copy over the new files, but that would mean I would have to update my whole DB with triple the amount of queries so don't know if that's worth it.

This could be totally wrong, but in my mind I envision the following complete process:
1. The code loops through the 3 source folders, takes the first file from each of them and does like "save as replace" and replaces the 3 linked files in the "working" folder. The tricky part here which I haven't mentioned yet is that each group of 3 has to be specific. It has to grab for example, file A from folder 1, file AA from folder 2, and file AAA from folder 3.
2. Then it executes my current code like it does now and generates the first report.
3. Goes back to the 3 source folders and copies the next 3 files, etc.

 

[ebs17]

' copies a single file
FileCopy source, destination

'moves a single file
Name oldpathname As newpathname

So if you know the path and file name of the file in question (both source and destination), you can always bring the desired individual files into your processing area in statement sequences and loops.
Know the path and file name: Standardized folder structures are helpful, for example to calculate a variable path before it is used.

 

[arnelgp]

he tricky part here which I haven't mentioned yet is that each group of 3 has to be specific.

can you Mention what are the specific files involved on each folder.
and what are common characteristics so that you can have them as a "group-of-3"?

 

[MajP]

I did a full working demo of this and was amazed at how little code it would require.

I have three source folders and a destination folder.

In each source folder there are CSV files with 2 records per file. Each "triplet" is related by Name. In this case a simple number, but it could be a date or a property of the file.
Example in Source1 the first file is
AnimalType1.Txt
This matches Source2
Sound1.Txt
And Source3
Legs1.Txt

The three files are needed to link together and an insert query is run

There is a form to allow you to browse to the Source and destination folders and then run the process

Here is the entire code to run the process of
1. Loop the files in folder 1
2. Find and get the matching file in source folders 2 and 3 by comparing the names to see that they have a matching pattern.
3. Move the triplet of files to the destination folder
4. Run an append query using the 3 files linked in the destination folder
repeat 1 until all files are processed

Option Compare Database
Option Explicit

Private FSO As FileSystemObject
Private SourceFiles1 As Scripting.Files
Private SourceFiles2 As Scripting.Files
Private SourceFiles3 As Scripting.Files

'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- FSO ---------------------------------------------------------------------------------
Private Sub cmdProcess_Click()
  CurrentDb.Execute "qryDelImport"
  Me.qryDisplay_subform.Form.Requery
  Me.Dirty = False
    If Not Dir(Me.txtSource1, vbDirectory) = "" And Not Dir(Me.txtSource2, vbDirectory) = "" And Not Dir(Me.txtSource3, vbDirectory) = "" And Not Dir(Me.txtDestination, vbDirectory) = "" Then
    Set FSO = CreateObject("Scripting.FileSystemObject")
    SetFiles
    moveFiles
  Else
    MsgBox "Verify all paths are correct.", vbInformation, "Bad Path"
  End If
End Sub

Public Sub SetFiles()
  Set SourceFiles1 = FSO.GetFolder(Me.txtSource1).Files
  Set SourceFiles2 = FSO.GetFolder(Me.txtSource2).Files
  Set SourceFiles3 = FSO.GetFolder(Me.txtSource3).Files
End Sub

Public Sub moveFiles()
  Dim file1 As Scripting.file
  Dim file2 As Scripting.file
  Dim file3 As Scripting.file
 
  For Each file1 In SourceFiles1
    Set file2 = getMatchingFile(file1, SourceFiles2)
    Set file3 = getMatchingFile(file1, SourceFiles3)
    FSO.CopyFile file1.Path, Me.DestinationPath & "\Import1.txt"
    FSO.CopyFile file2.Path, Me.DestinationPath & "\Import2.txt"
    FSO.CopyFile file3.Path, Me.DestinationPath & "\Import3.txt"
    CurrentDb.Execute "QryAppendImport"
    Me.qryDisplay_subform.Form.Requery
    MsgBox "Files copied in import folder and appended", vbInformation, "Appended"
 
  Next file1
End Sub

Public Function getMatchingFile(file1 As Scripting.file, SourceFiles As Scripting.Files)
  Dim fileMatch As Scripting.file
  For Each fileMatch In SourceFiles
    'need some pattern, to compare. I will check the number
     If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then
       Set getMatchingFile = fileMatch
       Exit Function
     End If
  Next
End Function

 

[MajP]

To get the matching members of your "triplet" the below code gets a file from Source1 and checks the other files in Source2 and Source3 for the corresponding name pattern.

Public Function getMatchingFile(file1 As Scripting.file, SourceFiles As Scripting.Files)
  Dim fileMatch As Scripting.file
  For Each fileMatch In SourceFiles
    'need some pattern, to compare. I will check the number
     If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then
       Set getMatchingFile = fileMatch
       Exit Function
     End If
  Next
End Function

My check if very simple it checks for example
AnimalType1.Txt ends in (1.txt) and sees that it matches other folders whose name ends in (1.txt).

So modify this line if your pattern is something more involved

If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then

If each of the matching source files are imported into the source folders on specific dates and they do not have an easy pattern to match then you can check the date created (assuming only a triplet is saved on any given day).

If datevalue(fileMatch.dateCreated) = datevalue(file1.datecreated) Then

 

[BlueFllame]

I did a full working demo of this and was amazed at how little code it would require.

I have three source folders and a destination folder.

View attachment 105606

In each source folder there are CSV files with 2 records per file. Each "triplet" is related by Name. In this case a simple number, but it could be a date or a property of the file.
Example in Source1 the first file is
AnimalType1.Txt
This matches Source2
Sound1.Txt
And Source3
Legs1.Txt

The three files are needed to link together and an insert query is run

There is a form to allow you to browse to the Source and destination folders and then run the process
View attachment 105607

Here is the entire code to run the process of
1. Loop the files in folder 1
2. Find and get the matching file in source folders 2 and 3 by comparing the names to see that they have a matching pattern.
3. Move the triplet of files to the destination folder
4. Run an append query using the 3 files linked in the destination folder
repeat 1 until all files are processed

Option Compare Database
Option Explicit

Private FSO As FileSystemObject
Private SourceFiles1 As Scripting.Files
Private SourceFiles2 As Scripting.Files
Private SourceFiles3 As Scripting.Files

'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- FSO ---------------------------------------------------------------------------------
Private Sub cmdProcess_Click()
  CurrentDb.Execute "qryDelImport"
  Me.qryDisplay_subform.Form.Requery
  Me.Dirty = False
    If Not Dir(Me.txtSource1, vbDirectory) = "" And Not Dir(Me.txtSource2, vbDirectory) = "" And Not Dir(Me.txtSource3, vbDirectory) = "" And Not Dir(Me.txtDestination, vbDirectory) = "" Then
    Set FSO = CreateObject("Scripting.FileSystemObject")
    SetFiles
    moveFiles
  Else
    MsgBox "Verify all paths are correct.", vbInformation, "Bad Path"
  End If
End Sub

Public Sub SetFiles()
  Set SourceFiles1 = FSO.GetFolder(Me.txtSource1).Files
  Set SourceFiles2 = FSO.GetFolder(Me.txtSource2).Files
  Set SourceFiles3 = FSO.GetFolder(Me.txtSource3).Files
End Sub

Public Sub moveFiles()
  Dim file1 As Scripting.file
  Dim file2 As Scripting.file
  Dim file3 As Scripting.file

  For Each file1 In SourceFiles1
    Set file2 = getMatchingFile(file1, SourceFiles2)
    Set file3 = getMatchingFile(file1, SourceFiles3)
    FSO.CopyFile file1.Path, Me.DestinationPath & "\Import1.txt"
    FSO.CopyFile file2.Path, Me.DestinationPath & "\Import2.txt"
    FSO.CopyFile file3.Path, Me.DestinationPath & "\Import3.txt"
    CurrentDb.Execute "QryAppendImport"
    Me.qryDisplay_subform.Form.Requery
    MsgBox "Files copied in import folder and appended", vbInformation, "Appended"

  Next file1
End Sub

Public Function getMatchingFile(file1 As Scripting.file, SourceFiles As Scripting.Files)
  Dim fileMatch As Scripting.file
  For Each fileMatch In SourceFiles
    'need some pattern, to compare. I will check the number
     If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then
       Set getMatchingFile = fileMatch
       Exit Function
     End If
  Next
End Function

Hi MajP,

Thanks so much. I've been drowning in other work so I haven't had the chance to apply this. I have to redesign our database anyway since it has many outdated queries and your code has given me a lot of ideas. I'll report back when I'm done.

 

[BlueFllame]

To get the matching members of your "triplet" the below code gets a file from Source1 and checks the other files in Source2 and Source3 for the corresponding name pattern.

Public Function getMatchingFile(file1 As Scripting.file, SourceFiles As Scripting.Files)
  Dim fileMatch As Scripting.file
  For Each fileMatch In SourceFiles
    'need some pattern, to compare. I will check the number
     If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then
       Set getMatchingFile = fileMatch
       Exit Function
     End If
  Next
End Function

My check if very simple it checks for example
AnimalType1.Txt ends in (1.txt) and sees that it matches other folders whose name ends in (1.txt).

So modify this line if your pattern is something more involved

If Right(fileMatch.Name, 5) = Right(file1.Name, 5) Then

If each of the matching source files are imported into the source folders on specific dates and they do not have an easy pattern to match then you can check the date created (assuming only a triplet is saved on any given day).

If datevalue(fileMatch.dateCreated) = datevalue(file1.datecreated) Then

Hey MajP,

Thank you so much! I was able to use your code as a base to modify it for my needs and everything works! My only question is that for some reason my database gets bloated after every loop and after loop18 it crashes because it reaches the 2GB limit. I have split my database and I don't actually store any long term data in the back-end. I have like 10 delete queries that run after every loop to empty the tables for the new data, and the actual data being used from the raw files is around 100MB so I can't figure out how it's bloating to 2GB.

 

[theDBguy]

I can't figure out how it's bloating to 2GB.

Perhaps you could review this old article related to that problem.

Should I use Make-Table or Append Query?

I have been involved in a few discussions over what causes database bloat more when it comes to using temporary tables: using a Make-Table q...

thedbguy.blogspot.com

 

[gemma-the-husky]

Personally, I wouldn't want to link to text files.
I would import them to the database, or a side database and use the tables.

Then it's probably a matter of standardising your file/table naming process.