Backup File

[wichien]

How Can I make Backup File with VBA Command (may be .tmp) ,Because sometime a Excel is Error . This BackupFile have to has the same PATH of Old File also . Thanks a lot.

 

[BabyTiger]

If Dir(ThisWorkbook.parth & "tempname.tmp") <> "" Then Kill (ThisWorkbook.parth & "tempname.tmp")
ActiveWorkbook.SaveCopyAs (ThisWorkbook.parth & "tempname.tmp")

The first is to delete the existing backup copy of the file.
Second line is the save a copy of the file to the same directory as the current file.

 

[BabyTiger]

Please note, for some reason, the forum converted my 1 slash into 2, so when you are copying the code, make sure you change any 2 slashes into 1.

 

[BabyTiger]

One typo I have picked up. Instead of "parth", it should be "Path".

 

[Jay Petrulis]

Hi Wichien,

No need for VBA to do this. When saving, use SaveAs and click the Options button. There is a checkbox "Always Create Backup" which will do what you want.

If you must do this through VBA, in the code module for ThisWorkbook try,

---begin VBA---
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Name, CreateBackup:=True
Application.DisplayAlerts = True
End Sub
---end VBA---

This should do what you want. Please repost if you need something different.

Thanks,
Jay

 

[wichien]

That's what I want !. Thanks so much BabyTiger and Jay