VBA Access Get File Name with wildcard-CodePudding

I'm having a little trouble getting a filename using a wildcard. I'm using a wildcard because the filename has a "version" in it so in this case "Test v*" could be "Test v1" or "Test v2" because of this I want it to pull the name of whatever version is currently on the desktop.

Here is a simplistic version of my code. if ran you get "Test v*" rather than the actual file name. Not sure what I'm missing but any help would be greatly appreciated

Dim Owner As String
Dim Cver As String
Dim FileName As String

Owner = Environ("USERNAME")
FileName = "C:\Users\" & Owner & "\Desktop\TEST v" & "*" & ".accdb"
Cver = Left(FileName, InStr(FileName, ".") - 1)

MsgBox "" & Mid(Cver, 7, 2) & ""

CodePudding user response：

you are going to want to use Dir()

Here is my version of your code:

Sub getVersion()

    Dim owner As String
    Dim Cver As String
    Dim fileName As String

    Dim owner As String: owner = Environ("USERNAME")

    Dim fileName As Variant: fileName = Dir("C:\Users\" & owner & "\Desktop\TEST v" & "*" & ".accdb", vbDirectory)

    Cver = Left(fileName, InStr(fileName, ".") - 1)

    MsgBox "" & Mid(Cver, 7, 2) & ""
End Sub