Running a Macro when a Workbook is Closed

Thanks

Good day, can someone help me? I'm using Excel 2010. I want to print a report every morning 6AM. Currently this report print only once after opening it. The next day this report don't want to print.
Private Sub Workbook_Open()

Select Case Weekday(Date)
Case 1, 2, 3, 4, 5, 6, 7

Application.OnTime TimeValue("06:00:00"), "PRINT_REPORT"

End Select
Application.OnTime ("05:50:00"), "Workbook_Open"

End Sub


Sub PRINT_REPORT()
'
' PRINT_REPORT

ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

My macro checks for the excel version installed on workbook_Open(). if it is a lower version than excel 2010 then I am calling Workbook.close to close the excel file. But it is closing the Excel application and restarting by itself.

I just want to close my excel workbook alone and to leave the excel application opened.

Looking forward to get help on this.
Thanks

Hi,
I have the following macro.

Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("B5:I25").Select
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Today = Format(Now(), "dd-mm-yyyy")

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Hi Team," & vbNewLine & "Please find the below status of HPQC Defect we raised as on date " & Today & vbNewLine & vbNewLine & "With regards," & vbNewLine & "NADESAN MM" & vbNewLine & vbNewLine & "Note:This is a macro genarated mail"
.Item.To = "nadesan.mm@unilever.com"
.Item.Subject = "HPQC Defect Status |Clarity ID:2007320| AC UAT | As on " & Today
.Item.display
End With

End Sub

The above macro should run at a specified time everyday, even the work book is closed or opened.

could you please help on this.

regards.
NADESAN MM

@habib

Something like this:




Private Sub Workbook_Open()
Dim d As Date
Dim f As Date

d = DateValue(Now)
f = DateValue("20/10/2015")

If (f - d) > 10 Then Call KillMyFile
End Sub


Sub KillMyFile()
On Error GoTo ErrorHandler
With ActiveWorkbook
If .Path <> "" Then
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
Application.Quit
End If
End With
Exit Sub

ErrorHandler:
MsgBox "Fail to delete file: " & ActiveWorkbook.FullName
End Sub

(macro up there from http://www.excelforum.com/excel-programming-vba-macros/561546-delete-kill-active-workbook-on-close-possible.html )




Paste this code in your Workbook code zone for the file you want to delete and set "f" as you see fit. When someone opens the book it'll disappear.

However, you should know that holding SHIFT disables any automatic macro execution, so if you want to use this as a security measure (or anything VBA-based, for that matter) it won't work.

Also careful where you paste this, as you won't be able to recover what you delete.

hi,
I like to know that how can i make an excel files for others which it is erase after for example 10 days via a macro.

Oh, I thought that this was going to be about code that tries to run a macro that is in another workbook, but that other workbook is closed.

Indeed, in Excel 2010 or later, there is a new event:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

If Success Then
MsgBox ("The workbook was successfully saved.")
End If

End Sub

The Auto_Close macro is a relic of old Excel versions. This is what Help (Excel 2007) says:

"Workbook.RunAutoMacros Method
Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros."

In this case better use one of these events in the ThisWorkbook code page:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'code goes here
End Sub

or

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'code goes here
End Sub