How can I have a workbook delete itself?

[bersoy]

I am a VBA programmer.
I have a challenge thatI have not been able to solve yet. I need to update an excel file with the newest version if current one is not the newest version. I have already handled everything necessary to check the versions etc.
Suppose that I have two files, with exactly the same VBA code in them. Only filenames are different: MyFile.xls and MyFileNEW.xls

When I double click on MyFile.xls, I need it to close AND DELETE ITSELF, then rename MyFileNEW.xls as MyFile.xls, then open newly renamed MyFile.xls

There is no event like "after workbook close".
I used FileSystemObject and tried this: On close event of MyFile.xls, code opens MyFileNEW.xls.
On activate or open of MyFileNEW.xls, it tries to close MyFile.xls then delete MyFile.xls on the next line. THE PROBLEM IS: MyFileNEW.xls closes MyFile.xls, it just stops executing further. It does not go to the next line where I want to delete MyFile.xls. I think this is because activation of MyFileNEW.xls was already trigered by MyFile.xls, that is why it just exits after it closes MyFile.xls and does not go to the next line in MyFileNEW.xls activation code.

PLEASE PLEASE PLEASE HELP!!!

 

[Samiran]

Sub Suicide()
Dim FName As String
Dim Ndx As Integer
With ThisWorkbook
.Save
For Ndx = 1 To Application.RecentFiles.Count
If Application.RecentFiles(Ndx).Path = .FullName Then
Application.RecentFiles(Ndx).Delete
Exit For
End If
Next Ndx
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close SaveChanges:=False
End With
End Sub

 

[FaneDuru]

I think it is impossible to do what you want.
If you call a macro from an other file this macro stops execution when the initial file is closed even if there are other lines to execute.
I tried to write in the Autpen sequence a VBScript beginning with "WScript.Sleep 5000", thoughting that after activating WScript.exe will run sepparately but Excel waits for finishing the script execution ?!?!
Even if you would find a way to hoax Excel, I'm afraid that when you try to open the file after replacing it, all the cicle will repet for ever ?!?!
You may use a script able to do what you wont:

Set fso = CreateObject("scripting.filesystemobject&quot
Path = "D:\My Documents\"
Killed ="MyFile.xls"
Set Good = fso.GetFile(Path&"MyFileNEW.xls&quot
fso.DeleteFile(Path&Killed)
Good.Copy(Path&Killed)
Set shl = CreateObject("Shell.Application&quot
shl.Open Path&Killed

Running the script you will obtain the result you want ?!?!

 

[FaneDuru]

Me again
I found a compromis :

Private Sub Workbook_Open()
If ThisWorkbook.Name = "MyFile.xls" Then
Workbooks.Open (ThisWorkbook.Path & "\MyFileNEW.xls&quot
Workbooks("MyFileNEW.xls&quot.Activate
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
If ThisWorkbook.Name = "MyFileNEW.xls" Then
Application.DisplayAlerts = False
Workbooks("MyFile.xls&quot.Close
ThisWorkbook.SaveAs ThisWorkbook.Path & "/MyFile.xls"
End If
End Sub

All the job will be done at the first click. ?!?!