Open workbooks in the "path" with "passwd", and read data.

ReadDataFromWorkbookWithPasswd.vba

Public Sub パスワード付きファイルを開く()
    Dim path As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            path = .SelectedItems(1)
        End If
    End With

    Dim passwd As String
    passwd = InputBox("読み取りパスワード", "読み取りパスワード", "")

    Dim fso, file, files
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set files = fso.GetFolder(path).files
    
    
    Dim r As Integer
    r = 1
    
    Dim mws As Worksheet
    
    Set mws = Application.Workbooks(1).Worksheets(1)
    
    For Each file In files
        Dim wb As Workbook
        Set wb = Workbooks.Open(Filename:=file, Password:=passwd, ReadOnly:=True)
        
        mws.Cells(r, 1) = file
        mws.Cells(r, 2) = passwd
        mws.Cells(r, 3) = wb.Sheets(1).Cells(1, 1).Value
                
        Call wb.Close(SaveChanges:=False)
    
        r = r + 1
    
    Next file
    
End Sub