The VBA script given below will create a backup of your Excel spreadsheet with the current date in the file name, and save it to the specified backup folder. You can modify the script to change the backup folder path and file name if needed.
- Open the Excel spreadsheet that you want to back up.
- Press the Alt + F11 keys to open the Visual Basic Editor.
- In the Visual Basic Editor, go to Insert -> Module to create a new module.
In the new module, type the following code:
Sub Backup()
' Declare variables
Dim backupPath As String
Dim fileName As String
Dim sourcePath As String
Dim FSO As Object
' Set the backup folder path
backupPath = "C:\Backups\"
' Set the file name
fileName = "MyExcelFileBackup_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
' Set the source path
sourcePath = ThisWorkbook.FullName
' Check if backup folder exists, create it if it does not
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(backupPath) Then
FSO.CreateFolder (backupPath)
End If
' Check if backup file exists, delete it if it does
If FSO.FileExists(backupPath & fileName) Then
FSO.DeleteFile (backupPath & fileName)
End If
' Copy the file to the backup folder
FSO.CopyFile sourcePath, backupPath & fileName
' Show a message box to confirm completion
MsgBox "Backup completed successfully!", vbInformation, "Backup"
End Sub
- Save the module and close the Visual Basic Editor.
- To run the backup script, go to the Excel spreadsheet and press Alt + F8 to open the Macros dialog.
- Select the “Backup” macro and click Run.
