?
Solved

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

Posted on 2012-09-12
10
Medium Priority
?
1,467 Views
Last Modified: 2012-09-12
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?
0
Comment
Question by:SeyerIT
  • 5
  • 5
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38390601
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38390633
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 38390639
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:SeyerIT
ID: 38390647
Yeh, I just want the first match.. I will tweak that to reflect your changes. Thanks!
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 38390656
Oh. I think I could just incorporate my MoveFileToUnproven code into this code and use the strFirstMatch as my movefile?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38390689
Exactly...!

Do you need help with that?
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 38390696
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38390707
<< 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
 
LVL 61

Expert Comment

by:mbizup
ID: 38390720
Ah - looks like we cross-posted.  Glad you've got it sorted.
0
 
LVL 2

Author Closing Comment

by:SeyerIT
ID: 38390760
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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

755 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