Ms Access Get filename with wildcards or loop

Question

I am using MS Access Forms and I am trying to open a file but don't know how to open the file based knowing only part of the name. Example below works

Private Sub Open_Email_Click()
  On Error GoTo Err_cmdExplore_Click
  Dim x As Long
  Dim strFileName As String
  strFileName = "C:\data\office\policy num\20180926 S Sales 112.32.msg"
  strApp = """C:\Program Files\Microsoft Office\Office15\Outlook.exe"""
  If InStr(strFileName, " ") > 0 Then strFileName = """" & strFileName & """"
  x = Shell(strApp & " /f " & strFileName)
Exit_cmdExplore_Click:
  Exit Sub

Err_cmdExplore_Click:
  MsgBox Err.Description
  Resume Exit_cmdExplore_Click
End Sub

If I change the strFilename to being strFileName = "C:\data\" & Me.Office & "\" & Me.nm & " " & Me.pol & "\" & "*"& " S Sales " & Me.amt & "*" & ".msg" It includes the * rather than using it as a wildcard, the date/numbers can be anything or in another format but always eight numbers. I tried using a while loop on the numbers but I am not sure the best way of doing this sorry.

Erik A · Accepted Answer · 2018-09-28T13:00:08.650

4

You can use the Dir function to iterate over all files that match a string pattern.

strApp = """C:\Program Files\Microsoft Office\Office15\Outlook.exe""" 
Dim strFilePattern As String
strFilePattern ="C:\data\" & Me.Office & "\" & Me.nm & " " & Me.pol & "\" & "*"& " S Sales " & Me.amt & "*" & ".msg"

Dim strFileName As String
strFileName = Dir(strFilePattern)
Do While Not strFileName = vbNullString
    If InStr(strFileName, " ") > 0 Then strFileName = """" & strFileName & """" 
    x = Shell(strApp & " /f " & strFileName) 
    strFileName = Dir
Loop

The first call to Dir with the pattern as a parameter will find the first file that matches the pattern supplied. All subsequent calls without the pattern will return the next file that matches the pattern.

edited Sep 28 '18 at 13:00

answered Sep 28 '18 at 12:26

Erik A

31,639
12
42
67

1

Yeah, makes sense. But still, probably something like `Shell(QUOTE & fileName & QUOTE)` would look better I guess. And declaring `QUOTE` as constant. – Vityata Sep 28 '18 at 12:41
I tried this but asks for expected end of statement error so I removed dim from strFilePattern and StrFileName and nothing seems to happen. – New2Programming Sep 28 '18 at 12:59
@New2Programming Sorry, sloppy error there. Are you sure the pattern is valid? You can debug.print strFilePattern and verify files that match this pattern exist – Erik A Sep 28 '18 at 13:00
I just tried debug and it shows as * S Sales 112.31*.msg instead of the numbers – New2Programming Sep 28 '18 at 13:30
I assume that's a fully valid path, not just a filename? – Erik A Sep 28 '18 at 13:42
Please ignore me it does work I noticed I needed to add another wildecard as the pattern on some files are different now getting a cant open msg so need to look into this . many thanks. – New2Programming Sep 28 '18 at 13:44
Sorry just noticed debug still shows the * wildcards? is this correct? Thanks – New2Programming Sep 28 '18 at 14:15
`strFilePattern` should show the wildcards, `strFileName` only file names. – Erik A Sep 28 '18 at 14:16
My confusion sorry yes the filename is spot on! Just wish I knew why my msg file wont open guessing its trying to open it twice or something. thanks for al your help and sticking with me. – New2Programming Sep 28 '18 at 15:21

score 1 · Answer 2 · answered Sep 28 '18 at 12:27

So, lets rebuild the question a bit. Imagine that you are having the following 5 files in a given folder:

A:\peter.msg
A:\bstack.msg
A:\coverflow.msg
A:\heter.msg
A:\beter.msg

and you need to find the files, that correspond to "A:\*eter.msg" and print them. For this, you need to use the keyword Like:

Sub TestMe()

    Dim someNames As Variant
    someNames = Array("A:\peter.msg", "A:\bstack.msg", _
                "A:\coverflow.msg", "A:\heter.msg", "A:\beter.msg")

    Dim cnt As Long
    For cnt = LBound(someNames) To UBound(someNames)
        If someNames(cnt) Like "A:\*eter.msg" Then
            Debug.Print someNames(cnt)
        End If
    Next

End Sub

Loop through files in a folder using VBA?

Ms Access Get filename with wildcards or loop

2 Answers2