Here is a great bit of visual basic code that can be used to password protect multiple workbooks at once. Most recently, I used this code to distribute a number of spreadsheets to people across our business that included confidential information. While this article doesn’t cover it, it should be very easy to update this code to add passwords to other MS Office files, such as word documents (for total pay statements, or benefits documents.)

To get started, run this code by opening excel and hitting “Alt” + “F9”, and pasting this code into the VBA box, and then clicking “Run”.



Public Sub addPassword()
 
  Dim FSO As Object
  Dim folder As Object, subfolder As Object
  Dim wb As Object
 
  Set FSO = CreateObject("Scripting.FileSystemObject")
  'update the path where the files are saved below
  folderPath = "C:\"
  Set folder = FSO.GetFolder(folderPath)

     With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
    .AskToUpdateLinks = False

  End With
  
  For Each wb In folder.Files
    'the different formats below are specificied with "xls", "xlsx" and "xlsm"
    If Right(wb.Name, 3) = "xls" Or Right(wb.Name, 4) = "xlsx" Or Right(wb.Name, 4) = "xlsm" Then
      Set masterWB = Workbooks.Open(wb)
      'update "yourpassword" to the password you would like to use, below
      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)
        'update "yourpassword" to the password you would like to use, below
        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

ufeffEnd Sub

The code will look like the image below, in the VBA environment. Once you’ve got the code in the window, simply press run, the green triangle in the ribbon, right above “Debug”, to password protect your files. 

After you hit run, the code will loop through all of your files within the folder and subfolders you specified and apply the password. Depending on how many files you are protecting, this may take awhile.

Categories: