Refresh Macro to run each day?

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

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.
 
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]
 
hi
i had a similar problem some years back. using 2000 i think. i solved the
problem by creating a master file with all the refresh macros in it and a
master sub that called all the refresh macros. most of my macros not only
refreshed the data but also processed the refreshed data further and created
a summary report in the master file. don't know if you need that. i also used
the on time method to run the master macro a 5 in the morning. end
results...what used to take 3-4 hours manually was not done and with all
reports on the printer when i got to work thereby eliminating a half a days
work. weekends were skipped and some reports were only run once a week. i did
have to leave my pc on for the on time method to start but you could easily
attached the macro to a button and skip the macAAPrelaunchMR() and
macALaunchMR() macros. most of the last macro (macAStart()) is setting up the
summary report in the master file.
you didn't give enough info for me to write the macro for you but here is
the macro i use back then. maybe you can use it as a guide to develope your
own.
careful. some lines way wrap.

Sub macAAPrelaunchMR()

Set LookDate = Range("A1")
If LookDate = Date Then
MsgBox ("The Morning Routine has been run today. Update not allowed
at this time.")
Exit Sub 'don't run twice
Else
Range("B1").Select
Range(ActiveCell, ActiveCell.Offset(40, 5)).ClearContents
Range(ActiveCell, ActiveCell.Offset(40, 5)).Interior.ColorIndex
= Automatic
Selection.Font.ColorIndex = 0
Range("B3").Select

If Weekday(Now()) = 6 Then
ActiveCell.FormulaR1C1 = _
" This Macro will not start until Monday Morning about
5:00am " & Date + 2.5
Else
ActiveCell.FormulaR1C1 = _
"This macro will not start until 5:00am " & Date + 1
ActiveCell.Offset(1, 0).Select
End If
End If
Range("B5").Select
Call macALaunchMR

End Sub
Sub macALaunchMR()

If Weekday(Now()) = 6 Then '1 = Sunday, 2 = Monday, 3 = Tuesday, ect
'ActiveCell.FormulaR1C1 = "This macro will not start until 5:00am "
& Date + 2.5

Application.OnTime Now() + 2.5 + TimeValue("00:00:03"), "macAStart"
Else
Application.OnTime TimeValue("05:00:00"), "macAStart"
End If

End Sub

Sub macAStart()

Range("A1").Select
Set LookDate = Range("A1")
If LookDate = Date Then
MsgBox ("The Morning Routine has been run today. Update not allowed
at this time.")
Exit Sub
Else
Range("B2:H50").Clear
Call macBDolbyLoc

Windows("MRMacro.xls").Activate
Range("C3").Select
ActiveCell.FormulaR1C1 = "1."
Range("D3").Select
ActiveCell.FormulaR1C1 = "Dollars by Loc ran successfully."
Range("B3").Select

ActiveCell.FormulaR1C1 = Time

Call MacCMRPLocQty

Windows("MRMacro.xls").Activate
Range("C8").Select
ActiveCell.FormulaR1C1 = "2."
Range("D8").Select
ActiveCell.FormulaR1C1 = "MRP Location Quantities ran successfully."
Range("B8").Select
ActiveCell.FormulaR1C1 = Time
Range("D9").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("E9:F9").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Call macDOpenWOs

Windows("MRMacro.xls").Activate
Range("C13").Select
ActiveCell.FormulaR1C1 = "3."
Range("D13").Select
ActiveCell.FormulaR1C1 = "OpenWODev ran sucessfully."
'ActiveCell.FormulaR1C1 = "The OpenWO Report has been temporarily
deactivated."
Range("B13").Select
ActiveCell.FormulaR1C1 = Time

'Call macENegNumRpt

'Windows("MRMacro.xls").Activate
Range("C17").Select
ActiveCell.FormulaR1C1 = "4."
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "The Negative Number Report has been
temporarily deactivated ." 'ran successfully."
'Range("D18").Select
'ActiveCell.FormulaR1C1 = "A Printed report(2 copies) is on the
Printer."
Range("B17").Select
ActiveCell.FormulaR1C1 = Time
Range("D19:F20").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Call macFZStockRpt

Windows("MRMacro.xls").Activate
Range("C22").Select
ActiveCell.FormulaR1C1 = "5."
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "The Zero/Got Stock Report has been
temporarily deactivated." 'ran successfully."
'Range("D23").Select
'ActiveCell.FormulaR1C1 = "A Printed report(5 copies) is on the
Printer."
Range("B22").Select
ActiveCell.FormulaR1C1 = Time

Call macGDolWipTitles

Windows("MRMacro.xls").Activate
Range("C25").Select
ActiveCell.FormulaR1C1 = "6."
ActiveCell.Offset(0, 1).Select
'ActiveCell.FormulaR1C1 = "This Top 50 report turned off 3/4/04 to
correct a latent flaw."
'Range("D26").Select
'ActiveCell.FormulaR1C1 = "The report will be turned back on when
the flaw is corrected.)"
'ActiveCell.FormulaR1C1 = "The Top 50 Report has been temporarily
deactivated."
ActiveCell.Offset(1, 0).Select
'ActiveCell.FormulaR1C1 = "A Printed report(2 copies) is on the
Printer."
ActiveCell.FormulaR1C1 = "The Top 50 Report ran successfully."
ActiveCell.Offset(-1, -2).Select
ActiveCell.FormulaR1C1 = Time
Range("D27:G30").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With


If Weekday(Now()) = 2 Then '1 = Sunday, 2 = Monday, 3 = Tuesday, ect

Call macHNonBOM

Else

Range("C32").Select
ActiveCell.FormulaR1C1 = "7."
ActiveCell.Offset(0, 1).FormulaR1C1 = " The NonBOM report did
not run. Today is not Monday." 'has been temporarily deactivated."
ActiveCell.Offset(1, 1).FormulaR1C1 = " Run the report manually
if a real time copy is needed."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time
End If

'Call macJAVATrack

Range("C35").Select
ActiveCell.FormulaR1C1 = "8."
ActiveCell.Offset(0, 1).FormulaR1C1 = " The AVATrack Report has been
temporarily deactivated." 'ran successfully."
'ActiveCell.Offset(1, 1).FormulaR1C1 = " A printed report(2
copies)is on the printer."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time

