Loop through ".DAT" files, open run code, close next

[Les Stout]

Hi all,

I have a folder called downloads which contains a number of .DAT files
and i need to loop through them one by one, open them, run some code
which will close it on completion of the code then open the next one
until i have processed all files in the folder. When i have processed
the file i put a "d" at the end of the file name.e.g.
IRSQM_010306.DAT before and IRSQM_010306_d.DAT after.

Is this at all possible ? if so some help with some code would be
appreciated.

Thanks in advance

Les Stout

 

[Andrew Taylor]

Something like this should do it (untested):

Sub ProcessFiles
Dim strFile as string, strNewName as string
Dim strPath as string, wb as workbook
strPath = "C:\<whatever>\downloads\"
strFile = dir (strPath & "*.dat")
do until strFile = ""
set wb = workbooks.open (strPath & strFile)

' your code here

wb.close savechanges:=true
' previous line not needed if your code closes the file

strNewName = left(strFile, len(strfile)-4) & "_d.dat"
Name strPath & strFile as strPath & strNewName

strFile = Dir () ' get next file name
Loop
End Sub


HTH
Andrew

 

[Andrew Taylor]

Just to clarify: my code assumes the .DAT files can
be opened as Excel workbooks. If this is not the
case then you can use the VBA file-handling
functionality, e.g.

dim strData as string
Open strPath & strFile for input as #1
strData =input (LOF(1),#1)
Close #1
'' process data
Open strPath & strFile for output as #1
Print #1, strData
Close #1

 

[Les Stout]

Thanks for the code and advice Andrew, will give it a bash and let you
know.

best regards,

Les Stout