Need VBA help to refresh and save worksheet.

April 22, 2012 12:37 PM

Have Excel 2007. I would like to have a spreadsheet perform the following simple? tasks

* At 6:30 AM run a Macro
* At 6:31 AM run another Marco
* After 6:31 Macro is done running, Save the Worksheet as xxxx. YYYYMMDDhhmmss
* After the worksheet saves, I want cell C2 to list Date and time of last saved.

I think I have most of the code already, you can view it here in pastebin.

Right now its all separate components. I'm not sure how to make it flow. Any VBA magicians out there that can help solve my riddle?

Thanks!

posted by bleucube to computers & internet (3 answers total)

It's been several years since I've done VBA programming in Excel, so rather than try to answer your question directly, let me suggest that you also post this question to this Google Groups, which is devoted to all manner of Excel-related programming. I've had a lot of luck over the years with this group.
posted by dfriedman at 1:26 PM on April 22, 2012 [1 favorite]

First setup the macros as AutoStart Macros, so they run as soon as the workbook is opened.

Then setup a pair of scheduled tasks in the windows control panel to run at 6:30 and 6:31 have them open excel and the relevant workbook, make sure the macros save and exit everything when done.

To save the date/time, add this just before the save command:
Cells(C2) = Date + Time
posted by Lanark at 3:14 PM on April 22, 2012 [2 favorites]

Ok figured it out.

Here's the answer:

1) At 6:30 AM, everyday – Run the Roll Regional Load module and the Refresh Regional Load module
2) Once modules have ran, Save the entire workbook to a network location with name Regional_Load_Outlook_Day- MM_DD_YY.xlsm
3) Update C2 on Regional Loads worksheet with the Lasted time it was saved.

Here’s the code:

In ThisWorkbook
Private Sub Workbook_Open()
'
'Run Refresh macro at 6:30 AM
'
Application.Wait Now + TimeValue("00:00:01")
Application.OnTime TimeValue("06:30:00"), "RunEverything"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'
'Update cell C2 with last save date and time
'
Sheets("Regional Loads").Range("C2").Value = Format(Now(), "mm-dd-yy hh:mm")
End Sub

In a New Module (I will use Module 2 as an example)
Sub SaveAs()
'
'Save entire workout as Regional_Load_Outlook_Day.MM_DD_YY.xlsm
'
ActiveWorkbook.SaveAs Filename:="C:\Regional_Load_Outlook_Day-" & _
Format(Now(), "MM_DD_YY") & ".xlsm"
End Sub

Sub RunEverything()
'
' Run at 6:30, Refresh stats, and save as new file
'
Application.OnTime TimeValue("06:30:00"), "RunEverything"
RollRegionalLoad
RefreshRegionalLoad
SaveAs
End Sub
posted by bleucube at 6:47 AM on April 26, 2012