Help to use Sheet name to read other workbooks

Hi Guys, I really need help with the code below.
Basically i have many other workbooks with names like 2110, 4685 , 3345 etc. All workbooks are identical, with the same structure.
I have other (matrix) that contain information about all the other workbooks consolidated.Also inside i have some worksheets with the same name (2110,4685,3345).
So basically, the code below is in the Matrix and in my mind i can use it to get information from all other workbooks (2110, 4685 , 3345 ) using the worksheet name of the Matrix.
My problem : Actually i have one code to read each workbook. What can i do on this code code to get all informations from the workbooks and fill the MATRIX ?
I know that i need to change this part (path = ActiveWorkbook.Path & "\2110.xlsm" ) but i have no idea how can i do it.

Sub BuscaSQL1()
    Dim ConecaoPlan As New ADODB.Connection
    Dim rsConsulta As New ADODB.Recordset
    Dim path As String
    Dim Roda As Integer
    Dim sql As String

    path = ActiveWorkbook.Path & "\2110.xlsm"
    
    ConecaoPlan.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Caminho & ";Extended Properties=Excel 8.0;"  '";Extended Properties=Excel 12.0 Xml;HDR=YES;"Extended Properties=Excel 8.0
    ConecaoPlan.Open

    Range("B10").Select
    
    Do While ActiveCell.Value <> "Cash Receipts"
        sql = "Select * From [MontaBase$] Where Dado1 Like '" & ActiveCell.Value & "'"
        rsConsulta.Open sql, ConecaoPlan, adOpenKeyset, adLockOptimistic
                
        If rsConsulta.RecordCount > 0 Then
            For Roda = 2 To 65 Step 5
            'Range("FU10").Select
                Cells(ActiveCell.Row, ActiveCell.Column + Roda).Value = rsConsulta!Dado2
                rsConsulta.MoveNext
            Next
        End If
        rsConsulta.Close
        Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
    Loop
    Set ConecaoPlan = Nothing
    Set rsConsulta = Nothing
End Sub

Thanks for your time and sorry about my bad english.

Hi vidaLL,

The following code should help you get started:

Sub FatherToBuscaSQL1()

  Dim sBaseFileName As String
  
  sBaseFileName = "2110"
  
  Call BuscaSQL1(sBaseFileName)
  
  'or

  Call BuscaSQL1("2110")

End Sub

Sub BuscaSQL1(sBaseFileName As String)
    Dim ConecaoPlan As New ADODB.Connection
    Dim rsConsulta As New ADODB.Recordset
    Dim path As String
    Dim Roda As Integer
    Dim sql As String

    path = ActiveWorkbook.path & "\" & sBaseFileName & ".xlsm"
    
    ConecaoPlan.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Caminho & ";Extended Properties=Excel 8.0;"  '";Extended Properties=Excel 12.0 Xml;HDR=YES;"Extended Properties=Excel 8.0
    ConecaoPlan.Open

    Range("B10").Select
    
    Do While ActiveCell.Value <> "Cash Receipts"
        sql = "Select * From [MontaBase$] Where Dado1 Like '" & ActiveCell.Value & "'"
        rsConsulta.Open sql, ConecaoPlan, adOpenKeyset, adLockOptimistic
                
        If rsConsulta.RecordCount > 0 Then
            For Roda = 2 To 65 Step 5
            'Range("FU10").Select
                Cells(ActiveCell.Row, ActiveCell.Column + Roda).Value = rsConsulta!Dado2
                rsConsulta.MoveNext
            Next
        End If
        rsConsulta.Close
        Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
    Loop
    Set ConecaoPlan = Nothing
    Set rsConsulta = Nothing
End Sub

Please note that the use of 'path' as a variable name works here, but should not be used becausse 'path' is an Excel VBA keyword.

Lewis

The same applies to "Sql", this word is also reserved in vba (QueryTables collection), e.g.:

    ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

It's true that nothing 'terrible' happens, but ... who knows ...