Private Function Sheets_List_From_File(SourceFileFullName As String, SheetNames As Variant, Separator As String) As String

    Dim cn As Object, cat As Object, tbl As Object, str$
    Set cn = CreateObject("ADODB.Connection")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
    cn.Open "dsn=excel files;dbq=" & SourceFileFullName
    cat.ActiveConnection = cn
    For Each tbl In cat.Tables
        str = str & Replace(tbl.Name, "$", "") & Separator
    Next
    'MsgBox str
    cn.Close
    Set cn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
    
    Sheets_List_From_File = str

End Function

lists sheets without opening the file

Sheets_List_From_File

database, list

Visual Basic