Solved: Delete The File Itself

[sheeeng]

Hi

Can a VBA macro execute to delete its own Excel file and then clear the command from the memory?

Just to find out whether this is possible...

Thx in advance.

[Norie]

As far as I know No.

Try it

Kill ThisWorkbook.Path & "\" & ThisWorkbook.Name

[sheeeng]

Sorry, the code does not work...

Error msg attached...

Please help. Thx.

[Norie]

I know the code doesn't work, that's what I'm saying it isn't possible for a file to delete itself via VBA.

[Justinlabenne]

From Tom Urtis:

[VBA]Option Explicit

Sub SuicideSub()
MsgBox "Later!"
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub[/VBA]

[sheeeng]

Great.! Almost what I need...But can it close also the excel application...?

Thx.

[Justinlabenne]

[VBA]Option Explicit

Sub SuicideSub()
' Original code from Tom Urtis

MsgBox "I'm gonna do it!!!"

With ThisWorkbook

.Saved = True

.ChangeFileAccess xlReadOnly

Kill .FullName

Application.Quit

End With

End Sub[/VBA]

[brettdj]

Weird

I submitted virtually the same code a couple of hours ago to the KB

[Justinlabenne]

You mean this one don't you:


kb Entry

[sheeeng]

Aiyo...How come ar...I thought of combining this for one KB...Great that you have did it before me...

[brettdj]

No, not the data table one, the suicidal workbook. Its still in the approval process

I saw the suicidal workbook cose around one year ago at Experts - Exchange and I'd bookmarked it, today I finally got around to submitting it and then I saw this thread. Did Tom Urtis actually claim authorship of this code or did you see him use it and then assign the credit it to him?

Cheers

Dave

[Justinlabenne]

He has it in the book "VBA and Macros for Microsoft Excel" by Bill Jelen and Tracy Syrstad.

It's on page 301, and it does look a bit different, it is set to delete itself after a specific date, but it only about one line different. I have used various incarnations of this same code, like this:
[VBA]
Option Explicit

Private Sub Workbook_Open()
Dim n As String, x As String
n = "C:\Me.xls" 'Right here, put the file path where file should be located

If ThisWorkbook.FullName <> n Then

x = MsgBox("Re-Named files will self destruct", vbCritical, "Deleting File!!!")
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
Application.Quit
End With
Exit Sub
End If
End Sub[/VBA]


Which is pretty effective at keeping people from copy and moving files to other locations, so long as macros are enabled,

I just always give him credit, since I have learned loads from him.

ps: if the attachment isn't stored on the C:\ drive with the same name it given, it will prompt then die.

[brettdj]

Thanks for the info.

I was concerned that I may have inadvertently lifted someone elses code, but the unattributed example I saw pre-dated the book. Much as yourself, If I do use code that had come from a website or can be credited to an individual I will list their name in the code (ie Jon Peltier laid much of the groundwork for exporting from Excel to PowerPoint)

Cheers

Dave

[sheeeng]

Thanks for all your help!
There goes one of my intented KB..

[sheeeng]

[VBA]Option Explicit

Sub SuicideSub()
' Original code from Tom Urtis

MsgBox "I'm gonna do it!!!"

With ThisWorkbook

.Saved = True

.ChangeFileAccess xlReadOnly

Kill .FullName

Application.Quit

End With

End Sub[/VBA]

Great!
Another marked solved.

[brettdj]

For completeness here is Suat's solution to the same problem. Code to be appreciated.

[vba]
'----Code Start---
Sub SelfDel()
Dim tmpcode As String
Dim wrk As Workbook
Dim vbcomp As Object
tmpcode = tmpcode & "Sub FinishProcess(wrk As Workbook)" & vbCrLf
tmpcode = tmpcode & " ThisWorkbook.Worksheets(1).Cells(1, 1).Value = wrk.FullName" & vbCrLf
tmpcode = tmpcode & " Application.OnTime Now + TimeValue(""00:00:01""), ""KillerCode""" & vbCrLf
tmpcode = tmpcode & " wrk.Close False" & vbCrLf
tmpcode = tmpcode & "End Sub" & vbCrLf
tmpcode = tmpcode & "Sub KillerCode()" & vbCrLf
tmpcode = tmpcode & " Kill ThisWorkbook.Worksheets(1).Cells(1, 1).Value" & vbCrLf
tmpcode = tmpcode & " ThisWorkbook.Saved = True" & vbCrLf
tmpcode = tmpcode & " ThisWorkbook.Close False" & vbCrLf
tmpcode = tmpcode & "End Sub" & vbCrLf
Set wrk = Workbooks.Add
Set vbcomp = wrk.VBProject.VBComponents.Add(1)
vbcomp.CodeModule.AddFromString tmpcode
vbcomp.Name = "Killer"
Application.Run wrk.Name & "!Killer.FinishProcess", ThisWorkbook
End Sub
'----Code End----
[/vba]

Cheers

Dave

[LVRebels]

So what is the code to creat a self-destructing file after a certain time? I'm not in your league guys, just an excel user.