Auto-delete the workbook on expiry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm sending out a time-restricted Excel workbook for others to use, and I'd like the file to auto-delete itself if it's already expired. Is this possible to do in VBA? For example, if it's possible, the code could reside in the Workbook_Open event, where a msgbox will pop up to tell the user the file's expired (done), and then delete the file

Thanks heaps for your help

SuperJas.
 
Yes that can be done, but very easy to get around, what if they open the
book with macros disabled?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
SuperJas said:
Hi,

I'm sending out a time-restricted Excel workbook for others to use, and
Click to expand...
I'd like the file to auto-delete itself if it's already expired. Is this
possible to do in VBA? For example, if it's possible, the code could reside
in the Workbook_Open event, where a msgbox will pop up to tell the user the
file's expired (done), and then delete the file.
 
Hi Paul

That isn't a problem, as they wouldn't know that the macro would do that...until the file's disappeared. These recipients have elementary macro exposure

Could you please show me how to do it

Thanks

SuperJas

----- Paul B wrote: ----

Yes that can be done, but very easy to get around, what if they open th
book with macros disabled
 
Deleting a workbook without letting the user know that is a posibility would
be identical to infecting them with a virus.

Definitely would not make you any sales.

--
Regards,
Tom Ogilvy

SuperJas said:
Hi Paul,

That isn't a problem, as they wouldn't know that the macro would do
Click to expand...
that...until the file's disappeared. These recipients have elementary macro
exposure.
 
SuperJas, You are assuming that they don't have a backup copy of the file
that they can open with macros disabled, always back up your work!. I don't
think it would be a good idea to just kill the fill without them knowing
about it. How about something like this, it will give them a message box
when the fill is opened and if the date has passed it will delete it on
close. Or you could use the macro at the bottom, that I got off of this
newsgroup, to pause the sheet on open, will pause for 1 Minute for everyday
past expiration date. Again this and most things you can do for and
expiration date can be disabled, but maybe it will work for you. You can
also password protect the VBA to make it a little harder to get around, but
not much. Put this code in the thisworkbook code


Private Sub Workbook_Open()
MsgBox "This file will expire on 2-20-2004"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Date >= #2/20/2004# Then
ThisWorkbook.Saved = True
MsgBox "This file has expired, it will be deleted!"
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End If
End Sub

Or use this

Private Sub Workbook_Open()
'program will pause for 1 Minute for everyday past expiration date
'can also be used in before save, worksheet Activate,
'Worksheet_SelectionChange, etc
Dim myCutOff As Long
myCutOff = DateSerial(2004, 2, 20)
If Date > myCutOff Then
MsgBox "I'm sorry, this workbook should have expired on: " _
& Format(myCutOff, "mm/dd/yyyy") & vbLf & _
"After you dismiss this box, this program will pause for: "
_
& CLng(Date) - myCutOff & " Minutes!" & vbLf & vbLf & _
"One Minute for each day past expiration!"
Application.Wait TimeSerial(Hour(Now), Minute(Now) +
CLng(Date) - myCutOff, _
Second(Now))
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
SuperJas said:
Hi Paul,

That isn't a problem, as they wouldn't know that the macro would do
Click to expand...
that...until the file's disappeared. These recipients have elementary macro
exposure.
 
Hi Tom

Haha! =) No fear, the file's being sent out to internal staff for their use. We wish to delete the file so that we'll be sure that they use the most updated version available

Thanks

SuperJas.
 
SuperJas, if you like that and the time is to long, this one will pause for
1 second for everyday past expiration date

Private Sub Workbook_Open()
'program will pause for 1 second for everyday past expiration date
'can also be used in before save, worksheet Activate,
'Worksheet_SelectionChange, etc
Dim myCutOff As Long
myCutOff = DateSerial(2004, 2, 7)
If Date > myCutOff Then
MsgBox "I'm sorry, this workbook should have expired on: " _
& Format(myCutOff, "mm/dd/yyyy") & vbLf & _
"After you dismiss this box, this program will pause for: "
_
& CLng(Date) - myCutOff & " seconds!" & vbLf & vbLf & _
"One second for each day past expiration!"
Application.Wait TimeSerial(Hour(Now), Minute(Now), _
Second(Now) + CLng(Date) - myCutOff)
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Is there any way to make this expiration relative to the current date? For example if a user saves the file on Feb 27, I could give them 30 days to have the file?
 
Back
Top