VBA Open Filename not working.

[Flailer]

I'm trying to use the open filename command to open a specific file for printing the problem is the filename is always changing, I've tried using wildcards but no success. The "00-00.xls" portion of the name changes randomly since the last two digits of the name are seconds, causing the open command to fail sometimes.
Any suggestions?

Thanks

The code:
Workbooks.Open Filename:="c:\DeltaV Reporter\Reports\Daily\daily." & Format(Date, "yyyy-mm-dd") & " " & Hour(Now()) & "-00-00.xls"

 

[AlphaFrog]

Try something like this (not tested)...

Dim strPath As String, strFileWild As String, strFileOpen As String

strPath = "C:\DeltaV Reporter\Reports\Daily\"
strFileWild = "daily." & Format(Now, "yyyy-mm-dd h-") & "*.xls"
strFileOpen = Dir(strPath & strFile)  'Test if file exists

If strFileOpen <> vbNullString Then
    Workbooks.Open Filename:=strPath & strFileOpen
Else
    MsgBox "Coludn't locate file:" & vbLf & strPath & strFileWild, vbExclamation, "File Not Found"
End If

 

[Flailer]

Thanks for the reply AlphaFrog. I'm still having an issue with the wildcard, the Msg Box shows that its trying to call up the file with the wild card in it.

daily.20110629-11-*.xls

any further thoughts?

 

[AlphaFrog]

Change this...
strFileOpen = Dir(strPath & strFile) 'Test if file exists

To this...
strFileOpen = Dir(strPath & strFileWild) 'Test if file exists

 

[Flailer]

Thanks AlphaFrog, the code works perfect.