If Weekday(Now()) = 2 Then '1 = Sunday, 2 = Monday, 3 = Tuesday, ect

Call macKWklyPORpt

Range("C39").Select
ActiveCell.FormulaR1C1 = "9."
ActiveCell.Offset(0, 1).FormulaR1C1 = " The Weekly PO Create
Report ran successfully."
ActiveCell.Offset(1, 1).FormulaR1C1 = " A copy was e-Mail to
Patty."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time
Else
Range("C39").Select
ActiveCell.FormulaR1C1 = "9."
ActiveCell.Offset(0, 1).FormulaR1C1 = " The Weekly PO Create
Report did not run. Today is not Monday."
ActiveCell.Offset(1, 1).FormulaR1C1 = " Run the report manually
if a real time copy is needed."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time
End If

Call macLDolByLocII

Range("C42").Select
ActiveCell.FormulaR1C1 = "10."
ActiveCell.Offset(0, 1).FormulaR1C1 = "DolByLocII ran successfully."
ActiveCell.Offset(1, 1).FormulaR1C1 = " A printed report(2 copies)is
on the printer."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time

Call macRawStockTitles

Range("C45").Select
ActiveCell.FormulaR1C1 = "11."
ActiveCell.Offset(0, 1).FormulaR1C1 = "Raw Material Locs and Qtys
ran successfully."
ActiveCell.Offset(0, -1).FormulaR1C1 = Time
' this report is obsolete. removed 3/12/04
' If Weekday(Now()) = 2 Then '1 = Sunday, 2 = Monday, 3 = Tuesday,
ect
' Call macYasudaRpt
' ActiveCell.Offset(0, 1).FormulaR1C1 = " The Items Received
Report has been temporarily deactivated." 'ran successfully."
' ActiveCell.Offset(1, 1).FormulaR1C1 = " A printed report is on
the printer."
' ActiveCell.Offset(0, -1).FormulaR1C1 = Time
' Else
' Range("C42").Select
' ActiveCell.FormulaR1C1 = "10."
' ActiveCell.Offset(0, 1).FormulaR1C1 = " The Items Received
Report did not run. Today is not Monday."
' ActiveCell.Offset(1, 1).FormulaR1C1 = " Run the report manually
if a real time copy is needed."
' ActiveCell.Offset(0, -1).FormulaR1C1 = Time
' End If

Call macformats 'formats MRMacro summary sheet
'Call MacroPrint

Range("A1").Select
Range("A1").Value = Date
ActiveWorkbook.Save

End If

End Sub

regards
FSt1
 
Back
Top