Move files using wildcard characters using Access 2007

Posted on 2011-10-25
Medium Priority
Last Modified: 2012-06-27

I am needing to move files from a folder on a local computer to a folder on the server.  The files on the local device are created by that system.  The file naming protocol on the local device is to use the patient ID number followed by the time and date that the file was created (ie.  18814_10252011_1052.jpg)

I need the code to find all files in the folder that include the patient's ID number (in the example 18814) and move them to the server so they can be accessed by the database application.  To do this I need to modify the following code so that the files are recognized using wildcard characters for the time and date as I won't necessarily know the date or the specific time that the file was created.  Thanks.

    Dim fso
    Dim file As String, sfol As String, dfol As String
    file = "18814" & "*.pdf" ' THE SYNTAX HERE IS WHERE I HAVE THE PROBLEM"
    sfol = "C:\Patient Files\"
    dfol = "Z:\Server\Patient Files\"
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(sfol & file) Then
        MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"
    ElseIf Not fso.FileExists(dfol & file) Then
        fso.MoveFile (sfol & file), dfol
        MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
    End If
Question by:isurgyn
  • 6
  • 3

Author Comment

ID: 37027352
So the code works fine if I provide the correct file name.  It needs to be modified to look for wildcard characters in the file name.  Thanks
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 248 total points
ID: 37027355
That syntax looks okay to me.  What you're doing is basically

MOVE 18818*.pdf destination

You could do the same thing with

MOVE 18818_????????_????.pdf destination

Author Comment

ID: 37027401
One other thing.  The Patient ID number will come from a field on a form rather than being embedded in the code.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 37028512
Hey Paul,

The error message I am receiving is

C:\Patient Files\18814*.pdf does not exist!

If I remove all of the other part of the name and eliminate the wildcard * then the code runs fine and moves the file.

I will try your Move command and see if it will work.
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37028531
Try:     file = "18814" & "%" & .pdf"

VBA uses "%" as the wildcard, not "*" for extra confusion I guess
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37028538
Correction:  file = "18814" & "%" & ".pdf"


Author Comment

ID: 37028603
OK now I get the same error

C:\Patient Files\18814%.pdf does not exist!

I think that you are onto something with the wildcard character but the code doesn't recognize it as a wild character but rather as a real character when it is in quotation marks.  There must be another part to the syntax to tell the compiler to view the wild character as wild.
LVL 10

Assisted Solution

by:Michael Vasilevsky
Michael Vasilevsky earned 252 total points
ID: 37028638
I don't think the function FileExists() supports wildcards then unfortunately. You'll probably have to import all the file names to a table, and then look for that string using SQL.

Accepted Solution

isurgyn earned 0 total points
ID: 37029124
Thanks guys.  I figured out the right way to code this process.  Took a bit of surfing for code but this works perfectly.  The patient name and patient ID are coming from a field on the open Access form.

    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileName As String
    Dim PatientName As String
    Dim PatientID As String
    Dim FileInFromFolder As Object
    On Error GoTo ErrHandler
    PatientID = Me.PatientID
    PatientName = Me.First & " " & Me.Middle & " " & Me.Last

    FromPath = "C:\Patient Files\"  
    ToPath = "Z:\Server\PatientFiles\"

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    If Right(ToPath, 1) <> "\" Then
        ToPath = ToPath & "\"
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    If FSO.FolderExists(ToPath) = False Then
        MsgBox ToPath & " doesn't exist"
        Exit Sub
    End If

    For Each FileInFromFolder In FSO.getfolder(FromPath).Files
        FileName = (FileInFromFolder.Name)

        If FileName Like PatientID & "*" Then
            FileInFromFolder.Move ToPath
        End If
    Next FileInFromFolder

    MsgBox "The documents from the Pentacam for " & PatientName & " have been successfully moved from " & FromPath & " to " & ToPath


    If Err = 58 Then
        MsgBox "The Pentacam files have already been transfered", vbOKOnly, "File Already Transfered"
        Exit Sub
    End If

Author Closing Comment

ID: 37052399
The comments added by other experts did not seem to lead to an appropriate solution for the stated problem.  The key was to create an IF THEN statement using the Like syntax.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

839 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