Count Files in Folder

[proctk]

Hi

I'm trying to find code that will count the number of excel file in a folder called Viewpoint and place the result in cell b3 on a worksheet called Sum any help is great

thank you

 

[Tazguy37]

Something like this?:

Sub listFiles()
Dim filesToProcess As Long
Dim myPath As String

    myPath = "C:\ViewPoint\"

    'Use the Filesearch Object
    With Application.FileSearch
        .NewSearch
        .LookIn = myPath
        .SearchSubFolders = False
        .Filename = "*.*"
        .Execute
        Worksheets("Sum").Range("B3").Value = .FoundFiles.Count
    End With
        
End Sub

Hope that helps!

 

[Nimrod]

Though I think Taz's solution is slicker I thought I might as well post this alternative , since I had it kicking around in my soltion library. :wink:

Public Sub Demo()
    MsgBox CountFiles("C:\ViewPoint\", "*.xls")
End Sub

Function CountFiles(tgtDir As String, Extention As String)
    Dim fName As String
    Dim cnt As Integer
    
    fName = Dir(tgtDir)
    cnt = 0
   
    Do While fName <> ""
       
        If fName <> "." And _
        fName <> ".." And _
        fName Like Extention Then
            cnt = cnt + 1
        End If
        
        fName = Dir()
    Loop
   
    CountFiles = cnt
End Function

 

[Ivan F Moala]

Or Alternatively

Sub FileCount()
    Dim fsoObj As Object  '// Scripting.FileSystemObject
    Dim fsoMapp As Object '// Scripting.Folder
   
    Set fsoObj = CreateObject("Scripting.FileSystemObject")
    Set fsoMapp = fsoObj.GetFolder("C:\ExcelFiles\Useful")
    
    MsgBox fsoMapp.Files.Count
    
    Set fsoObj = Nothing
    Set fsoMapp = Nothing
    
End Sub

 

[Nimrod]

Hello Ivan ... No looping ! I like it