Remove known passwords from multiple files in folder

[ilsley_excel]

Hi All! I have a folder with several .xlsx files which I wanted to password protect with the same password, which I did using the following code:

Public Sub LockSpreadsheets()


  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 = "R:\Data Collection\Tutor Reports\2017-18\Y7 Y8 Y9 Y10\"
  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)


      ActiveWorkbook.SaveAs fileName:=Application.ActiveWorkbook.FullName, Password:="a"
      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:="a"
        ActiveWorkbook.Close True


      End If


    Next
  Next


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


End Sub

However, I now want to build a similar routine that REMOVES the password (i.e. the password is 'a') from all the files.

Any help would be appreciated!

Thanks.

 

[RoryA]

Supply the password when opening, and leave blank when saving (incidentally, there's no need to save when closing given that you just saved it!):

Set masterWB = Workbooks.Open(wb, password:="a")


      ActiveWorkbook.SaveAs fileName:=Application.ActiveWorkbook.FullName, Password:=""
      ActiveWorkbook.Close

 

[ilsley_excel]

Supply the password when opening, and leave blank when saving (incidentally, there's no need to save when closing given that you just saved it!):

Set masterWB = Workbooks.Open(wb, password:="a")


      ActiveWorkbook.SaveAs fileName:=Application.ActiveWorkbook.FullName, Password:=""
      ActiveWorkbook.Close

Thanks!

That worked perfectly.

 

[RoryA]

Glad to help.