Display Folder Property:"Contains" in Vba

[sarvadeshavyapta]

Is it possible to fetch the Folder property -"Contains" instead of looping through each file in VBA.I'm using following code to count the no. of files in folder.But curious to know.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Sub[/COLOR][COLOR=#303336] sample[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#000000]Dim FolderPath AsString, path AsString, count AsInteger
FolderPath ="C:\Documents and Settings\"
path = FolderPath &"\*.xls"
Filename = Dir(path)
DoWhile Filename <>""
count = count +1
Filename = Dir()
Loop
Range("Q8").Value = count
MsgBox count & " : files found in folder"
End Sub[/COLOR]</code>

 

[Domenic]

The FileSystemObject can be used to count the number of files in a folder. Here's an example that uses a function to return a count of files from a specified folder...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    v = FileCount("c:\users\domenic\desktop")
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(v) [COLOR=darkblue]Then[/COLOR]
        MsgBox "Number of files:  " & v
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "No such folder exists.", vbExclamation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Function[/COLOR] FileCount([COLOR=darkblue]ByVal[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oFSO [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oFSO = CreateObject("Scripting.FileSystemObject")
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oFSO.FolderExists(sPath) [COLOR=darkblue]Then[/COLOR]
        FileCount = [COLOR=darkblue]CVErr[/COLOR](xlErrValue)
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oFolder = oFSO.GetFolder(sPath)
        FileCount = oFolder.Files.Count
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oFSO = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oFolder = [COLOR=darkblue]Nothing[/COLOR]
End [COLOR=darkblue]Function[/COLOR]

Hope this helps!

 

[sarvadeshavyapta]

Domenic,Unfortunately the folder that I access would contain sub folders too.So the code mentioned by you won't work for counting specific extensions

 

[Domenic]

Sorry, I missed the fact that you wanted to count files having a specific extension. My code can be modified to count files with a specific extension, but it would mean that we would still have to loop through each file in the folder.