VBA TIPS
Suicide - kill this workbook from HD

With Kill Statement you can delete files from a disk, but it does not work for ThisWorkbook. Because ThisWork book has been loaded on the memory of the machine, it means it still alive. Kill Statement cannot kill running files.

Here are two way of thinking. Both of the following examples would make a workbook delete. The first one writes a Module then makes a new workbook and import the written module. The module works for deleting ThisWorkbook.

The second one changes the file access mode so that This workbook will be opened as ReadOnly.

Place following in the standard module.

Option Explicit

Sub Suicide1()
   Dim objNB As Object
    Set objNB = Workbooks.Add
    With ThisWorkbook
        Open .Path & "\xx.bas" For Output As #1
        Print #1, "Sub Temp"
        Print #1, "Workbooks(" & """" & .Name & """" & ").Close False"
        Print #1, "Kill " & """" & .Path & "\" & .Name & """"
        Print #1, "Kill " & """" & .Path & "\xx.bas" & """"
        Print #1, "ThisWorkbook.Close False"
        Print #1, "End Sub"
        Close #1
    objNB.VBProject.VBComponents.Import Filename:=.Path & "\xx.bas"
    End With
    Application.OnTime Now(), objNB.Name & "!Temp"
End Sub


Sub Suicide2()
    With ThisWorkbook
        .Save
        .ChangeFileAccess Mode:=xlReadOnly
        Kill .FullName
        .Close SaveChanges:=False
    End With
End Sub

| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo