Sub test()
Dim listsheet, filepath$
'adapter le chemin ici ou injecter un dialog getopenfilename
filepath$ = "C:\Users\patricktoulon\Desktop\Nouveau Feuille de calcul Microsoft Excel.xlsx"
listsheet = ListSheetOnClosedFile(filepath)
MsgBox Join(listsheet, vbCrLf)
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
a = a + 1
ReDim Preserve tbl(1 To a)
tbl(a) = Replace(recordST!TABLE_NAME, "$", "")
recordST.MoveNext
Loop
recordST.Close: Connection.Close: Set recordST = Nothing: Set Connection = Nothing
ListSheetOnClosedFile = tbl
End Function