Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Macro to run each day?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Macro to run each day?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh Macro to run each day?
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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro doesn't refresh | Excel Programming | |||
How do I refresh a macro? | Excel Discussion (Misc queries) | |||
refresh macro | Excel Worksheet Functions | |||
Refresh macro | Excel Discussion (Misc queries) | |||
Timing of automatic query refresh and macro pivot table refresh | Excel Programming |