Finding a full file path from a wildcard

[ScatmanKyle]

Hello again MrExcel Forum!

I get a ton of pdf invoices from a vendor as well as a weekly statement of what is outstanding. My long-term goal is to be able to put all the pdfs in one folder, then use a macro to go down the statement, identify what pdfs I currently have from the statement, and sort them into the appropriate folder based on a set of rules.

My current ask is trying to find the full file path of a pdf given that it may be in one of the subfolders and that the file names always include the invoice number but may include other characters. For example, an invoice could be "BDI123456.pdf" or it could be "CDS-BDI123456.pdf" or "BDI123456-Invoice.pdf"

I'd like to use the invoice number on the statement ("BDI123456") to return the full file path including the file name (i.e. "C:\users\user\desktop\CDS Folder\CDS-FG\BDI123456-invoice.pdf"

I've gotten to where I can find the full file name itself, but not the directory (in case of it being in a subfolder)

Sub InvoiceFind ()

Dim folderExplorer As FileDialog
Dim strFolderPath As String
Dim strInvoiceNum As String
Dim strFileName As String

'Lets you choose the folder with CDS invoices in it
With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then
        strFolderPath = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With

'Creates sorting folders in the CDS invoice directory if they're not already there
If Dir(strFolderPath & "\CDS-FG", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-FG")
End If
If Dir(strFolderPath & "\CDS-LM", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-LM")
End If
If Dir(strFolderPath & "\CDS-POS", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-POS")
End If
If Dir(strFolderPath & "\CDS-OTH", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-OTH")
End If
If Dir(strFolderPath & "\CDS-WTM", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-WTM")
End If
If Dir(strFolderPath & "\CDS-iVerify", vbDirectory) = "" Then
    MkDir (strFolderPath & "\CDS-iVerify")
End If

'Retrieves and stores the file path and name of the current invoice
strInvoiceNum = "BDI123456"
strFileName = Dir(strFolderPath & "\*" & strInvoiceNum & "*")

End Sub

 

[John_w]

Try replacing your last line with:

    Dim subfolder As Variant, invoiceFileName As String, invoiceFullName As String

    invoiceFullName = ""
    invoiceFileName = Dir(strFolderPath & "\*" & strInvoiceNum & "*")
    If invoiceFileName <> vbNullString Then
        invoiceFullName = strFolderPath & "\" & invoiceFileName
    Else
        For Each subfolder In Array("CDS-FG", "CDS-LM") 'add the other subfolders!
            invoiceFileName = Dir(strFolderPath & "\" & subfolder & "\*" & strInvoiceNum & "*")
            If invoiceFileName <> vbNullString Then
                invoiceFullName = strFolderPath & "\" & subfolder & "\" & invoiceFileName
                Exit For
            End If
        Next
    End If
   
    If invoiceFullName <> "" Then
        MsgBox "Invoice " & strInvoiceNum & " found:" & vbCrLf & invoiceFullName
    Else
        MsgBox "Invoice " & strInvoiceNum & " not found"
    End If

 

[ScatmanKyle]

Try replacing your last line with:

    Dim subfolder As Variant, invoiceFileName As String, invoiceFullName As String

    invoiceFullName = ""
    invoiceFileName = Dir(strFolderPath & "\*" & strInvoiceNum & "*")
    If invoiceFileName <> vbNullString Then
        invoiceFullName = strFolderPath & "\" & invoiceFileName
    Else
        For Each subfolder In Array("CDS-FG", "CDS-LM") 'add the other subfolders!
            invoiceFileName = Dir(strFolderPath & "\" & subfolder & "\*" & strInvoiceNum & "*")
            If invoiceFileName <> vbNullString Then
                invoiceFullName = strFolderPath & "\" & subfolder & "\" & invoiceFileName
                Exit For
            End If
        Next
    End If
  
    If invoiceFullName <> "" Then
        MsgBox "Invoice " & strInvoiceNum & " found:" & vbCrLf & invoiceFullName
    Else
        MsgBox "Invoice " & strInvoiceNum & " not found"
    End If

It works! Is there a downside to using "" instead of vbNullString?

 

[ScatmanKyle]

Also since it searches the subfolders, what if it's in the main folder? Is there a way to add that to the array or do I just put an if statement to search the main folder first?

 

[John_w]

In the past I've found that when Dir returns "" it doesn't always mean 'file not found', so now I check for vbNullString.

The first Dir searches the main folder. If not found there, the second Dir searches the subfolders.