Can A Workbook Delete Itself

  • Thread starter Thread starter BillCPA
  • Start date Start date
No, because you can't delete a file in windows when it is opened.

You may want to put the macro in a seperate workbook from the data so you
can delete the data workbook and not delete the macro which is running.
 
Sure.

In the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With ThisWorkbook
On Error Resume Next
.ChangeFileAccess xlReadOnly
Kill .FullName
On Error GoTo 0
.Close False
End With
End Sub

you can add better code which can check for the existence of the workbook,
rather than using On Error Resume Next, but this is the basic idea.
 
I've seen a workbook disappear when I had an excel crash multiple times
during execution, reopened the file and didn't do anything with the recovered
version. At some point, all I had was a recovered version that I had to
search for.
 
Here is a macro I have in my personal.xls assigned to a custom button on the
toolbar
It DOES ask before deleting.

Sub KillActiveWorkbook()
With ActiveWorkbook
mb = .Name
..Close
End With

MyAnswer = MsgBox("Do you want to KILL this file?", vbYesNo)
If MyAnswer = vbYes Then Kill mb

End Sub
 
Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?
 
That works also - actually it also 'Quits' Excel, which is what I preferred.

In my particular situation, I did have to change the file access to
'xlReadOnly' and use the '.FullName' parameter.

Thanks!
 
I'm not sure what exactly you are asking.

I execute 'Application.Quit' from a menu Exit button, which I assume sets a
process in motion to close all files and exit Excel, no matter whether the
current workbook still exists or not.
 
You said you wanted to "delete" the workbook when you close it. To me,
delete means remove it from the hard drive so it no longer exists. The code
Tim gave you (which you appeared to say does what you want) uses the Kill
function which does exactly that... it removes the file from the hard drive.
My question is this... what useful thing is the workbook doing that you only
need it once and never again (remember, it is deleted after being used one
time)?
 
Rick,
My question is this... what useful thing is the workbook doing that you only
need it once and never again (remember, it is deleted after being used one
time)?
Click to expand...


A valid question. I came across this several years ago with the code
triggered by a date in the workbook_open event, where a workbook written by a
disaffected (ex) employee committed suicide when that date arrived.
Unfortunately for that ex employee the workbook was backed up each night so
when started with macros disabled it was easy to delete the rogue code.

Mike
 
I can imagine deleting a file after only one use. What intrigues me is why
the OP is using the file that the code is being run from to do the delete.
 
I have a 'Base' file that I start this particular process with - lots of
macros and formulas. I pull in a couple of additional files, do some data
manipulation - delete rows, insert columns, add worksheets, etc. - and then
write out a new file with info that goes into the general ledger.

I want the 'Base' file to be the same each month when I start the
processing, so once I get into the process, I save the 'Base' file with a
different name. Once I have created my output file for the GL, I no longer
need the file I'm working from, so I want to delete it. I used to save it in
case I needed to reference it, but never needed to, so now I just delete it.
If I did need it, I can always re-run the process - it only takes fifteen
seconds or so.
 
While I haven't played with these much myself, it sounds like you could save
your "base file" as a Template and then just use it when you needed it (each
month)... when you Save the file and then close it, the base file would
remain unchanged and your data would be in the file you saved out to. Maybe
you would want to test this concept out on a sample "base file" and see it
this works for you.

--
Rick (MVP - Excel)


BillCPA said:
I have a 'Base' file that I start this particular process with - lots of
macros and formulas. I pull in a couple of additional files, do some data
manipulation - delete rows, insert columns, add worksheets, etc. - and
then
write out a new file with info that goes into the general ledger.

I want the 'Base' file to be the same each month when I start the
processing, so once I get into the process, I save the 'Base' file with a
different name. Once I have created my output file for the GL, I no
longer
need the file I'm working from, so I want to delete it. I used to save it
in
case I needed to reference it, but never needed to, so now I just delete
it.
If I did need it, I can always re-run the process - it only takes fifteen
seconds or so.
Click to expand...
 
Back
Top