kill a excel file from the file itself (resolved)

[Goh]

is it possible to kill a excel file upon a click button on the excel form itself?

[BrianB]

Yes. You have to assign a macro to the button.
The code statement is Kill.

[Goh]

i cant kill the file from the file itself even with marcos...it says access denied...

[RobDog888]

If you are trying to delete the Excel file itself then the answer is
NO, not from within the same workbook.

You would need to have your macro create another workbook
with code in it to kill the original workbook.
Add this code to a workbook called "KillMe.xls".
It will save itself as Main.xls and then it deletes the KillMe.xls workbook.

Private Sub Workbook_Open()
    ActiveWorkbook.SaveAs "Main.xls"
    Kill Mid$(ActiveWorkbook.FullName, 1, InStrRev(ActiveWorkbook.FullName, "\")) & "KillMe.xls"
End Sub

[Goh]

how abt the Main.xls? it still exist rite? even if you close it...

[medison]

This routine will create a new workbook that never gets saved. The orginal file will be deleted once the new workbook is closed. In order for this to run, a reference needs to be set to "Microsoft Visual Basic Extensibility 6.0" (with Excel 2000, should be similar, if not the same, for other versions).

Be careful testing this, so that you don't delete a file you are not ready to lose.


HTH

Sub KillMe()
    Dim tempWB As Workbook
    
    Set tempWB = Workbooks.Add
    
    With tempWB.VBProject.VBComponents("ThisWorkbook").CodeModule
        .InsertLines .CountOfLines + 1, _
            "Private Sub Workbook_BeforeSave(" & _
            "ByVal SaveAsUI As Boolean, Cancel As Boolean)" & vbCrLf & _
                "Cancel=True" & vbCrLf & _
            "End Sub" & vbCrLf & vbCrLf & _
            "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & vbCrLf & _
                "ThisWorkbook.Saved=True" & vbCrLf & _
                "Kill """ & ThisWorkbook.FullName & """" & vbCrLf & _
            "End Sub"
    End With
    
    Set tempWB = Nothing
 
    ThisWorkbook.Close SaveChanges:=False
End Sub

[RobDog888]

Yes, the Main.xls workbook will stilll be there.

medison, I get an application error 1004 on the with line of code.

[Goh]

so this code cant work?

[RobDog888]

medison, I figure out part of the issue. The checkbox for trusting
access to VB Projects needs to be checked in the tools > macros
> security > Trusted sources tab.

After that, I am now getting to the line that adds the code to the
other vba project and then it crashes Excel.

[medison]

How are you running the code? Are you trying to step through it? If so, try running the code from Excel instead of from the VB Editor.

The code works fine on my computer (WinXP) with both Excel 97 & 2000 when I run it from Excel (Tools - Macro - Macros. . .).

I have had trouble in the past when trying to step through code like this. I had never looked into the causes though. So, I did a little searching and learned a few things:

The reference to "Microsoft Visual Basic Extensibility 6.0" is only necessary when declaring variables of type VBProject, VBComponent, etc. Since the example does not do this, the reference can be removed.

The best information that I found, Chip Pearson's site, recommends setting the reference to "Microsoft Visual Basic For Applications Extensibility" instead.

Chip's site also mentions putting a check next to "Trust Access To Visual Basic Project" for newer versions of Excel.

About half-way down the page, in the section about adding a procedure, it mentions using "Application.Run" to call procedures that add code to different modules. I would guess that this is done to avoid similar problems.

The CodeModule object has a CreateEventProc method which could be used to shorten the code a little.

Try running the example from Excel and using the Application.Run method. If that does not work, I guess we'll keep looking.

[RobDog888]

Originally posted by RobDog888
The checkbox for trusting access to VB Projects needs to be
checked in the tools > macros > security > Trusted sources tab.

Yes, I was stepping through the code - Excel's VBA Editor.
I will try running from Tools > macros. etc.