Backup File

wichien · Mar 20th, 2002, 08:16 PM

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 · Mar 20th, 2002, 08:37 PM

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 · Mar 20th, 2002, 08:38 PM

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 · Mar 20th, 2002, 08:39 PM

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

Jay Petrulis · Mar 20th, 2002, 08:39 PM

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 · Mar 20th, 2002, 09:19 PM

That's what I want !. Thanks so much

BabyTiger and Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 08:16 PM	#1
wichien New Member Join Date: Mar 2002 Posts: 11	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.

Mar 20th, 2002, 08:37 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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.

Mar 20th, 2002, 08:38 PM	#3
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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.

Mar 20th, 2002, 08:39 PM	#4
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	One typo I have picked up. Instead of "parth", it should be "Path".

Mar 20th, 2002, 08:39 PM	#5
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Mar 20th, 2002, 09:19 PM	#6
wichien New Member Join Date: Mar 2002 Posts: 11	That's what I want !. Thanks so much BabyTiger and Jay