Trying to use a wildcard (*) in access vba to match filename.

Here is my code;

If FSObj.FileExists("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3") Then

MoveFileToUnProven

Else

MsgBox "The application was unable to locate a program for this tool sheet. " & _
"You will need to manually move the program from the Server to the unproven folder. " & _
"Be sure the Tool Sheet # does not have the revision or hd3 in the name before closing the Tool Sheet back out."
...

Basically the filename I'm trying to get it to match is 006.hd3

I want it to beable to match it using Forms!frmHurcoSetups.ProgNum which is 006 and a wildcard which in this case will be nothing, and ".hd3".

It's not finding the filename with my code setup the way it is though.

Anyone got any ideas as to how I can tweak this so that it will find the file?
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
If you need to get the actual filename, Dir("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3") will return the file name of the first match and successive calls to Dir()  without the filename argument will return any additional matching file names.

You can retrieve the first matching filename like this:

Dim strFirstMatch as string

strFirstMatch =  Dir("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3")  & "" 
if strFirstMatch <> "" Then
    msgbox strFirstMatch
    ' do something with strFirstmatch
    MoveFileToUnProven

Else
MsgBox "The application was unable to locate a program for this tool sheet. " & _
"You will need to manually move the program from the Server to the unproven folder. " & _
"Be sure the Tool Sheet # does not have the revision or hd3 in the name before closing the Tool Sheet back out."
End if

Open in new window

0
 
mbizupCommented:
try this:

IF Dir("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3")  & "" <> "" Then
    ' File exists
Else
    ' No file/wildcard matches
End if

Open in new window

0
 
Jarred MeyerProduction ManagerAuthor Commented:
That fixed that.. Could you perhaps give me some input on the move module as well? I didn't think about that part. The code for it was;

    FSObj.MoveFile "\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & Forms!frmHurcoSetups.Revision & ".hd3", _.....

So I need to change it to something that works just like the above code you gave;
I tried this as you gave me above but I guess that doesn't work for this scenario?
FSObj.MoveFile ("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3") & "" <> "", _

Any thoughts? I can close this one out in any case and start a new questions on this part if need be:)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Jarred MeyerProduction ManagerAuthor Commented:
Yeh, I just want the first match.. I will tweak that to reflect your changes. Thanks!
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Oh. I think I could just incorporate my MoveFileToUnproven code into this code and use the strFirstMatch as my movefile?
0
 
mbizupCommented:
Exactly...!

Do you need help with that?
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Private Sub cmdOpenProg_Click()

Dim FSObj       As FileSystemObject
Set FSObj = New FileSystemObject
Dim strFirstMatch As String


On Error Resume Next

DoCmd.OpenForm "frmHurcoSetups", , , "[KeyField] = '" & Me.KeyField & "'"

MkDir "\\sfile0\e\CNC\CNC SET UP PHOTOS\iPad Setup Photos\Hurco\" & Me.KeyField & "\"

Forms!frmHurcoSetups.JobOpen = True
Forms!frmHurcoSetups.Machine = Me.cboMachine

strFirstMatch = Dir("\\sfile0\e\cnc\vm2\" & Forms!frmHurcoSetups.ProgNum & "*" & ".HD3") & ""
If strFirstMatch <> "" Then

MsgBox strFirstMatch

    FSObj.MoveFile ("\\sfile0\e\cnc\vm2\" & strFirstMatch), _
         "\\mikem\g\" & Forms!frmSplash.cboMachine & "\unproven\" & strFirstMatch

Else

MsgBox "The application was unable to locate a program for this tool sheet. " & _
"You will need to manually move the program from the Server to the unproven folder. " & _
"Be sure the Tool Sheet # does not have the revision or hd3 in the name before closing the Tool Sheet back out."

End If

End Sub

Open in new window


This got the move part working.. Just incorporated it into the rest of the first code. This is the working complete code.
0
 
mbizupCommented:
<< I can close this one out in any case and start a new questions on this part if need be:)  >>

No need to do that... give it a try and post back with your code if you run into trouble with it.
0
 
mbizupCommented:
Ah - looks like we cross-posted.  Glad you've got it sorted.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I'm going to ask a related question on how to have the code pull the last matching file it finds instead of the first.. It seems I had a lot more duplicates than I thought and the last match will always be the most recent.
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.

All Courses

From novice to tech pro — start learning today.