CODEWITHSUNDEEP
excelvba
StillLearningHowtoCode
StillLearningHowtoCode

Reputation: 23

Excel VBA code to password protect back up file

Good Afternoon,

I have the below code that I'm using to create a back up of my workbook upon saving. It is saving to a separate location and everything appears to be working correctly. The below code is entered for the workbook instead of individual sheets.

Can anyone tell me how I can create a password protection on the back up file the below code is generating?

I'm fairly new to VBA scripting but I have seen a function to create password protection with the save as function but I don't know how to incorporate that into my code below.

Any help is greatly appreciated!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False

thisPath = ThisWorkbook.Path
myName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".") - 1))
ext = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, "."))
backupdirectory = "Backup"

Set FSO = CreateObject("Scripting.FileSystemObject")

If Not FSO.FolderExists(ThisWorkbook.Path & "/" & backupdirectory) Then
    FSO.CreateFolder (ThisWorkbook.Path & "/" & backupdirectory)
End If

T = Format(Now, "mmm dd yyyy hh mm ss")
ThisWorkbook.SaveCopyAs thisPath & "\" & backupdirectory & "\" & myName & " " & T & "." & ext

Application.EnableEvents = True
End Sub

Upvotes: 2

Views: 509

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

I have an Excel file called master.xlsm. This tiny sub:

1.saves the file
2.saves a backup copy of the file with protection
3.quits Excel

Sub SaveAndBackup()
    ThisWorkbook.Save
    ThisWorkbook.SaveAs Filename:="backup.xlsm", Password:="secret"
    Application.Quit
End Sub

Change this to meet your own file-naming and location needs.

Upvotes: 1

Related Questions