The best way is to create one Excel file that contains in its
ThisWorkbook module an Open event procedure that contains or calls the
code that opens each workbook and updates it. E.g.,

'[ThisWorkbook]
Private Sub Workbook_Open()
Dim Path As String
Dim WBName As String
Dim WB As Workbook

Path = "C:\Test" 'CHANGE AS NEEDED
ChDrive Path
ChDir Path
WBName = Dir("*.xls", vbNormal)
Do Until WBName = vbNullString
Set WB = Workbooks.Open(WBName)
Application.Run WB.Name & "!Refresh"
WB.Close savechanges:=True
WBName = Dir
Loop
For Each WB In Application.Workbooks
If WB.Path = vbNullString Then
WB.Close savechanges:=False
Else
If StrComp(WB.FullName, ThisWorkbook.FullName, _
vbTextCompare) < 0 Then
WB.Close savechanges:=True
End If
End If
Next WB

Application.Quit

End Sub


This will sequentially open all XLS files in the directory specified
in the Path variable, execute a procedure named "Refresh" in each
workbook and the close it. Finally, it shuts down Excel.

Now, use Windows Task Scheduler to schedule Excel to run and open the
workbook containing the code above at the desired time. The
command-line for Task Scheduler is (all on one line)

"C:\program files\microsoft\microsoft office\office12\excel.exe"
"C:\MyFiles\Master.xls"

where C:\MyFiles\Master.xls is the workbook contain the code above.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]










On Thu, 28 Jan 2010 10:26:01 -0800, Cam
wrote:

Hello,

I have several (10) Excel files in a "Reports" folder on a network drive,
each file have a refresh macro calls "Refresh" to update data from an Access
file.
Each morning, I have to open all the Excel files one at a time to run the
macro.

How can I create a VBA code to run the macro on all those files located in
the "Report" folder each day whether automically or from a macro button?

Thanks for any help.