Find file w/ unknown part

[Atchleykl]

I have a file with the following naming convention:

fileYYYYMMDD.xls

the year and month is always the current year and month. I know how to pull
a file that way. My problem is the day is different everytime. I know I can
use this it it was just YYYYMM:

"\\path\file & yr & mo &".txt"
where yr & mo are calculations to give the current year and month.

Is there a way to add a wild card after the mo variable and before the ".txt"?

Any help would be greatly appreciated! Thanks

 

[Paolo]

Hi Atchleykl,

I think you can use the star as follow
"\\path\file & yr & mo &"*.txt"

HTH Paolo

 

[Klatuu]

=Format(Date,"yyyymm") & "*.txt"

 

[Atchleykl]

That didn't work. It made the * part of the file name. Any other
suggestions? Thanks

 

[Dirk Goldgar]

That didn't work. It made the * part of the file name. Any other
suggestions? Thanks

What's your code doing with the constructed path containing the wild-card
character? You can't just open it and expect to get the first (or only)
file that matches. You need to use the Dir() function to get the actual
matching filename. For example:

'----- start of example code -----
Dim strFolder As String
Dim strFileName As String

strFolder = \\Your Folder Path\
' Note: that path should be enclosed in quotes, but my
' newsreader insturning it into a URL.

strFileName = Dir(strFolder & "file" & yr & mo & "??.txt")

If Len(strFileName) > 0 Then
strFileName = strFolder & strFileName ' prepend folder path

' ... Open or process the file ...

Else
' There's no matching file. Do something appropriate.
End If
'----- end of example code -----

Note that the above code will only find the first matching file. If there
are more than one that would match, you have to decide if you want to do
something with all of them. In that case, repeated calls to the Dir()
function with no argument will return each file in turn.

 

[Klatuu]

Making * part of the name was intended to be used for searching for matching
file names. For example, you could use it for the Dir function to find all
matching files for the month:

Dim strFindPath As String
Dim strFoundFile as String
Dim blnSelected As Boolean

strFindPath = "\\path\" & Format(Date,"yyyymm") & "*.txt"
strFoundFile = Dir(strFindPath)
If strFoundFile = vbNullString Then
MsbBox "No Matching Files Found"
Else
Do While strFoundFile <> vbNullString
If MsgBox(" Found " & strFoundFile & vbNewLine & "Use This
File", _
vbQuestion + vbYesNo) = vbYes Then
blnSelected = True
Exit Do
End If
Loop
If Not blnSelected Then
MsgBox "No File Selected"
End If
End If

 

[Atchleykl]

It didn't work, Access is treating the * as part of the file name:
fileYYYMM*.txt instead of treating it like a wildcard. I have this
programmed in function in a Access module, does that make a difference. Any
other suggestions? Thanks.

 

[Atchleykl]

Thank you! This worked perfectly!

Making * part of the name was intended to be used for searching for matching
file names. For example, you could use it for the Dir function to find all
matching files for the month:

Dim strFindPath As String
Dim strFoundFile as String
Dim blnSelected As Boolean

strFindPath = "\\path\" & Format(Date,"yyyymm") & "*.txt"
strFoundFile = Dir(strFindPath)
If strFoundFile = vbNullString Then
MsbBox "No Matching Files Found"
Else
Do While strFoundFile <> vbNullString
If MsgBox(" Found " & strFoundFile & vbNewLine & "Use This
File", _
vbQuestion + vbYesNo) = vbYes Then
blnSelected = True
Exit Do
End If
Loop
If Not blnSelected Then
MsgBox "No File Selected"
End If
End If