Unprotect the Excel workbooks in a specified path and refresh the workbook conection and then protec-CodePudding

To elaborate(Unprotect the Excel workbooks in a specified path and refresh the workbook conection and then protec the workbooks)

I've a four workbooks in a specific path used by different users. I've used the power query to consolidate the workbooks with my Mastersheet using Data--> Refreshall. Some of the internal team issues I'm in a situation of protecting the workbooks.

After the initiation, the mastersheet won't get refreshed it indicates '[DataFormat.Error] File contains corrupted data.'. When i'm unprotecting the workbook, it worked properly.

So, please help me with this.

Option Explicit
Sub Unlock_Refresh()

    Dim wb As Workbook, ws As Worksheet
    Dim Filepath As String, Filename As String
    Dim n As Long
    
    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 <> ""
        
        Set wb = Workbooks.Open(Filepath & Filename, Password:=pass)
        With wb
            .Unprotect Password:=pass
            .RefreshAll
            Application.Wait Now   TimeValue("00:00:10")
            .Protect Password:=pass
            .Close savechanges:=True
        End With
        n = n   1
        Filename = Dir

    Loop
    Application.ScreenUpdating = True
    MsgBox n & " workbooks refreshed in " & vbLf & Filepath, vbInformation    
End Sub

CodePudding user response：

Don't know if this is related to the issue you're having or not, but there appears to be some logical disconnections in your code.
Line:

Set wb = Workbooks.Open(Filepath & Filename, Password:=pass)

Opens a workbook using a password that would be required only if the book was password protected to open. I note here:
a) this line of code will work even of no 'password-to-open' was set
b) as the code doesn't set a password-to-open, one assumes that is done manually?

Lines:

.Unprotect Password:=pass
.RefreshAll
.Protect Password:=pass

Un-protects the book-structure (and windows), refreshes external data, then re-protects the book-structure (only). I note here:
a) book-structure protection doesn't need to be off, in order to refresh external data
b) this doesn't set the 'password-to-open'
So it's unclear why the un-protect and re-protect logic cycle is there?

In case it's relevant, the following code would be used to set the 'password-to-open':

[workbook].Protect = pass