execute macro; save it and close using Application.OnTime

I

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top