Count_files_through VBA

[vsy060687]

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

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

[Zack Barresse]

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

[Bob Phillips]

This works for me

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

[vsy060687]

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

[vsy060687]

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

[Bob Phillips]

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.

[vsy060687]

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.

[Bob Phillips]

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

[vsy060687]

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