Looking through a folder/subfolders to find and print a list of pdfs

Question

I have a list of pdfs which are located in one folder and subfolders within that folder. I would like to be able to have a macro that goes down the list and prints each of the pdf's after finding them in the folders.

The list of pdf names in the excel sheet start on B3 and go down. The look like "10028844" while the pdf's are saved with the same name "10028844.pdf".

I've looked all around and have found many examples on how to look through folders for ALL files in the folder/subfolder but none that look for specific files or a list of files. Any help is appreciated.

I've found some articles that helped with some code by adding the files to a collection but when I run this macro there is nothing in the collection. Does anyone see where this is going wrong?

Sub GetFiles(StartFolder As String, Pattern As String, _
         DoSubfolders As Boolean, ByRef colFiles As Collection)

Dim f As String, sf As String, subF As New Collection, s

If Right(StartFolder, 1) <> "\" Then StartFolder = StartFolder & "\"

f = Dir(StartFolder & Pattern)
Do While Len(f) > 0
    colFiles.Add StartFolder & f
    f = Dir()
Loop

sf = Dir(StartFolder, vbDirectory)
Do While Len(sf) > 0
    If sf <> "." And sf <> ".." Then
        If (GetAttr(StartFolder & sf) And vbDirectory) <> 0 Then
                subF.Add StartFolder & sf
        End If
    End If
    sf = Dir()
Loop

For Each s In subF
    GetFiles CStr(s), Pattern, True, colFiles
Next s

End Sub

.

Sub BatchPrint()

Dim colFiles As New Collection
Dim CustRow, LastRow As Long

LastRow = Sheet1.Range("B9999").End(xlUp).Row

With Sheet1

For CustRow = 3 To LastRow

GetFiles "C:\Users\Desktop\Test\", "B" & CustRow & ".pdf", True, colFiles
If colFiles.Count > 0 Then
'work with found files
End If

Next CustRow

End With

Dim i As Long
For i = 1 To colFiles.Count
Debug.Print colFiles(i)
Next i

End Sub

If you have the list of file names, why do you need to *find* each file? — BigBen, Jul 13 '20 at 16:12
You can use `Dir(fullPathHere)` to check the file exists before printing it. — Tim Williams, Jul 13 '20 at 16:16
@BigBen Even though I have the name of each file, the folder/subfolders have thousands of files in them and to find each one takes a while. Where if I had a macro to look through and print them it would save a lot of time. — JoshL, Jul 13 '20 at 16:18
I guess to me it sounds like you already know the filepath, so the code doesn't need to "find" the file. It just needs to open and print. But `Dir` could be helpful as Tim points out. — BigBen, Jul 13 '20 at 16:20
I know the 'general' path of the file. Though it could be in any of the subfolders so I don't know the exact path. — JoshL, Jul 13 '20 at 16:21
https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders/20688126#20688126 — Tim Williams, Jul 13 '20 at 16:49
@TimWilliams The list can go anywhere from 5 to 50, and there are over 100,000 files in the folder/subfolders. I'll take a look at that link, Thank you! — JoshL, Jul 13 '20 at 16:52
Hopefully there's some logic to the folder structure which will allow you to narrow the search... — Tim Williams, Jul 13 '20 at 16:54
If it were up to me I would definitely reorganize this but I am not in control.. :/ — JoshL, Jul 13 '20 at 17:01

score 0 · Accepted Answer · answered Jul 14 '20 at 20:14

After some work I finally got this to work. Code below. Choose what happens to the collection by changing the line Debug.Print colFiles(i).

Sub GetFiles(StartFolder As String, Pattern As String, _
         DoSubfolders As Boolean, ByRef colFiles As Collection)

Dim f As String, sf As String, subF As New Collection, s

If Right(StartFolder, 1) <> "\" Then StartFolder = StartFolder & "\"

f = Dir(StartFolder & Pattern)
Do While Len(f) > 0
    colFiles.Add StartFolder & f
    f = Dir()
Loop

sf = Dir(StartFolder, vbDirectory)
Do While Len(sf) > 0
    If sf <> "." And sf <> ".." Then
        If (GetAttr(StartFolder & sf) And vbDirectory) <> 0 Then
                subF.Add StartFolder & sf
        End If
    End If
    sf = Dir()
Loop

For Each s In subF
    GetFiles CStr(s), Pattern, True, colFiles
Next s

End Sub

.

Sub BatchPrint()

Dim colFiles As New Collection
Dim CustRow, LastRow As Long

Set colFiles = New Collection

LastRow = Sheet1.Range("B9999").End(xlUp).Row

With Sheet1

For CustRow = 3 To LastRow

GetFiles "C:\Users\Desktop\Test\", Sheet1.Range("B" & CustRow) & ".pdf", True, colFiles

Next CustRow

End With

Dim i As Long
For i = 1 To colFiles.Count
    Debug.Print colFiles(i)
Next i

Set colFiles = Nothing

End Sub

Looking through a folder/subfolders to find and print a list of pdfs

1 Answers1