How to automatically have excel read the sheet name of sheet 1 of the target file when using ADODB

I am trying to use ADODB to automatically read information from specific cells on a closed workbook and put that information into a master sheet.

The closed workbooks all only have 1 sheet but they all have different names.

How do I make it so the macro automatically knows the name of the sheet?

I.E rs.Source = "SELECT * FROM [XXX$A1:A1] 'XXX being the name of the target sheet that the macro has found automatically

Sub ImportDataFromClosedSheet() 
    Dim cn As ADODB.Connection 
    Dim rs As ADODB.RecordsetSet    
    
    'This paragraph selects a target file
    Dim file As FileDialog
    Dim sItem As String
    Dim GetFile As String

    Set file = Application.FileDialog(msoFileDialogFilePicker) 
    With file 
        .Title = "Select a File" 
        .AllowMultiSelect = False 
        '.InitialFileName = strPath 
        If .Show <> -1 Then GoTo NextCode 
        sItem = .SelectedItems(1) 
    End With

NextCode: 
    GetFile = sItem 
    Set file = Nothing    
    
    cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & GetFile & ";" & _
        "Extended Properties='Excel 12.0 Xml;HDR=No';"
    
    cn.Open
    
        Set rs = New ADODB.Recordset
    
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [XXX$J14:J14]"
        rs.Open
    
        Sheet1.Range("A1").CopyFromRecordset rs
       
        rs.Close
    
    cn.Close

End Sub

Solution

If you don't specify the sheetname in the SQL statement, The ADODB driver will use the first sheet. As you state that there is always only one sheet in the file, just use

 rs.Source = "SELECT * FROM [J14:J14]"

However, if you really want to get the sheet names, use ADODB openShema with the QueryType adSchemaTables. The following function will return an array with all table names of an open connection - in case of Excel, "table" means sheets.

Function getTableNames(cn As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Set rs = cn.OpenSchema(adSchemaTables)

    Dim data
    data = rs.GetRows
        
    ReDim tableNames(LBound(data, 2) To UBound(data, 2))
    Dim row As Long
    For row = LBound(data, 2) To UBound(data, 2)
        tableNames(row) = data(2, row)
    Next
    getTableNames = tableNames
End Function

Now, your code could look like this:

cn.Open
Dim sheetNames
sheetNames = getTableNames(cn)
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

rs.Source = "SELECT * FROM [" & sheetNames(0) & "J14:J14]"
rs.Open
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
cn.Close