Sub FormatOpay()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim OPAYPath As String
Dim OPAYMonth As String
Dim sFilename As String

'Establish Directory Path
OPAYPath = "C:\VBA Test\2007\"
OPAYMonth = "03" ' each month this number will change

On Error Resume Next

sFilename = Dir(OPAYPath & "39385\*" & OPAYMonth & ".xls")
Do While sFilename < ""
Call oPayformat
sFilename = Dir()
Loop

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Kim" wrote in message
...
Hi all,

Each month one of systems exports a bunch of excel documents into various
directories. I am writing a macro that will automatically format each
file. I've attached part of my code below. My question is, is there
a more efficient way for the macro to open each file and format it without
me having to list the path of each file. Keep in mind that I have about
50 files (hence 50 file paths). I'm still a newbie so I have lots to
learning about programming. Any help is appreciated.

Thanks Kim


Sub FormatOpay()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim OPAYPath As String
Dim OPAYMonth As String

'Establish Directory Path
OPAYPath = "C:\VBA Test\2007\"
OPAYMonth = "03" ' each month this number will change

On Error Resume Next

'Open Workbooks to be formatted
Workbooks.Open Filename:=OPAYPath & "39385\MEOPGL" & OPAYMonth & ".xls"
Call OpayFormat

Workbooks.Open Filename:=OPAYPath & "34335\MSOPGL" & OPAYMonth & ".xls"
Call OpayFormat

Workbooks.Open Filename:=OPAYPath & "34392\MTOPGL" & OPAYMonth & ".xls"
Call OpayFormat
' I will have about 47 more file paths; the OpayFormat macro formats and
automatically closes each file once it is formatted.

End Sub