Deleting or renaming an active file

[dflak]

I'm am looking for a way to make a file commit suicide.

I have a requirement to process a file.

When certain cells are filled in, the file is said to be complete. What the user wants to do is rename the file and put it in another directory and remove the original file.

Copying the file to the directory with the new name is not an issue. The problem is getting rid of the file that's still running.

I'm fairly certain that this is impossible without invoking another workbook that saves the file that calls it, closes that file and then moves it.

Here is the appropriate part of the code. SavePath and SaveFile are defined.

If DeleteFlag = True Then
    ThisWorkbook.Save
    Savefile = Replace(ThisWorkbook.Name, ".xlsb", "")
    Savefile = Savefile & " Complete.xlsb"
    ' Get rid of the copy to file if it exists.
    If Dir(SavePath & "\" & Savefile) <> "" Then
        Kill SavePath & "\" & Savefile
    End If
    FileCopy ThisWorkbook.Path & "\" & ThisWorkbook.Name, SavePath & "\" & Savefile
    Kill ThisWorkbook.Path & "\" & ThisWorkbook.Name
End I

The code doesn't like the Kill statement.

[dflak]

I added the word "suicide" to describe what I needed the workbook to do. Apparently that was the magic word for Google. I found this and it works.

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

This wipes out the workbook completely. It bypasses the Recycle Bin. I have a lot of copies of my test file.

[k64]

Well, that is simpler than what I came up with. Here is what I got though in case it fits your needs better. First run "addref" to add the required reference.

Sub addref()
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=3
On Error GoTo 0
End Sub

Sub deleteme()
Path = ThisWorkbook.FullName
With Workbooks.Add
    With .VBProject.VBComponents.Add(vbext_ct_StdModule)
        .CodeModule.AddFromString "Sub deleteit()" & vbNewLine & _
            "Kill """ & Path & """" & vbNewLine & _
            "Thisworkbook.close false" & vbNewLine & _
            "End sub"
    End With
    Application.OnTime Now + 2 / 86400, .FullName & "!deleteit"
    ThisWorkbook.Close False
    .Close False
End With
End Sub

[dflak]

You are using VB code to write VB Code? I could really get dangerous with this. (Insert maniacal laugh here).

I'll stick with what I have, but I am keeping this one for a future event.

[k64]

Yes. Because of the possible danger, you have to have "Trust access to the VBA project object module" checked in Macro Security. I have put some "surprises" in workbooks that I have coded for my brother using this technique