Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.


"BillCPA" wrote:

Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.

"BillCPA" wrote:

Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

That's awesome. How did you discover that?

--
Bill @ UAMS


"Tim Zych" wrote:

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.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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!

--
Bill @ UAMS


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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 (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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)?


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

"Rick Rothstein" wrote:

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 (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that
it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

Combination of a macro I wrote to do just this and what I have read online.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"BillCPA" <Bill @ UAMS wrote in message
...
That's awesome. How did you discover that?

--
Bill @ UAMS


"Tim Zych" wrote:

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.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

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 (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS

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

--
Bill @ UAMS


"Rick Rothstein" wrote:

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 (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
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.

--
Bill @ UAMS


"Rick Rothstein" wrote:

Just for my own curiosity, what function did the workbook perform that
it
can be deleted from existence when it is closed?

--
Rick (MVP - Excel)


"BillCPA" <Bill @ UAMS wrote in message
...
Is it possible for a workbook to delete itself when it closes?

--
Bill @ UAMS