Solved

Find filename in directory to match components

Posted on 2012-03-26
3
411 Views
Last Modified: 2012-03-27
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!
0
Comment
Question by:terpsichore
3 Comments
 
LVL 12

Expert Comment

by:danishani
ID: 37769706
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
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 37769721
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
 

Author Closing Comment

by:terpsichore
ID: 37770948
simple, works great
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question