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.

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

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.

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.

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