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