Solved

Find filename in directory to match components

Posted on 2012-03-26
3
418 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

827 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