That's good if you have few enough to define in one array, it simplifies
matters.

I think you should have left the Dir check in there, in case you make an
error in the array definition, or a file gets deleted.

--
HTH

Bob

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

"Kim" wrote in message
...
Thanks Bob again for all your help. I modified your code slightly and it
works perfectly.

Kim


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

Dim OPAYPath As String
Dim OPAYMonth As String
Dim DataFiles As Variant
Dim Opaybook As Workbook
Dim i As Long

'Establish Directory Path
OPAYPath = "C:\VBA Test\2007\"
OPAYMonth = "04"
DataFiles = Array("39385\MEOPGL", "34335\MSOPGL", "34392\MTOPGL")

On Error Resume Next
For i = LBound(DataFiles) To UBound(DataFiles)

'Open Opay Workbooks to be formatted
Set Opaybook = Workbooks.Open(Filename:=OPAYPath & DataFiles(i) &
OPAYMonth & ".xls")
Call OpayFormat


"Bob Phillips" wrote in message
...
You could just move all files not required to a separate directory,
sometimes it is best not to try and do everything automatically.

But ... if you must

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

Dim OPAYPath As String
Dim OPAYMonth As String
Dim sFilename As String
Dim aryFiles
Dim i As Long

aryfiles=array("MEOPGL","MSOPGL","MTOPGL") ' add the rest

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

For i = LBound(aryFiles) to UBound(aryfiles)

sFilename = OPAYPath & "39385\" & OPAYMonth & ".xls")
If Dir(sFilename) < "" Then
Call oPayformat
End If
Next i

'Then repeat for the other directory(ies)

End Sub

--
---
HTH

Bob

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



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

Thanks for the quick reply. However, however, the problem is that there
are other files in the same directory that do not need to be altered.
In addition, there are other folders besides the "39385" folder that
will need to be looked at. I thought about listing the file directories
in a list in the actual workbook (cells A1 through A50) and have the
macro loop thru the list to get the file path. Any ideas or advice?

Thanks Again Kim