Option Explicit
Dim objWMIService As Object
Dim Lettre As String
Dim Resultat As Integer
Sub Test()
Dim strComputer As String, Chemin As String
strComputer = "."
Chemin = "C:\Documents and Settings\mimi\dossier\general\excel"
Lettre = Left(Chemin, 2)
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
SousDossiers Chemin, True
MsgBox "Il y a " & Resultat & " classeurs Excel dans " & vbCrLf & "'" & Chemin & "'" & _
vbCrLf & "et ses sous répertoires."
Resultat = 0
Set objWMIService = Nothing
End Sub
Sub SousDossiers(ByVal NomDossier As String, BoolSousRep As Boolean)
Dim colSubfolders As Object, objFolder As Object
Dim colFiles As Object
Dim Repertoire As String
Repertoire = Application.WorksheetFunction.Substitute(NomDossier, "\", "\\")
Repertoire = Mid(Repertoire, 3) & "\\"
'----------
Set colFiles = objWMIService. _
ExecQuery("SELECT * FROM CIM_DataFile WHERE Path = '" & Repertoire & "' " & _
"AND Drive = '" & Lettre & "' AND Extension = 'xls'")
Resultat = Resultat + colFiles.Count
'Debug.Print NomDossier
'----------
If BoolSousRep Then
Set colSubfolders = objWMIService.ExecQuery _
("Associators of {Win32_Directory.Name='" & NomDossier & "'} " _
& "Where AssocClass = Win32_Subdirectory " & "ResultRole = PartComponent")
For Each objFolder In colSubfolders
SousDossiers objFolder.Name, BoolSousRep
Next
End If
End Sub