VBA to Close and Save Workbook on Timer

[richardtims]

Hello,

I found code in this forum to close my workbook after a certain amount of time, but when I use it, after closing the workbook, it wants to re-open it. Can anyone provide code that works or help me fix this one?

[COLOR=#333333]Public RunWhen As Double[/COLOR]
[COLOR=#333333]Public Const cRunIntervalSeconds = 300 ' this is 300 seconds or 5 Minutes[/COLOR]
[COLOR=#333333]Public Const cRunWhat = "The_Sub"[/COLOR]

[COLOR=#333333]Sub Auto_Open()[/COLOR]

[COLOR=#333333]RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)[/COLOR]
[COLOR=#333333]Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _[/COLOR]
[COLOR=#333333]schedule:=True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]


[COLOR=#333333]Sub The_Sub()[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]ActiveWorkbook.Save[/COLOR]
[COLOR=#333333]ThisWorkbook.Close savechanges:=True[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]Auto_Open[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[John_w]

Try deleting Autpen in The_Sub.

Also, stop the timer before the ThisWorkbook.Close - see http://www.cpearson.com/excel/OnTime.aspx

 

[richardtims]

After I posted this, I thought that maybe taking out the Autpen line would do the trick but it didn't. I looked at the article, but being somewhat new to VBA, I can't make out what lines of code I should use and where I should put it. Can you provide help with that piece? I really appreciate your help.

 

[richardtims]

I think I may have figured it out. I wanted to post the code here so others can use it for themselves. If there is no further reply, then we know it definitely works. Thanks for your help with this.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1800 ' this is 1800 seconds or 30 Minutes
Public Const cRunWhat = "The_Sub"


Sub Auto_Open()


RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True

End Sub

Sub The_Sub()


ActiveWorkbook.Save


 Call StopTimer


ThisWorkbook.Close savechanges:=True


End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub