Option Explicit
Sub Test()
'http://forum.hardware.fr/hfr/Programmation/VB-VBA-VBS/liste-fichiers-repertoire-sujet_57846_1.htm
'ChDir "C:\...Mon chemin....\Mes documents"
Range("2:100").Clear
Dim i As Byte, z As String
ChDrive Left(Cells(1, 2), 1)
ChDir Cells(1, 2).Value
i = 1
z = Dir("*.xls", 1)
While z <> ""
ActiveSheet.Cells(i + 1, 1).Value = z
i = i + 1
z = Dir
Wend
End Sub
Sub Liste_feuilles_Selection()
Dim cell As Object, FAO As String, i As Integer, N As Integer, nc As Integer
For Each cell In Selection
FAO = Cells(1, 2) & "\" & cell.Value
Workbooks.Open (FAO)
nc = ActiveWorkbook.Sheets.Count
For N = nc To 1 Step -1
Sheets(N).Visible = True
Sheets(N).Activate
If Sheets(N).Type <> 3 Then Cells(1, 1).Select
Next
For i = 1 To nc ' Step -1
cell.Offset(0, i) = Sheets(i).Name
Next
ActiveWorkbook.Close 0
Next
'MEF centrer etenvoie à la ligne auto
Selection.CurrentRegion.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub