hmm i may have over thought...though i am unsure of duplication would be an issue but
the search string can override the need to write in files
Sub FName()
Dim strFileName As String
Const strFolder As String = "C:\Downloads\" 'Directory
Dim pos As Long
Dim InvNum As String
Dim strSearch As String
strFileName = Dir(strFolder & "*.pdf") 'list filenames only with .pdf
i = 7 'starting row
Range("B7:D50").ClearContents 'clear previous searchs ...extend pass 50 if you have more than 50
strSearch = Range("B4").Value 'search term
Do While strFileName <> vbNullString 'loop for filenames in folder
If InStr(strFileName, strSearch) > 0 Then 'if search term found in filename then go ahead
pos = InStr(strFileName, "-") 'position of first -
If pos <> 0 Then ' check delimiter is found
Cells(i, 2).Value = Left(strFileName, pos - 1) 'Name
InvNum = Mid(strFileName, pos + 2) 'inv num - date.pdf
Cells(i, 4).Value = Replace(Right(InvNum, Len(InvNum) - InStr(InvNum, "-")), ".pdf", "") 'Date minus the .pdf
Cells(i, 3).Value = Left(InvNum, InStr(InvNum, "-") - 1) 'extract Inv Num
End If
i = i + 1 ' increment row
End If
strFileName = Dir 'next file
Loop
End Sub
instr is case sensitive if that is an issue need to UPPER or LOWER everything first
Bookmarks