Finding a full file path from a wildcard

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,524
Try replacing your last line with:
VBA Code:
    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

Board Regular
Joined
Oct 26, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Try replacing your last line with:
VBA Code:
    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

Board Regular
Joined
Oct 26, 2015
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,524
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top