isurgyn
asked on
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.Fi
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
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.Fi
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One other thing. The Patient ID number will come from a field on a form rather than being embedded in the code.
ASKER
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.
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.
Try: file = "18814" & "%" & .pdf"
VBA uses "%" as the wildcard, not "*" for extra confusion I guess
VBA uses "%" as the wildcard, not "*" for extra confusion I guess
Correction: file = "18814" & "%" & ".pdf"
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER