Search For File In Directory Using Wildcard Works, But Now To Get The Complete Name Of That File

[Ark68]

This code checks to see if a file (tar_str) exists in a directory.

Sub cheese()
    

    sdate = 43609 'May 24 2019
    EDate = 43757 'Oct 20 2019
    Days = 148
    For lp = sdate To EDate
        t_mon = Format(lp, "mmm")
        t_day = Day(lp)
        t_dayy = Format(lp, "ddd")
        tar_str = t_mon & "-" & t_day & " (" & t_dayy & ") Schedule_*"
        srcfile = tar_str
        
        strFileName = srcpath & srcfile
        strFileExists = Dir(strFileName)
 
        If strFileExists = "" Then
            MsgBox "The selected file doesn't exist"
        Else
            MsgBox "The selected file exists"
        End If
        lp = lp + 1
    Next lp
     
End Sub

"tar_str" consists of a trailing wildcard as it will locate the one file (if it exists) in the directory that is of the flavour "_3.xlsx", "_5.xlsx" or "_6.xlsx".

So, now that such a file is determined to exist, how can I get that file's full name (to include the portion represented by the asterisk)? I need to now open it, and I can't open a workbook with a wildcard.

 

[John_w]

strFileExists is the actual file name returned by Dir, so you need to prepend the path to open it:

        strFileExists = Dir(strFileName)
 
        If strFileExists = "" Then
            MsgBox "The selected file doesn't exist"
        Else
            MsgBox "The selected file exists"
            Workbooks.Open srcpath & strFileExists
        End If

 

[daverunt]

Just a quick look.
I think the wildcard as used will return the Schedule_* and not a filename.
I could be wrong. srcpath isn't defined

tar_str = Dir(srcpath & t_mon & "-" & t_day & " (" & t_dayy & ") Schedule_*")

 

[John_w]

Yes, OP you need to define the srcpath string, assuming it isn't a global variable defined elsewhere. I advise you to always have Option Explicit at the top of modules.

Dir returns a complete file name.

 

[Ark68]

Ahhh. I see. Thanks.