-1

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
Erik A
  • 31,639
  • 12
  • 42
  • 67
Calhoun
  • 5
  • 2
  • You're invoking `Dir` inside the loop, for one specific file name; the `tmp = Dir` will repeat for that specific file, and return an empty string, which is your exit condition - the code loops correctly, exactly as written. If you mean to delete `*.txt` files, then `tmp` has the file you want to delete - remove the `If Len(Dir(...))` condition. – Mathieu Guindon Oct 04 '18 at 00:06
  • See [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba). – Comintern Oct 04 '18 at 00:07
  • Why are you killing one specific file instead of `tmp`, and expecting it to `Kill` all .txt files in the folder? – Mathieu Guindon Oct 04 '18 at 00:07

1 Answers1

0

Your Adhok solution is 

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

Using Dir inside the loop breaks the Dir outside the loop as Mathieu Guindon mentiond in comment

Siyon DP
  • 504
  • 6
  • 20
  • Please mark my answare as correct, just press the green v in the left of the answare, so as everyone will see your question is answared, thanks. – Siyon DP Oct 07 '18 at 08:27