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
news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> 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
(E-Mail Removed)
"BillCPA" <Bill @ UAMS> wrote in message
news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> 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
> news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> > 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
news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> 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
> (E-Mail Removed)
> "BillCPA" <Bill @ UAMS> wrote in message
> news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> > 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
> news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
> > 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
news38667D3-B38F-4EB4-B797-(E-Mail Removed)...
> 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
>> news:E4AF7D35-2BDA-4E96-95B1-(E-Mail Removed)...
>> > Is it possible for a workbook to delete itself when it closes?
>> >
>> > --
>> > Bill @ UAMS
>>
>>