Workbook SelfDestruct

P

PhilDean

Is it possible to cause a workbook to delete all data contained on al
sheets including hidden sheets, if the work book is opened after
certain period of time?

Thanks

Phi
 
F

Frank Kabel

Hi
not really with a secure/safe way. All the user has to do is disable
macros and your procedure won't run. Also I don't think this would be a
good idea from a user point of view (destroying my data)
 
S

ste mac

Hi Phil, l can give you some bits of code you may be able to use:

This will delete the VBA in a workbook....

Option Explicit
Public Sub BinallVBAinwb()
Dim vbComp As Object
For Each vbComp In ActiveWorkbook.VBProject.VBComponents
With vbComp
If .Type = 100 Then
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
ActiveWorkbook.VBProject.VBComponents.Remove vbComp
End If
End With
Next vbComp
End Sub

This is a workbook 'expiry' piece of code...
Open this with macro's diabled.. otherwise you will not be able to change
the date...
Click to expand...
Option Explicit

Private Sub Workbook_Open()
'Public should be private when doing this for real
Dim myCutOff As Long
myCutOff = DateSerial(2002, 10, 6)

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

This macro will delete itself....(just module one.. once it has ran!)

Sub deletmodule1()

MsgBox "place your code here"

With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With
End Sub

hope these help..

seeya ste
 
I

icestationzbra

methinks, the OP wanted to delete all the data from the excel workboo
(as in data on the sheets), not the code (as in behind the sheets)
 
S

ste mac

icestationzbra
methinks, the OP wanted to delete all the data from the excel workbook
(as in data on the sheets), not the code (as in behind the sheets).
Click to expand...

Oops... my fault..<g>
 
P

PhilDean

icestationzbra

you are right in that I do want to destroy the data on the sheets not
the VB code.

stemac

Thanks for you help anyway

Phil
 
I

icestationzbra

just an improvement over what stemac has provided. this will erase al
the data in the workbook if it is opened after the cutoff date.

this goes into the ThisWorkbook code module.

*****

Private Sub Workbook_Open()

Dim myCutOff As Long

Dim s As Worksheet

myCutOff = DateSerial(2004, 8, 26)

Application.EnableEvents = False

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)

'clears sheets

For Each s In Me.Worksheets

s.Select
Cells.Select
Selection.ClearContents

Next s

'saves without prompting

ActiveWorkbook.Save

Application.EnableEvents = True

End If

End Su
 
Top