VBA code to unprotect multiple workbooks with the same password

The Visual Basic code posted here, by the great Graham Mayor, can be used to either automatically change the password for multiple Excel files or change their password to something other than the original password.

Open a blank workbook, and then press ALT + F11 to go into Visual Basic. In a new module enter the below VBA code. This macro will enter a given password for all of the Excel files with the extension .xls or xlsx in the file path listed on the line beginning:

Const fPath As String =

Enter the current password for the files on the line beginning: Const strPassword As String =

. . . and then the new password on these lines:

Password:="", _ WriteResPassword:="", _

If you don't want the files to be password protected you can simply enter "" on these lines.

In this example the original password of the files is: desk

. . . and I am unprotecting the workbooks altogether.

Sub RemovePasswords() Dim xlBook As Workbook Dim strFilename As String Const fPath As String = "C:\FooFolder\excels\" 'The folder to process, must end with "\" Const strPassword As String = "desk" 'case sensitive Const strEditPassword As String = "" 'If no password use "" strFilename = Dir$(fPath & "*.xls") 'will open xls & xlsx etc While Len(strFilename) <> 0 Application.DisplayAlerts = False Set xlBook = Workbooks.Open(Filename:=fPath & strFilename, _ Password:=strPassword, _ WriteResPassword:=strEditPassword) xlBook.SaveAs Filename:=fPath & strFilename, _ Password:="", _ WriteResPassword:="", _ CreateBackup:=True xlBook.Close 0 Application.DisplayAlerts = True strFilename = Dir$() Wend End Sub

Press play and the macro will open each file and remove the original password. One of the nice things about Graham's code is that it creates a back-up of each original file, which retains the original password, and is renamed with 'Backup of' at the beginning of its file name.

As always, I have tested out this vba code and confirmed that it works.

Note if you have trouble pasting this vba code directly into Visual Basic from this page, copy it into Word first and then re-paste into Visual Basic.