Modify Backup Copy path of Microsoft Excel

[Kumar esan]

When checking the setting in the Save As Options "always create backup", By default excel saves the backup copy to the same path as the original file. Can this backup file be saved to another path. If so, where can I make this change? File > Save AS > Tools > General Options > Always create backup.

[GALIENA]

Not as far as I know. Backups are saved in the Excel default file location. You could create a macro that will save the file at the default location and a copy to the folder of your choice. The macro could be run manually or in before save code in ThisWorkbook.

Sub BUandSave2()
'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="E:\GordStuff\Backup\" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

[QUENBY]

This macro should do what you want and it puts the backup in the same directory.

  Public Sub CommandButton1_Click () 'workbook copy protection
 Sun name As String
     name = Day (Date) & "-" & Month (Date) & "-" & Year (Date) & "_" & ActiveWorkbook.Name
     ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & Name
     rep = MsgBox ("Your database is saved under the name:" & name, vbYes + vbInformation, "Backup Copy folder")
 End Sub

To integrate you open VBA (Alt + F11) and the Project window (ctrl + R) then your question sheet and you glue the macro. Then you pressed on your sheet, you do a right click on the menu bar in free space, form and you choose the icon button. With small cross thou draw a button where you want and you put the label wanted then with right click you choose and you assign a macro valid your macro that appears. The function is used.

[Hollyn]

I am a new to VBA and am trying to automate a dashboard that contains data from a database that it even exists on a separate sheet of the same workbook that contains the dashboard. On the database I have the following variables: number, name, pole, months of training, amount of training, hotel, meals, airfare, logistics costs. The panel contains the months of training, the pole and consumption monthly knowing that to consumption = amount of training + hotel + meals + airfare + logistics costs. I how to automate this panel.

[Leanne]

I added the date and time in the macro, redirected to a backup directory "old" nickel and it works except for the time: it is 11:06, for example, the filename will be 25-10 - 2007_116_ [filename]. How to add the '0 'scores in this case. Name = Day (Date) & "-" & Month (Date) & "-" & Year (Date) & "_" & Hour (Time) & Minute (Time) & Second (Time) & "_" & ActiveWorkbook. Name. Another question without abusing your patience: I would reverse the filename to get something of the form [xxxxxx_xxxx_extension_xls] _ [date] _ [time] _ [. Xls]. And if I put ActiveWorkbook.Name first, I directly attached xls extension which gives [xxxxx_xxxx_extension_xls] _date_xxx.

[Koman]

Here is a short vba code which will help you. Try running the same and then check back.

Function to test whether the backup folder exists 
 Function backup folder (ByVal FolderName As String) As Boolean 
 If (Dir (FolderName, vbDirectory) <> "") Then 
 Backup folder = True 
 Else 
 Backup folder = False 
 End If 
 End Function 

 'Create backup folder may not be available 
 Sub auto_open () 
 Not if backup folder (ActiveWorkbook.Path & "\ backup \") Then 
 MkDir ActiveWorkbook.Path & "\ backup \" 
 End If 
 End Sub 

 'Automatic backup in a Unterverzeichis "\ backup \" 
 Sub Backup () 
 Dim WshShell 

 'Note on backup 
 Set WshShell = CreateObject ("WScript.Shell") 
 WSHShell.Popup "... please wait", 1, "Auto Backup ..." 

 'Save a copy of the workbook 
 ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\ backup \ backup_" & Date & ". Xls" 

 'Timer 
 WSHShell.Popup "Backup finished", 1, "Auto Backup ..." 
 Application.OnTime Now + TimeSerial (0, 5, 0), "Backup", False 
 End Sub

[AnitG]

Yes I would also prefer the auto-recovery - why create so much garbage - but no matter. I suspect that it does not work because you have the code in a worksheet. Since the procedure "backup" will not be found. If that's the case, then pack the code in a normal module. Incidentally, you should disable ontime statements if they were not running when the file is closed.