About the easiest way would to either have a process on
the server that checks last modified dates on the file(s)
in question and "auto-destroy" them or to create a macro
that locks the file and closes it based on time of
day/current date.
The other requires that the security of Excel be set to
Low. (And we all know what can happen then... Can you
say "VIRUS!!!") Add to the Workbook_Open event, in the
ThisWorkbook module, the following example:
Private Sub Workbook_Open()
Dim deadDate As Date
' Define a "dead" date
deadDate = #3/1/2004#
Application.ScreenUpdating = False
With Application.ActiveWorkbook
If Now >= deadDate Or _
.Worksheets("DEAD").Cells(1, 1).Value = "DEAD" Then
' check if the active windows are already
' password protected
If .ProtectWindows = False Then
' Add a sheet to the workbook that will have
' the word "DEAD" in cell A:1 and is
' named "DEAD"
.Worksheets.Add
.ActiveSheet.Name = "DEAD"
.Worksheets("DEAD").Cells(1, 1).Value = "DEAD"
' this next statement will password protect
' the workbook. Use any tough string for the
' first parameter and skip the second parameter
' with another comma and go on to the third
' and set it to True to protect all windows
' in the workbook.
Application.ActiveWorkbook.Protect _
"OprTexQ3-*", , True
' turn off any announcement dialogs.
Application.DisplayAlerts = False
' save the workbook to lock the password
Application.ActiveWorkbook.SaveAs
Application.DisplayAlerts = True
End If
' Announce the reason for Excel shutting down
MsgBox "This spreadsheet is no longer active. " & _
"Excel will shut down now", vbExclamation
' don't announce the Save dialog box
Application.DisplayAlerts = False
' Quit Excel
Application.Quit
' Alternatively you can close the workbook.
'Application.ActiveWorkbook.Close
' Kinda pointless....
Application.DisplayAlerts = True
End If
Application.ScreenUpdating = True
End Sub
This method can have "work arounds" if some one so
desires. Such as the user knows the dead date. The code is
also exposed until the sheet is "locked" so anyone can see
the code and know the password and dead date. If a user
knows the dead date, they can then set the date on their
computer back to open it initially, but if it is opened
before after that date, then the file will definitely be
locked and almost useless. Anyway you look at this, the
file can be opened if security is set to Medium and the
user is prompted to enable macros. Say "No" and you can
open the file, edit it, whatever.
This also requires someone to open the file to
be "inactive".
There might other solutions out there, but this is the
only one I can think of.
Other than that, good luck!
-John Baughman
Fort Collins, CO
-----Original Message-----
Is there some way to embed a time limit in an Excel
document? So if someone went to my site and saved an
excel document, that document would auto destroy or auto-
protect (become uneditable) after a set amount of days?
