Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Calhoun99

Technical User
Oct 3, 2018
5
US
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
 
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

 
So now it'll rename just the first .txt file it finds ...
 
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. :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top