×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How can I have a workbook delete itself?

How can I have a workbook delete itself?

How can I have a workbook delete itself?

(OP)
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!!!

RE: How can I have a workbook delete itself?

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

RE: How can I have a workbook delete itself?

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 Auto_Open  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")
  Path = "D:\My Documents\"
  Killed ="MyFile.xls"
  Set Good = fso.GetFile(Path&"MyFileNEW.xls")
  fso.DeleteFile(Path&Killed)
  Good.Copy(Path&Killed)
  Set shl = CreateObject("Shell.Application")
  shl.Open Path&Killed

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

RE: How can I have a workbook delete itself?

Me again
I found a compromis :

Private Sub Workbook_Open()
If ThisWorkbook.Name = "MyFile.xls" Then
    Workbooks.Open (ThisWorkbook.Path & "\MyFileNEW.xls")
    Workbooks("MyFileNEW.xls").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").Close
   ThisWorkbook.SaveAs ThisWorkbook.Path & "/MyFile.xls"
 End If
End Sub

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources