For loop for each file in a folder?

[Kieran1028]

I've got a macro that imports a .txt file and moves the data around, an
copies some things here and there... I need to run this macro on abou
40 different .txt files in a folder. Is there a way to write
For-loop that will run the macro on EACH text file in a give
directory?

Thanks,
Kiera

 

[Tom Ogilvy]

sFile = dir("C:\Myfolder\*.txt")
do while sFile <> ""
bk =Workbooks.Open("C:\MyFolder\" & sFile)
. . .
bk.Close SaveChanges:=false
sFile = Dir()
Loop

If you are going to save the changed files, you might want to use the above
construct to build an array of file names, then process that array rather
than process the files immediately. There is a KB article that says that
changing the directory in the middle of a DIR loop can cause problems
although I have never encountered any.