Option Explicit
Sub test()
Dim listsheet, filepath$
'adapter le chemin ici ou injecter un dialog getopenfilename
filepath$ = "C:\Users\patricktoulon\Desktop\tototo.xlsx"
listsheet = ListSheetOnClosedFile(filepath)
If UBound(listsheet) > 0 Then
MsgBox Join(listsheet, vbCrLf)
Else
MsgBox "La connection au classeur n'a pas pu lister les feuille du classeur"
End If
End Sub
Function ListSheetOnClosedFile(lPath As String)
'patricktoulon collection Ado
Dim Connection As Object, recordST As Object, tbl(), a&
Set Connection = CreateObject("ADODB.Connection") ' Créer connexion ADO
' Ouvrir connexion (type de connection pour les version superieures a 2003/2007 et +)
Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set recordST = Connection.OpenSchema(20) ' Récupérer la liste des feuilles ' 20 = adSchemaTables
Do Until recordST.EOF
Debug.Print recordST!TABLE_NAME
If recordST!TABLE_NAME Like "*$" Or recordST!TABLE_NAME Like "*'" Then
a = a + 1
ReDim Preserve tbl(1 To a)
tbl(a) = Replace(Replace(recordST!TABLE_NAME, "$", ""), "'", "")
End If
recordST.MoveNext
Loop
recordST.Close: Connection.Close: Set recordST = Nothing: Set Connection = Nothing
If a = 0 Then
ListSheetOnClosedFile = Array() ' on met un tableau vide pour eviter l'erreur dans la sub appelante
Else
ListSheetOnClosedFile = tbl
End If
End Function