execute macro; save it and close using Application.OnTime

[ina]

hello,

I would like to know how to save after than the macro is finished to
run; for example I have this piece of code:


......
dtmTime = Now + TimeValue("00:00:07")
Application.OnTime dtmTime, "thisworkbook.operations"


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\test.xls", FileFormat:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False
Application.DisplayAlerts = True
'and there how to determinate the exact time of the end of my macro; do
you think I have the
'possibility to catch it with something else than now ().
'my macro is a little slow as need to do a lot of calculation

dtmSave = dtmTime + TimeValue("00:01:00")
Application.OnTime dtmSave, "Save_Exit"

ina

 

[JonR]

Hello ina,

Here is the technique that I use, in three different subs. I use one sub to
set the Application.OnTime timer (which I activate with a control button)
that executes my 'master control' macro (which executes all of the other
macros in the proper sequence). The last macro I have the program execute is
the "Save" macro, which saves a copy in the same path as the original with
today's date appended to the file name. I've nto tried to do anything with
the time of the save, but I'm fairly sure that Now() would give you a usable
variable to add to the file name.

___________________________________________________________________

Sub Timer()

Application.OnTime ("7:55"), "MegaMacro"

' I put the MsgBox in because I use a control button to set the timer
' which runs the update macro.
' You can leave it out if this does not suit your purposes

MsgBox "Timer Set"

End Sub

_____________________________________________________________________

Sub MegaMacro ()

'*****************************************
' I use this macro as "control central" routing the
'spreadsheet to all subsequent macros in the right order
'******************************************

Save_This ' Execute this macro last - after all your updates

End Sub
_____________________________________________________________________

Sub Save_This ()

Day = DatePart("d", mydate)
Mon = DatePart("m", mydate)
Yr = DatePart("yyyy", mydate)

Path = ActiveWorkbook.Path

FileName = Path & "\My File " & Mon & "-" & Day & "-" & Yr & ".xls"

ActiveWorkbook.SaveAs (FileName)

End Sub

 

[ina]

Thank you very much for this help.