Results 1 to 9 of 9

Thread: Count_files_through VBA

  1. #1

    Count_files_through VBA



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

    Hi Ken,

    I am new in this group. I am not able to find out that how can i post a fresh post please let me know regarding this. I have a query regarding VBA coding I have tried but getting error... Can you please what is problem with coding.. getting error at below colored part

    Code:
    Sub count_files()
    Dim Fso As FileSystemObject
    Dim Str As String
    Dim FldPath As String
    Dim fldr As Folder
    Dim Fl As File
    Dim SubFldrs As Object
        
        Set Fso = CreateObject("Scripting.FileSystemObject")
        Str = VBA.InputBox("Enter file format you want to count or leave blank for each file", "File Format")
        Application.FileDialog(msoFileDialogFolderPicker).Show
        FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & " \"
        Set fldr = Fso.GetFolder(FldPath)
        Set SubFldrs = Fso.GetFolder(FldPath).SubFolders
    Dim i As Integer
    i = 0
        For Each Fl In fldr.Files
        If Str = "" Then
        i = i + 1
        ElseIf UCase(Mid(Fl.Path, InStrRev(Fl.Path, ".") + 1, Len(Fl.Path))) = UCase(Str) Then
        i = i + 1
        Else
        End If
        Next Fl
    Dim fld1 As Folder
    For Each fld1 In SubFldrs
        Dim fl1 As File
        For Each fl1 In fld1.Files
        If Str = "" Then
        i = i + 1
        ElseIf UCase(Mid(Fl.Path, InStrRev(Fl.Path, ".") + 1, Len(Fl.Path))) = UCase(Str) Then
        i = i + 1
        Else
        End If
        Next fl1
    Next fld1
        
    MsgBox i
            
    End Sub

    Thanks & Best Regards
    VJ singh
    Last edited by Zack Barresse; 2013-02-10 at 08:01 AM. Reason: Added CODE tags

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Hi VJ,

    I moved your post to a new thread.

    I'm not sure why you've got your code setup like you do. You're using early binding with your variables, but setting them like late binding. Have you set the proper reference? Also, it helps to know what kind of error you're getting. Can you give us a number or description?

    Edit: here is a KB entry at vbaexpress.com showing how to loop through folders and all subfolders http://www.vbaexpress.com/kb/getarticle.php?kb_id=245
    Regards,
    Zack Barresse

  3. #3
    This works for me

    Code:
    Sub count_files()Dim Fso As Object
    Dim Str As String
    Dim FldPath As String
    Dim fldr As Object
    Dim SubFldrs As Object
    Dim numFiles As Long
    Dim fld1 As Object
        
        Set Fso = CreateObject("Scripting.FileSystemObject")
        Str = VBA.InputBox("Enter file format you want to count or leave blank for each file", "File Format")
        Application.FileDialog(msoFileDialogFolderPicker).Show
        FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
        
        Set fldr = Fso.GetFolder(FldPath)
        
        Call TallyFiles(fldr, Str, numFiles)
        
        Set SubFldrs = Fso.GetFolder(FldPath).SubFolders
        For Each fld1 In SubFldrs
        
            Call TallyFiles(fld1, Str, numFiles)
        Next fld1
        
        MsgBox numFiles
            
    End Sub
    
    
    Private Function TallyFiles(ByRef folder As Object, ByVal filetype As String, ByRef cnt As Long) As Boolean
    Dim F1 As Object
    
    
        For Each F1 In folder.Files
    
    
            If filetype = "" Or _
                UCase(Mid(F1.Path, InStrRev(F1.Path, ".") + 1, Len(F1.Path))) Like UCase(filetype) Then
                
                cnt = cnt + 1
            End If
        Next F1
    End Function

  4. #4

    Query

    Hi Zack Barresse,

    Thanks for your valuable reply. Can you please explain what are early binding & late binding. I have set reference by as Tools:-Reference:-& then Microsoft Scripting Runtime. I am getting Run-time error '76'; & path not found.

    Thanks & Best Regards
    VJ SINGH

  5. #5
    Hi Bob,

    Thanks for your reply, but this is not counting all files of a folder and of that many subfolders. And can you please explain colored part of the coding thanks.


    Sub count_files()Dim Fso As Object Dim Str As String Dim FldPath As String Dim fldr As Object Dim SubFldrs As Object Dim numFiles As Long Dim fld1 As Object Set Fso = CreateObject("Scripting.FileSystemObject") Str = VBA.InputBox("Enter file format you want to count or leave blank for each file", "File Format") Application.FileDialog(msoFileDialogFolderPicker).Show FldPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\" Set fldr = Fso.GetFolder(FldPath) Call TallyFiles(fldr, Str, numFiles) Set SubFldrs = Fso.GetFolder(FldPath).SubFolders For Each fld1 In SubFldrs Call TallyFiles(fld1, Str, numFiles) Next fld1 MsgBox numFiles End Sub

  6. #6
    I just took the common code out and put it in a function.

    Explain what you mean by '... this is not counting all files of a folder and of that many subfolders/, because it worked fine in my tests.

  7. #7
    My requirement is to count all files of folder and of that subfolder and of that subfolders means, it should count all files of folder & of that all subfolders.

  8. #8
    That is what it does,so you haven't explained why you think it is wrong.

  9. #9
    Hi bob,

    My requirement is as below...

    like we have a folder in "D:\" named VIJAY, we have three excel files and a folder named "Singh" in folder "VIJAY" & We three excel files in folder "Singh" & two folder in folder in "Singh" & both folder have two - two files each.

    & I just want to count all excel files

    We have 3 excel files in folder VIJAY Main folder "VIJAY"
    We have 3 excel files in folder SINGH Sub folder of VIJAY "SINGH"
    we have 2 - 2 files in each folder of Singh Two Sub folder of SINGH name anything can be a,bc
    so we have overall 10 files & 4 folders

    Please explain easy coding as I m a new learner OF VBA.

    Thanks

Posting Permissions

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