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

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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

Les Stout wrote:
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

*** Sent via Developersdex http://www.developersdex.com ***

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

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


Andrew Taylor wrote:
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

Les Stout wrote:
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

*** Sent via Developersdex http://www.developersdex.com ***

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

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

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***