VBA Backup deletes original

[nparsons75]

Hi all,

I am using some code which I was given to create a backup of a file I use regularly. Intermittently when the backup takes place, the file is backed up but the original file disappears. Its very odd.

The code is as follows, hopefully someone can see why it happens. Thanks in advance.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)If Not Success Then Exit Sub
Const backupFolder = "C:\MAIN FILE\BACKUPS"


Dim savedName As String
Dim backupName As String
Dim dotFinder As Long


Application.EnableEvents = False
savedName = ThisWorkbook.FullName
backupName = backupFolder & Mid$(savedName, InStrRev(savedName, "\"))
dotFinder = InStrRev(backupName, ".")
backupName = Left$(backupName, dotFinder) & Format$(Now(), "yyyymmddhhnnss.") & Mid$(backupName, dotFinder + 1)
If backupName = savedName Then Exit Sub
If Dir$(backupName) <> "" Then Kill backupName
ThisWorkbook.SaveAs backupName
Kill savedName
ThisWorkbook.SaveAs savedName
Application.EnableEvents = True


End Sub

 

[stumac]

This line is deleting the original workbook.

Kill savedName

 

[nparsons75]

If I remove that will it effect it in any other way? It looks like it deletes the original and then is supposed to save a new one, it looks like it kills the old one to avoid the message... A file named.....already exists, do you want to replace it!!

 

[stumac]

No it shouldn't affect it in anyway.... try commenting out the 2 lines:

Kill savedName
ThisWorkbook.SaveAs savedName

there is no point in saving the file again as you haven't made any changes since running the macro

 

[nparsons75]

That stops the message and saves a backup, however it does not actually save the file now. How odd....

 

[stumac]

Your code is running on the AfterSave event - if it doesn't save then the code doesn't run.

 

[nparsons75]

Ah, I see, so what would prevent it saving? most of the time it does, sometimes it must fail as the file disappears?