VBA code to re-name a text file, execute a macro, then repeat (for all files in a folder)

[Calhoun99]

I am very new to VBA, so please be patient. I need to to re-name .txt files in a folder to a file name that never changes (it is linked as a table in Access). Run a macro that scrapes data into another table, then repeat the process for all the files in the folder (loop). The code below renames the 1st file and runs the macro, but that is as far as I get. It does not loop correctly. Any help is greatly appreciated!

Function process()

Dim tmp As String
tmp = Dir("C:\Users\Calhoun\Documents\REPORTING\Correspondence\*.txt")
Do While tmp > ""
If Len(Dir("C:\Users\Calhoun\Documents\REPORTING\Correspondence\STATIC_FILE_NAME.txt")) <> 0 Then
Kill "C:\Users\Calhoun\Documents\REPORTING\Correspondence\STATIC_FILE_NAME.txt"
End If
Name "C:\Users\Calhoun\Documents\REPORTING\Correspondence\" & tmp As "C:\Users\Calhoun\Documents\REPORTING\Correspondence\STATIC_FILE_NAME.txt"

DoCmd.RunMacro "RunQueries"

tmp = Dir
Loop

End Function

 

[Calhoun99]

This was resolved by removing the If Len(Dir(...)) condition. Using Dir inside the loop breaks the Dir outside the loop.
Corrected code below.

Function process()
'
Dim tmp As String
tmp = Dir("C:\Users\Calhoun\Documents\REPORTING\Correspondence\*.txt")
Do While tmp > ""
on error resume next
Kill "C:\Users\Calhoun\Documents\REPORTING\Correspondence\STATIC_FILE_NAME.txt"
On Error Goto 0
Name "C:\Users\Calhoun\Documents\REPORTING\Correspondence\" & tmp As "C:\Users\Calhoun\Documents\REPORTING\Correspondence\STATIC_FILE_NAME.txt"

DoCmd.RunMacro "RunQueries"

tmp = Dir
Loop
End Function

 

[strongm]

So now it'll rename just the first .txt file it finds ...

 

[Calhoun99]

the tmp = Dir will repeat for that specific file, and return an empty string when all files are renamed, which is your exit condition - the code loops correctly. For all file in the specified folder.

 

[strongm]

Oops, sorry, missed the 2nd tmp=dir