Find filename in directory to match components

Dear experts -
I am trying to: a) determine if a file exists in a given directory; and b) store the full name of that file (with path) to a variable.

Specifically, I want to see if there is a file in a given directory where: a) the first part of the filename; and b) end part of the filename match a given pattern (for example, in the directory: "J:\purchase orders\" is there a file that begins with the string "2001_ABC1" and ends with the string "_07.pdf".

Any thoughts are greatly appreciated!
terpsichoreAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
Here is a simple method using the VBA Dir() and a wildcard


Const strDir As String = "J:\purchase orders\"

Dim strFileName As String

strFileName = Dir(strDir & "2001_ABC1*_07.pdf")

Do Until strFileName = ""

   ' do some3hting with full path
   ' as test send to immediate window
   Debug.Print strDir & strFileName

   strFileName = Dir()
Loop

Open in new window

0
 
danishaniCommented:
Try this, late binding version of using the FileSystemObject:

Private Sub Command13_Click()

Dim SourceFolderName As String

Dim fso As Object
Dim SourceFolder As Object
Dim Subfolder As Object
Dim FileItem As Object

Dim myVar As String


SourceFolderName = "C:\YourPathNameHere\"

Set fso = CreateObject("Scripting.filesystemobject")


On Error GoTo Err_Handler
   
    Set SourceFolder = fso.GetFolder(SourceFolderName)
   
    For Each FileItem In SourceFolder.Files
       If Right(FileItem.Name, 7) = "_07.pdf" And Left(FileItem.Name, 9) = "2001_ABC1" Then
       
       myVar = FileItem.Path
       Debug.Print myVar
                   
       End If
   
    Next
   
Exit_Handler:
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set fso = Nothing
   
    Exit Sub
   

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler


End Sub
0
 
terpsichoreAuthor Commented:
simple, works great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.