EXCEL VBA Code to Expire the worksheet and close and delete the file.

[jamilm]

Excel Gurus,

can anyone help with a simple code to expire a workbook within 2 days

can you please put the code where the i have put querstions makrs in the VBA code below? thanks.


Private Sub
Workbook_Open()

If ???????????????????????????????????
Then Application.Run "KillWs"

End Sub

Sub KillWs()

MsgBox "This Workbook is expired", vbExclamation + vbMsgBoxRight

Application.Quit

End Sub

 

[Michael M]

Maybe try

Private Sub Workbook_Open()
Dim Edate As Date
Edate = Format("31/12/2012", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate + 2 Then
MsgBox "This workbook is Expired and will now close !!!"
ActiveWorkbook.Close
End Sub

 

[jamilm]

thank you Michael. your code was very useful.

closing for If was missing and i added it and it worked perfectly. thanks.

Private Sub Workbook_Open()
Dim Edate As Date
Edate = Format("31/12/2012", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate + 2 Then
MsgBox "This workbook is Expired and will now close !!!"
ActiveWorkbook.Close
End If
End Sub

Maybe try

Private Sub Workbook_Open()
Dim Edate As Date
Edate = Format("31/12/2012", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate + 2 Then
MsgBox "This workbook is Expired and will now close !!!"
ActiveWorkbook.Close
End Sub

 

[Michael M]

Ok, sorry about the missing "END IF"
I didn't have Excel at the time...cheers

 

[jamilm]

Ok, sorry about the missing "END IF"
I didn't have Excel at the time...cheers

thanks your code helped me get what i need. cheers

 

[jamilm]

Michael,

would there be a possibility for such macro to be time specific? for example, i want my file than only could be opened from 8:00AM to 4:00PM Monday through Friday.

can you help?

thank you Michael. your code was very useful.

closing for If was missing and i added it and it worked perfectly. thanks.

Private Sub Workbook_Open()
Dim Edate As Date
Edate = Format("31/12/2012", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate + 2 Then
MsgBox "This workbook is Expired and will now close !!!"
ActiveWorkbook.Close
End If
End Sub

 

[sachin katariya]

Maybe try

Private Sub Workbook_Open()
Dim Edate As Date
Edate = Format("31/12/2012", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate + 2 Then
MsgBox "This workbook is Expired and will now close !!!"
ActiveWorkbook.Close
End Sub

What if I manually change date of my system ?