I want to unprotect the excel workbooks in a specified path using the same password. I've used the below code to do the process.

Sub Unlock_Refresh()

Dim path As String, w As Worksheet, pass As String, wb As Workbooks
pass = "1519"
Worksheets("Sheet2").Select
path = Worksheets("Sheet2").Range("A1").Value

For Each wb In path

wb.Unprotect Password:=pass

Next wb

ThisWorkbook.RefreshAll

Application.Wait ("00:00:10")

For Each wb In path

wb.Protect Password:=pass

Next wb

End Sub

It won't work. Can someone help me on this.

CodePudding user response：

Application.Wait ("00:00:10") pauses the macro till 10 seconds past midnight which is not what you wanted I guess. Use Dir to iterate over the files in the directory, open the workbook, refresh , close and save.

Sub Unlock_Refresh()

    Dim wb As Workbook, ws As Worksheet
    Dim Filepath As String, Filename As String
    Dim n As Long
    Dim books As New Collection
    
    Const pass = "1519"
    Filepath = Worksheets("Sheet2").Range("A1").Value
    If Right(Filepath, 1) <> "\" Then Filepath = Filepath & "\"
    
    Filename = Dir(Filepath & "*.xls*")
    Application.ScreenUpdating = False
    Do While Filename <> ""
        n = n   1
        Set wb = Workbooks.Open(Filepath & Filename, Password:=pass)
        books.Add wb, CStr(n)
        Filename = Dir
    Loop
    ThisWorkbook.RefreshAll
    Application.Wait Now   TimeValue("00:00:10")
    
    ' close books
    For Each wb In books
        wb.Close SaveChanges:=False
    Next
    
    Application.ScreenUpdating = True
    MsgBox n & " workbooks opened and refreshed " & vbLf & Filepath, vbInformation
    
End Sub