• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

Move files using wildcard characters using Access 2007

Hi,

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
    Else
        MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
    End If
0
isurgyn
Asked:
isurgyn
  • 6
  • 3
3 Solutions
 
isurgynAuthor Commented:
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
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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
0
 
isurgynAuthor Commented:
One other thing.  The Patient ID number will come from a field on a form rather than being embedded in the code.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
isurgynAuthor Commented:
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.
0
 
Michael VasilevskySolutions ArchitectCommented:
Try:     file = "18814" & "%" & .pdf"

VBA uses "%" as the wildcard, not "*" for extra confusion I guess
0
 
Michael VasilevskySolutions ArchitectCommented:
Correction:  file = "18814" & "%" & ".pdf"

0
 
isurgynAuthor Commented:
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.
0
 
Michael VasilevskySolutions ArchitectCommented:
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.
0
 
isurgynAuthor Commented:
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

ErrHandler:

    If Err = 58 Then
        MsgBox "The Pentacam files have already been transfered", vbOKOnly, "File Already Transfered"
        Exit Sub
    End If
0
 
isurgynAuthor Commented:
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.
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now