VBA Loop through all files in all subfolders

[Vanda_a]

Dear all

Sub LopFolder()

    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder
    Dim MyPath As String, MyFile As String, File As Workbook

    Set f = fso.GetFolder("E:\Da\")
    For Each sf In f.SubFolders
        MyFile = Dir(sf & "\" & "*.xls*") ''''''''''''''''Loop through all this files before goto next sf.
        MyFile = Dir()
    Next

End Sub

I am struggling no loop through all files first before next sf. Could someone help please.
I have tried this but not work

For Each File in sf

Thank you very much

 

[V_Malkoti]

Try this:

Sub LoopFolder()


    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder
    Dim MyPath As String, MyFile As String, File As Workbook


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("C:\TestingResults\BBR\")
    For Each sf In f.SubFolders
[COLOR=#0000ff]        For Each f In sf.Files[/COLOR]
[COLOR=#0000ff]            If fso.GetExtensionName(f.Path) = "xls" Then[/COLOR]
[COLOR=#0000ff]                '' process here[/COLOR]
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
    Next


End Sub

 

[Vanda_a]

Try this:

Sub LoopFolder()


    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder
    Dim MyPath As String, MyFile As String, File As Workbook


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("C:\TestingResults\BBR\")
    For Each sf In f.SubFolders
[COLOR=#0000ff]        For Each f In sf.Files[/COLOR]
[COLOR=#0000ff]            If fso.GetExtensionName(f.Path) = "xls" Then[/COLOR]
[COLOR=#0000ff]                '' process here[/COLOR]
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
    Next


End Sub

Thank you so much. I don't understand one thing. Could you please bright me up.

Dim f As Folder
For Each f in sf.Files

It shd be like this.
Dim f as workbook or file. Then for each f(workbook or file) in sf.Files

 

[V_Malkoti]

Ah I didn't notice your declaration part. Rewrote sub with variable declarations:

Sub LopFolder()


    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder
[COLOR=#0000ff]    Dim ofile As File[/COLOR]
    Dim MyPath As String, MyFile As String, File As Workbook


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("C:\TestingResults\BBR\")
    For Each sf In f.SubFolders
[COLOR=#0000ff]        For Each ofile In sf.Files[/COLOR]
[COLOR=#0000ff]            If fso.GetExtensionName(ofile.Path) = "xls" Then[/COLOR]
[COLOR=#0000ff]                Debug.Print ofile.Name[/COLOR]
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
    Next


End Sub

 

[Vanda_a]

Ah I didn't notice your declaration part. Rewrote sub with variable declarations:

Sub LopFolder()


    Dim fso As New FileSystemObject
    Dim f As Folder, sf As Folder
[COLOR=#0000ff]    Dim ofile As File[/COLOR]
    Dim MyPath As String, MyFile As String, File As Workbook


    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("C:\TestingResults\BBR\")
    For Each sf In f.SubFolders
[COLOR=#0000ff]        For Each ofile In sf.Files[/COLOR]
[COLOR=#0000ff]            If fso.GetExtensionName(ofile.Path) = "xls" Then[/COLOR]
[COLOR=#0000ff]                Debug.Print ofile.Name[/COLOR]
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        Next[/COLOR]
    Next


End Sub

Cool thank you very much