Open pdf file looping through folders and subfolders

Question

I wrote a macro that uses Beforedoubleclick() range: You make a double click on a cell of column 5, and the macro goes into a specific folder to find the pdf file that have the code written inside the cell.

This macro isn't able to scan the subfolders but my pdf files could be located in subfolders.

I searched in the web and seems I have to use a loop or something like that. I don't know how to write this piece of code.

My macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim testo As String
Dim nomefile As String
Dim path As String

On Error Resume Next

If Target.Column = 5 Then

    path = "C:\Users\Alex\"
    testo = path & Cells(Target.Row, 5)
    nomefile = Dir(Left(testo, Len(testo)) & "*.pdf")

    If nomefile = "" Then
        MsgBox "File non trovato", vbCritical, "ATTENZIONE"
        Exit Sub
    End If

    Do
        Shell "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe " & path & nomefile, vbMaximizedFocus
        nomefile = Dir
    Loop While nomefile <> ""
End If
End Sub

You should check out how to work with the `FileSystemObject` or `FSO` that is what you need here. — Damian, Mar 18 '19 at 16:54
There are plenty of examples in SO for doing recursive searches through sub-folders. And if you use the `Cmd` window `Dir` command, as shown in [this question](https://stackoverflow.com/questions/53713804/recursive-search-through-subfolders-back-to-root-directory), you can even include wild cards in your string. Although, if your file names include characters with a code > 255, you may need to read them with something other than the fileSystemObject commands. — Ron Rosenfeld, Mar 18 '19 at 17:21

score 0 · Answer 1 · answered Mar 18 '19 at 17:29

Try something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim testo As String
    Dim FSO As Object
    Dim Folder As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")

    Set Folder = FSO.GetFolder("C:\Users\Alex\")
    ...
    ...
    testo = path & Cells(Target.Row, 5)
    nomefile = FindFile(testo, Folder, FSO)
End Sub

Function FindFile(FileName As String, Folder As Object, ByRef FSO As Object) As String
    DoEvents

    If FSO.FileExists(Folder & "\" & FileName & ".pdf") Then
        FindFile = Folder & "\" & FileName & ".pdf"
        Exit Function
    End If

    Dim SubFolder As Object
    For Each SubFolder In Folder.SubFolders
        FindFile = FindFile(FileName, SubFolder, FSO)
    Next
End Function

The FindFile function starts with a folder. I the sought file is there, good - return it. If not, take every sub folder and call FindFile and try. The DoEvents is there since it will be quite hard to interrupt otherwise.

score 0 · Answer 2 · answered Mar 19 '19 at 07:45

Thanks for your suggestion Sam; I try to adapt your code to my case and I wrote this:

Function FindFile(FileName As String, Folder As Object, ByRef FSO As Object) As String
DoEvents

If FSO.FileExists(Folder & "\" & FileName & "*.pdf*") Then
    FindFile = Folder & "\" & FileName & "*.pdf*"
    Exit Function
End If

Dim SubFolder As Object
For Each SubFolder In Folder.SubFolders
    FindFile = FindFile(FileName, SubFolder, FSO)
Next

End Function

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim testo As String
Dim nomefile As String
Dim FSO As Object
Dim Folder As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\Alex")
If Target.Column = 5 Then
    testo = Cells(Target.Row, 5)
    nomefile = FindFile(testo, Folder, FSO)

    If nomefile = "" Then
        MsgBox "File not found!", vbCritical, "WARNING"
        Exit Sub
    End If

    CreateObject("WScript.Shell").Run nomefile
End If

End Sub

The problem is the file I'm looking for (named "Test.pdf") is not located directly in "Alex" folder, but in a subfolder (...\Alex\TestFolder\"). The macro always returns the MsgBox "File not found!". What is wrong in my code?

Thanks, Alex

Open pdf file looping through folders and subfolders

2 Answers2