Save a workbook backup using VBA in Microsoft Excel

 

One should take backup of the data at regular intervals of time as any wrong modification by the other user or accidentally deletion of the excel file may spoil a lot of time and information.

In this article, we will cover how to take backup of Excel file using VBA.

In this article, we will cover two different ways of taking backup using VBA code. We have written two macros for taking backup of Excel file.

“SaveWorkbookBackup” macro will create a backup of Excel file with “.bak” extension within the same folder where active workbook is saved.

ArrowBackUpFileCreated

“SaveWorkbookBackupToFloppy” macro will create a copy of active workbook in drive D which will act as backup file for the active workbook.

ArrowBackUpCopyCreated

Code explanation

If Not Ok Then

MsgBox “Backup Copy Not Saved!”, vbExclamation, ThisWorkbook.Name

End If

Above code is used for displaying error message, when some runtime error occurs during the execution of the macro.

NotSaveDialogBox

If AWB.Path = “” Then

‘Displaying Save as dialog box for file saving

Application.Dialogs(xlDialogSaveAs).Show

Above code is used for displaying Save As dialog box, if file is not saved before taking the backup of the file.

SaveAs Dialog Box

 

Please follow below for the code

Option Explicit

Sub SaveWorkbookBackup()

Dim AWB As Workbook, BackupFileName As String, i As Integer, Ok As Boolean

On Error GoTo NotAbleToSave
    
Set AWB = ActiveWorkbook

'Assign full path of file along file name to variable BackupFileName
BackupFileName = AWB.FullName

'Checking whether file is saved
'If file is not saved then saving the file
If AWB.Path = "" Then
    'Displaying Save as dialog box for file saving
    Application.Dialogs(xlDialogSaveAs).Show
Else
    
    'Removing file extension from file name
    i = 0
    While InStr(i + 1, BackupFileName, ".") > 0
    'Find the extension of file
        i = InStr(i + 1, BackupFileName, ".")
    Wend
    
    If i > 0 Then BackupFileName = Left(BackupFileName, i - 1)
    
    'Adding back up extension ".bak" with file name
    BackupFileName = BackupFileName & ".bak"
    Ok = False
    
    With AWB
        .Save
        'Creating Backup of file
        .SaveCopyAs BackupFileName
        Ok = True
    End With
End If

NotAbleToSave:
'Code for error handling
    Set AWB = Nothing
    If Not Ok Then
        MsgBox "Backup Copy Not Saved!", vbExclamation, ThisWorkbook.Name
    End If
End Sub

Sub SaveWorkbookBackupToFloppy()

Dim AWB As Workbook, BackupFileName As String, i As Integer, Ok As Boolean
Dim DriveName As String

On Error GoTo NotAbleToSave
    
'Specify the path for making back up in D drive
DriveName = "D:\"

'Initializing the variables
Set AWB = ActiveWorkbook
BackupFileName = AWB.Name
Ok = False

'Checking whether file is saved
'If file is not saved then saving the file
If AWB.Path = "" Then
    'Displaying Save as dialog box for file saving
    Application.Dialogs(xlDialogSaveAs).Show
Else
    'Deleting file if backup file already exists
    If Dir(DriveName & BackupFileName) <> "" Then
        Kill DriveName & BackupFileName
    End If
    With AWB
        .Save
        'Creating the back up file
        .SaveCopyAs DriveName & BackupFileName
        Ok = True
    End With
End If

NotAbleToSave:
'Code for error handling
    Set AWB = Nothing
    
    If Not Ok Then
        MsgBox "Backup Copy Not Saved!", vbExclamation, ThisWorkbook.Name
    End If
End Sub

 

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com



Example:


4 thoughts on “Save a workbook backup using VBA in Microsoft Excel

  1. These macros are excellent. I’m trying to design one that will allow me to ask the user for a file name and pass that file name back to the “FileSaveAs” and save the file automatically then branch to another macro. If anyone has done this or knows how, please share and save my wall (and my head). Thanks

  2. Thanks! This works well if you want to back up only a few files. But if you have several people compiling large spreadsheets, back up versions pile up and eat away the disk space. I’s rather suggest version control with git, e.g. XLTools: https://xltools.net/excel-version-control/ or other versioning tool. On the plus side you can also add comments to the version log.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>