Remove password from Excel files

Question

I have about 400 Excel files in a folder (some are .xls and some are .xlsx ).

How can I remove a password from these files using VBA code?

Answer 1

Remove Workbook Password From .xls* Files

I guess you know the password and it's the same in all the files.

How:

It loops through all files in a folder and opens each with an extension specified in cStrExtensions, removes the password, saves and closes it.

Usage:

Run the code which will open a Folder Picker dialog, then navigate to the folder where the files are (you can't see them) and press OK.

Sub RemovePassword()

  ' String Lists
  Const cStrExtensions As String = "*.xls*"
  Const cStrPassword As String = "123"

  Dim strFolderPath As String     ' Search Folder
  Dim strFileName As String       ' Current File Name (Workbook)

  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
  End With

  On Error GoTo ProcedureExit

  With ThisWorkbook.ActiveSheet

    ' Choose Search Folder
    With Application.FileDialog(msoFileDialogFolderPicker)
      If .Show = False Then Exit Sub
      strFolderPath = .SelectedItems(1) & "\"
    End With

    ' Loop through folder to determine Current File Name (Workbook).
    strFileName = Dir(strFolderPath & cStrExtensions)

    ' Loop through files in folder.
    Do While strFileName <> ""

      ' Open each file in folder
      Workbooks.Open strFolderPath & strFileName

      With ActiveWorkbook
         .Unprotect cStrPassword
         .Close True
      End With

      strFileName = Dir()
      ' Exclude this workbook.
      If .Parent.Name = strFileName Then strFileName = Dir()

    Loop

  End With

ProcedureExit:

  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
  End With

End Sub