Results 1 to 7 of 7

Thread: Count of Files in a Folder - File Extension Type wise

  1. #1
    Neophyte buvanamali's Avatar
    Join Date
    Aug 2016
    Posts
    4
    Articles
    0
    Excel Version
    2007

    Count of Files in a Folder - File Extension Type wise



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Sir / Madam

    I am a newbie to VBA macros. I found the following macro on the internet, which is very much useful, accurate and perfect. As I am not conversant with VBA, I request you to help me in getting the count of files in a folder file extension type wise as attached in the sample workbook.

    Thanks in Advance

    Buvanamali

    Code:
    Function CountFiles_FolderAndSubFolders(strFolder As String, Optional strExt As String) As Double
    'Original Author: Ken Puls (www.excelguru.ca)
    'http://www.excelguru.ca/content.php?139-Count-Files-%28with-a-specific-extension-or-not%29-in-a-folder-and-subfolders
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim objSubFolder As Object
    Dim PosOfLastDot As Long
    Dim IncludeSubFolders As Boolean
    
    On Error GoTo EarlyExit
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFolder)
       
    For Each objFile In objFolder.Files
        PosOfLastDot = InStrRev(objFile, ".") 'to find the position of the last dot
            If (objFile.Attributes And vbHidden) = False Then 'if you list your documents folder (windows 7). there is hidden folders with shortcut arrows, will be skipped, but with a message "Permission denied", and carry on
                If strExt = "" Or (UCase(Right(objFile.path, (Len(objFile.path) - InStrRev(objFile.path, ".")))) = UCase(strExt)) Then
                    CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + 1
                Else
                    If Right(objFile, Len(objFile) - PosOfLastDot) = strExt Or (UCase(Right(objFile.path, (Len(objFile.path) - InStrRev(objFile.path, ".")))) = UCase(strExt)) Then
                        CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + 1
                    End If
                End If
            End If
        Next objFile
    
    IncludeSubFolders = True 'change to False if you want to list only the parent folder
    
    If IncludeSubFolders = True Then
     
        For Each objSubFolder In objFolder.SubFolders
            CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + CountFiles_FolderAndSubFolders(objSubFolder.path, strExt)
        
        Next objSubFolder
    End If
    
    Exit Function
    EarlyExit:
    MsgBox Err.Description
    Set objFile = Nothing
    Set objSubFolder = Nothing
    Set objFolder = Nothing
    Set objFSO = Nothing
    On Error GoTo 0
    End Function
    
    Sub CountFiles()
    'Original Author: Ken Puls (www.excelguru.ca)
    'http://www.excelguru.ca/content.php?139-Count-Files-%28with-a-specific-extension-or-not%29-in-a-folder-and-subfolders
    Dim dblCount As Double
    
    dblCount = CountFiles_FolderAndSubFolders("D:\DESKTOP\Mali", "xltx") 'put your extention between the last 2 "". Will list all files if left like this, also change your folder
                 Range("B23").Value = dblCount
                 
            
    End Sub
    Attached Files Attached Files
    Last edited by p45cal; 2020-07-07 at 04:06 PM. Reason: changed php tags to code tags

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,747
    Articles
    0
    Excel Version
    365
    In cell C5 of your sheet, paste in this formula:
    =CountFiles_FolderAndSubFolders($C$2,B5)
    and copy down.

  3. #3
    Neophyte buvanamali's Avatar
    Join Date
    Aug 2016
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Thank you Sir. Working Fine. For knowledge purpose, can you please advise vba code for the same.
    Thanks in adv.

    Buvanamali

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,747
    Articles
    0
    Excel Version
    365
    Code:
    Sub blah()
    Dim myPath As String
    With Sheets("Sheet1")
      myPath = .Range("C2").Value    'or:
      'myPath = "D:\DESKTOP\Mali" 'adjusted as necessary, of course.
      For Each cll In .Range("B5:B27").Cells
        cll.Offset(, 1).Value = CountFiles_FolderAndSubFolders(myPath, cll.Value)
      Next cll
    End With
    End Sub

  5. #5
    Neophyte buvanamali's Avatar
    Join Date
    Aug 2016
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Dear Sir

    Thanks a lot. Working Fine. One more request, in the above sample, the file extensions were given instead let the macro furnish the type of file extensions available and its count in a given folder. Further the macro may be designed to select a folder by the user.

    Thanks in advance.

    Buvanamali

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,747
    Articles
    0
    Excel Version
    365
    It seems you think that this is a free code- writing service.

  7. #7
    Neophyte buvanamali's Avatar
    Join Date
    Aug 2016
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Dear Sir

    Thanks a lot once again. Sorry for the disturbance.

    Have a nice day.
    Stay Safe

    Buvanamali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •