Unable to search/loop through subfolders

Question

Can any one help why I can't pickup file from sub-folders?

My code will locate locate and attach the file to an email if the file is in the main folder, but not if the file is located in sub-folders.

Code Sample:

Set obMail = Outlook.CreateItem(olMailItem)
With obMail
    .to = "email@comapny.com"
    .Subject = "O/S Blanace"
    .BodyFormat = olFormatPlain
    .Body = "Please see attached files"

    iRow = 24 'initialize row index from 24
    Do While Cells(iRow, 1) <> Empty

        'picking up file name from column A
        pFile = Dir(dPath & "\*" & Cells(iRow, 1) & "*")

        'checking for file exist in a folder and if its a pdf file
        If pFile <> "" And Right(pFile, 3) = "pdf" Then
            .Attachments.Add (dPath & "\" & pFile)
        End If

        'go to next file listed on the A column
        iRow = iRow + 1
    Loop

    .Send
End With

**Welcome to [so]!** -- Please [edit] your question to include only the code that is ***Minimal, Complete & Verifiable***: Use **as little code as possible that still produces the same problem** while providing **all the parts needed to reproduce the problem** (including data) and before posting it please **test the exact code & data** that you post, to make sure it reproduces the problem (with the data you're posting) . Having everything required to produce the problem (and nothing extra) makes it a lot easier for others to help you. **More Information: "[mcve]"** & "[help/on-topic]". — ashleedawg, Mar 05 '18 at 08:38
@PraSon - to confirm: column A your worksheet has a list of filenames that you need to attach, and the files are located either in the `dPath` *or* in an ***unknown*** subfolder, is that correct? Or is there another column with the *full* path of the files? — ashleedawg, Mar 07 '18 at 06:45
@PraSon - Excellent, that's much easier. I adjusted the indentation in your code sample and I think I know what you're trying to do now, and can probably help. `Dir` is not recursive, meaning it only checks the current folder, not subfolders... It's the equivalent of, on the command line (Start > Run > `cmd` ), using `Dir *.pdf` but **not** like `Dir *.pdf /s`.- anyhow, if you don't know the subfolder it's in, then you'l; need to use a recursive loop like [this](https://stackoverflow.com/q/22645347/8112776). If you DO know the folder then that needs to be added to `dPath`. — ashleedawg, Mar 07 '18 at 07:21
@ashleedawg first of all THANK YOU SO MUCH... you are the first one who helped me so far. For file listed in cell A24 is located under the same folder (sub-folder) location within the dpath folder location....Such as dpath location: C:\user\main-folder and sub-folder location: c:\ user \ main-folder \ sub-folder — PraSon, Mar 07 '18 at 09:54
@PraSon - You're welcome. This place takes some getting used to, but once you do, it's an incredible source of data, and all the many rules and "etiquette" starts to make sense. For example, *Shh! stop thanking me!* **Believe it or not** that's against the rules too -- see [this](https://meta.stackexchange.com/a/3021/370758)... (Actually that more applies to "noise" like please/thanks in the **posts** but comments aren't as big of a deal.) Personally I appreciate it though. :-) — ashleedawg, Mar 07 '18 at 10:02
@PraSon Can you add a screen shot of a small amount of sample data? Then I can give a definite answer. (change sensitive data if necessary, as long as it's *representative* of the actual data.) Just a few *different* rows of data along with the *actual* file *different* locations should be fine. — ashleedawg, Mar 07 '18 at 10:04
@ashleedawg just add screen shot of folder and sub-folder location where you will see the files listed in same folder as dpath and subfolder listed in the dpath folder. :) Also, its nice to know few rules (still new to this coding world) — PraSon, Mar 07 '18 at 22:35
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166437/discussion-between-prason-and-ashleedawg). — PraSon, Mar 08 '18 at 05:24

score 2 · Accepted Answer · answered Mar 07 '18 at 23:58

The Dir function doesn't traverse subfolders. It traverses the path you give it, not the tree structure. It also resets when called so calling recursively is not an option.

So if you pass it "C:\Test\" you can use it to traverse Test; if cell contains "C:\Test\NextTest\", you can use it to iterate over NextTest.

What you can do is use a Collection to hold each directory and explore recursively in that way.

For an example of how to do this see the following from How To Traverse Subdirectories with Dir

Sub TraversePath(path As String)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection

    currentPath = Dir(path, vbDirectory)

    'Explore current directory
    Do Until currentPath = vbNullString
        Debug.Print currentPath
        If Left(currentPath, 1) <> "." And _
            (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add currentPath
        End If
        currentPath = Dir()
    Loop

    'Explore subsequent directories
    For Each directory In dirCollection
        Debug.Print "---SubDirectory: " & directory & "---"
        TraversePath path & directory & "\"
    Next directory
End Sub


Sub Test()
    TraversePath "C:\Root\"
End Sub

You can easily adapt this to suit your purposes.

I have done this part and it does pass the test and prints the all the files name from folder and sub-folders in Immediate window. But I'm having bit difficulty applying to my current code. :( Not sure how to apply that to my above mail merge code — PraSon, Mar 08 '18 at 00:55

Unable to search/loop through subfolders

1 Answers1