CODEWITHSUNDEEP
vbams-access
New2Programming
New2Programming

Reputation: 383

Ms Access Get filename with wildcards or loop

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.

Upvotes: 0

Views: 1548

Answers (2)

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 4

Vityata
Vityata

Reputation: 43575

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?

Upvotes: 1

Related Questions