More efficient way for macro to run on multipe files?

[Kim]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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]

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)