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 ***

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 ***

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 ***

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 ***