Add password to all Excel workbook in folder

This Excel VBA tutorial explains how to add password to all Excel workbook in folder and how to mass remove password.

You may also want to read:

Excel VBA protect worksheet with password

Excel VBA hide worksheet with password (xlVeryHidden)

Protect VBA code with password

Add password to all Excel workbook in folder

In many organizations, employees are required to add password to Excel before sending workbooks through email, or before putting workbooks in network drive. If you have a lot of workbooks to add password, it is definitely a waste of time. This tutorial demonstrates how to add password to all workbooks in a specified folder.

Previously I have written another post that demonstrates how to add password to Excel using VBA.

Excel add password to workbook

I have also written a post that explains how to loop through folders and subfolders.

Excel loop workbooks in folders and subfolders with FSO

I will base on these two posts to write the Macro.

Install Microsoft Scripting Runtime

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

Excel VBA – Add password to all Excel workbook in folder

The below Procedure adds password “yourpassword” to all Excel under folder C:\Users\WYMAN\Desktop\excelfolder and all subfolders one level down.

Public Sub addPassword()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\Users\WYMAN\Desktop\excelfolder"
    Set folder = FSO.GetFolder(folderPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
        
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set masterWB = Workbooks.Open(wb)
            ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="yourpassword"
            ActiveWorkbook.Close True
        End If
    Next
    For Each subfolder In folder.SubFolders
        For Each wb In subfolder.Files
            If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
                Set masterWB = Workbooks.Open(wb)
                ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:="yourpassword"
                ActiveWorkbook.Close True
            End If
        Next
    Next
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Excel VBA – Remove password to all Excel workbook in folder

The difference between Remove password and Add password is that for remove password, we open workbook with the existing password and save the file with no password (double quote “”).

Public Sub removePassword()
    Dim FSO As Object
    Dim folder As Object, subfolder As Object
    Dim wb As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    folderPath = "C:\Users\WYMAN\Desktop\excelfolder"
    Set folder = FSO.GetFolder(folderPath)
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
        
    For Each wb In folder.Files
        If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
            Set masterWB = Workbooks.Open(Filename:=wb, Password:="yourpassword")
            ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:=""
            ActiveWorkbook.Close True
        End If
    Next
    For Each subfolder In folder.SubFolders
        For Each wb In subfolder.Files
            If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
                Set masterWB = Workbooks.Open(wb, Password:="yourpassword")
                ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, Password:=""
                ActiveWorkbook.Close True
            End If
        Next
    Next
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Add password to all Excel workbook in folder with User Interface

For those who are not familiar with VBA, I have written a template to add password or remove password for all Excel under specified path and one level down subfolders.

Click here to download the template.

 

You should be able to run FSO in Excel 2013. If you fail to run FSO Object, open VBE (ALT+F11) > Tools > References > Check the box Microsoft Scripting Runtine

 

 

Outbound References

https://support.office.com/en-us/article/Password-protect-a-workbook-7e365a4d-3e89-4616-84ca-1931257c1517