Here's an example of using DIR in a loop:

FileName = Dir("c*.xls")
Do While FileName < ""
'your code and acitivities go here
FileName = Dir
Loop

'Set calc to manual
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

'Set calc to auto
With Application
.Calculation = xlAutomantic
.MaxChange = 0.001
End With