CODEWITHSUNDEEP
vbaexcel
Chris
Chris

Reputation: 13

Close a workbook at a specific time

I have looked everywhere to get an answer to this and can't figure it out. I'm looking for a way to close and save a workbook automatically at midnight every night because it has important information that people keep losing because they don't save it. I've got the closing and saving part down, if I manually click the X to close Excel, but I can't get it to do it at a predetermined time. I've used Application.OnTime without any success. Here is the code that I'm using to close and save the workbook. I just need the code to have it run at midnight if the workbook is still open.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Sheet1.Activate
  Sheet1.Range("AB18:AD18").Select
  Selection.ClearContents

  ActiveWorkbook.Save

End Sub

Upvotes: 0

Views: 1492

Answers (1)

user4039065
user4039065

Reputation:

I had trouble referencing the Worksheet .CodeName property but not with the Worksheet .Name property.

Module1 code sheet

Option Explicit

Sub ScheduleClose()
    Application.OnTime Now + TimeSerial(0, 0, 4), "Kamikaze"
End Sub

Sub Kamikaze()
    Debug.Print "boom"
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Worksheets("Sheet1").Activate
        .Worksheets("Sheet1").Range("AB18:AD18").ClearContents
        .Save
        .Close savechanges:=False
    End With
End Sub

Of course, the workbook must be already saved (i.e. not Book1) since no name is provided but a Workbook.SaveAs method could be used if the workbook habitually is not already saved.

This will also leave 'orphaned' VBA projects within the Excel application 'instance'. VBA cannot kill itself as it has to be running to complete the command.

Upvotes: 3

Related Questions