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