Can a macro count all the .dot files in a particular folder and all it's subfolders?
Can a macro count all the .dot files in a particular folder and all it's subfolders?
Yes.
That's good to know. Can you help me figure out how to do it?
How about this?
David
Use FileSearch, but it does not have to be as complicated as Kens example.
If you just want a count.....[vba]
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
[/vba]
Or....
[vba]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
[/vba]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?
OK, OK, here is how you get the result that will do it by searched folder, not filename.
[vba]
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[/vba]
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