CODEWITHSUNDEEP
excelvbatimer
Ryan Gillooly
Ryan Gillooly

Reputation: 315

Delay Excel save function

I have a workbook which requires to be saved at the beginning then auto saving every 5 mins, but i need to delay the initial save so that when the workbook opens it waits 30secs then does the save.

This is the code i have, but it runs it automatically:

Private Sub Workbook_Open()
Time = Now() + TimeValue("00:00:30")
Application.OnTime Time, "WaitUntilReady"

Public Sub WaitUntilReady()

savefolder = "C:\Users\" & Environ$("Username") & "\Desktop\"

mypath = savefolder & Format(Date, "dd-mmm-yy")

If Len(Dir(mypath, vbDirectory)) = 0 Then MkDir mypath

On Error Resume Next

ThisWorkbook.SaveAs mypath & "\" & "Practice Monitoring Template" & " - " & Format(Time, "hh.nn") & ".xlsm"

Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 481

Answers (2)

KekuSemau
KekuSemau

Reputation: 6856

Time is an ingegrated function. If you do not declare it as a variable, the line Time = ... should throw an error. If you have declared it, it should actually run fine (not immediately) - it does in my tests. (Of course you should rather change the variable name).

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Time is reserved word in Excel (it returns current time), use this one instead:

Private Sub Workbook_Open()
    Application.OnTime Now() + TimeValue("00:00:30"), "WaitUntilReady"
End Sub

Upvotes: 2

Related Questions