Retrieve data from protected spreadsheets

You can maybe use VBA to automate the process. By embedding passwords in the VBA code, your master workbook can open the protected spreadsheets, extract the required data, and close them.

Here is an approach how you can implement it (code is not tested, backup your file first):

Insert this Code in a Module.

Sub RetrieveDataFromProtectedFiles()
    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    Dim filePath As String
    Dim passwords As Object
    Dim rng As Range
    
    ' Define a dictionary to hold file paths and passwords
    Set passwords = CreateObject("Scripting.Dictionary")
    
    ' Add file paths and their corresponding passwords
    passwords.Add "C:\Path\To\File1.xlsx", "password1"
    passwords.Add "C:\Path\To\File2.xlsx", "password2"
    ' Add more files as needed
    
    ' Set the destination worksheet
    Set wsDest = ThisWorkbook.Sheets(1) ' Adjust to your sheet index or name
    
    ' Loop through each file in the dictionary
    For Each filePath In passwords.Keys
        On Error Resume Next
        ' Open the password-protected workbook
        Set wbSource = Workbooks.Open(Filename:=filePath, Password:=passwords(filePath))
        If Err.Number <> 0 Then
            MsgBox "Error opening " & filePath & ". Please check the password.", vbCritical
            Err.Clear
            GoTo NextFile
        End If
        On Error GoTo 0
        
        ' Copy data from the source workbook
        ' Adjust the ranges to fit your needs
        Set rng = wbSource.Sheets(1).Range("A1:B10") ' Change the range and sheet as needed
        wsDest.Range("A" & wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        
        ' Close the source workbook
        wbSource.Close SaveChanges:=False
NextFile:
    Next filePath
    
    MsgBox "Data retrieval completed!"
End Sub

Replace "C:\Path\To\File1.xlsx" and "password1" with the actual file paths and passwords of your protected spreadsheets.

Add more entries to the passwords dictionary for all 10 files.

Hope I could help with this.