ScatmanKyle
Board Regular
- Joined
- Oct 26, 2015
- Messages
- 65
- Office Version
- 365
- Platform
- 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)
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