Auto-delete the workbook on expiry
 

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.

Auto-delete the workbook on expiry
 

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" wrote in message
...
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.

Auto-delete the workbook on expiry
 

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

--
Paul B

Auto-delete the workbook on expiry
 

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 wrote in message
...
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
the
book with macros disabled?

--
Paul B

Auto-delete the workbook on expiry
 

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" wrote in message
...
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
the
book with macros disabled?

--
Paul B

Auto-delete the workbook on expiry
 

Hi Paul

Thanks! I quite like the pausing idea- I'll give it a whirl

SuperJas.

Auto-delete the workbook on expiry
 

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.

Auto-delete the workbook on expiry
 

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 **
"SuperJas" wrote in message
...
Hi Paul,

Thanks! I quite like the pausing idea- I'll give it a whirl.

SuperJas.

Auto-delete the workbook on expiry
 

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?