Get count of all .dot files in folder and its subfolders?

[clhare]

Can a macro count all the .dot files in a particular folder and all it's subfolders?

[fumei]

Yes.

[clhare]

That's good to know. Can you help me figure out how to do it?

[Tinbendr]

How about this? (http://www.vbaexpress.com/kb/getarticle.php?kb_id=247)

[fumei]

Use FileSearch, but it does not have to be as complicated as Kens example.

If you just want a count.....
With Application.FileSearch
.FileName = ".dot"
.LookIn = "c:\test"
.SearchSubFolders = True
.Execute
If .FoundFiles.Count > 0 Then
Msgbox .FoundFiles.Count & " dot files found."
End If
End With


Or....

Dim var
Dim msg As String
With Application.FileSearch
.FileName = ".dot"
.LookIn = "c:\test"
.SearchSubFolders = True
.Execute
If .FoundFiles.Count > 0 Then
For var = 1 To .FoundFiles.Count
msg = msg & .FoundFiles(var) & _
vbCrLf
Next
End If
End With
Selection.TypeText Text:=msg
which would type in the full path and names. NOTE however, that the listing will be by the alphabetical order of the filenames, NOT the folder/subfolder. For example, here is the output of the above.

C:\Test\Appointment Template Gerry.dot
C:\Test\Appointment Template Health CentreGJK.dot
C:\Test\Change_Observation_Ver._7.1.dot
C:\Test\VBA_WorkDocs\checkvar.dot
C:\Test\CVConversion.dot
C:\Test\DifferentHeaders.dot
C:\Test\PPRTemplate.dot
C:\Test\test2\Restart NumberingGerry.dot
C:\Test\tektips.dot
C:\Test\VBA_WorkDocs\VBAExpress.dot

Notice that checkvar.dot (in the subfolder VBA_WorkDocs) is listed before CVConversion.dot (in the folder above it).

You can of course have things listed by folder...but really, Cheryl, what did you attempt yourself?

[fumei]

OK, OK, here is how you get the result that will do it by searched folder, not filename.

Dim var
Dim msg As String
With Application.FileSearch
.FileName = ".dot"
.LookIn = "c:\test"
.SearchSubFolders = True
.Execute (msoSortByNone)
If .FoundFiles.Count > 0 Then
For var = 1 To .FoundFiles.Count
msg = msg & .FoundFiles(var) & _
vbCrLf
Next
End If
End With
Selection.TypeText Text:=msg

Result:

C:\Test\Appointment Template Gerry.dot
C:\Test\Appointment Template Health CentreGJK.dot
C:\Test\Change_Observation_Ver._7.1.dot
C:\Test\CVConversion.dot
C:\Test\DifferentHeaders.dot
C:\Test\PPRTemplate.dot
C:\Test\tektips.dot
C:\Test\test2\Restart NumberingGerry.dot
C:\Test\VBA_WorkDocs\checkvar.dot
C:\Test\VBA_WorkDocs\VBAExpress.dot

Notice the order is:

C:\Test
C:\Test\test2
C:\Test\VBA_WorkDocs