You can do a lot with Microsoft Excel; real wizardry, I’d say. It’s without doubt one of the best applications Microsoft has ever built and its possibilities are huge. Today’s exercise proves it.
We’re going to create an Excel file, which could hold whatever data you like, with a macro that keeps checking the date to work out an expiry. When the deadline arrives, the file will show a warning and close. And if you want to go further, we can program the file to delete itself from the hard drive on the end date, without even leaving a trace in the recycle bin. How is that possible? Let’s see.
First, create a new Excel document and save it with any name, choosing Excel Macro-Enabled Workbook (.xlsm) as the file type.
With the file open, press Alt and F11 together to open the Microsoft Visual Basic for Applications editor. This is where we’ll write the macro.
On the left, select ThisWorkbook and enter this code:
Private Sub Workbook_Open()
Dim exdate As Date
exdate = "12/05/2014"
If Date > exdate Then
MsgBox ("This file has expired")
ActiveWorkbook.Close SaveChanges:=False
End If
MsgBox (exdate - Date & " days left")
End Sub
As you can see, the commands are very simple and easy to follow. The first line makes the macro run when the workbook opens. Then we define the exdate variable with the expiry date, and tell it that if today’s date is later than exdate it should show an “expired” message and close the workbook without saving. Otherwise, it shows another window with the number of days left until the expiry date.
Now save, close Excel and open the file again to check that it works.
Note: whoever opens the file will need to enable macros. To make sure they do, you can make it mandatory in order to see the file’s content.
If we’re still within the valid period, this message appears:
If the end date has passed (you can test it by moving the computer’s clock forward), we’ll see this message and the file will close:
And if we want to take it one step further, we can make the file delete itself from the hard drive without a trace once it has expired, as well as closing.
Open the file and edit the previous macro: now, if the date is past the expiry date, besides showing the message we’ll call a new macro, AutoDestruccio, which will Kill the file (delete it) and close it without saving:
Private Sub Workbook_Open()
Dim exdate As Date
exdate = "11/30/2014"
If Date > exdate Then
MsgBox ("This file has expired")
Call AutoDestruccio
End If
MsgBox (exdate - Date & " days left")
End Sub
Sub AutoDestruccio() On Error Resume Next Application.DisplayAlerts = False ChangeFileAccess xlReadOnly Kill ActiveWorkbook.FullName ActiveWorkbook.Close SaveChanges:=False On Error GoTo 0 End Sub
Now save, change the computer’s date and watch the file disappear from the hard drive with no way of recovering it.
Bear in mind this is a deterrent, not real security: if the person opening the file doesn’t enable macros, or keeps a copy, the expiry won’t apply.
Translated with the help of AI from the original article.