Opening a workbook

[Sam Ricca]

I have a code that opens a workbook with the name "CashVariance"
concatenated with the effective audit date, formatted as "mmddyy".
Therefore, if the audit date is 7/18/06, the file named
CashVariance071806.xls will open.

An upgrade to Crystal Reports saves the file as CashVariance &
"yyyy-mm-dd-hh-mm-ss".xls. The time is variable. Is there a way to modify my
code to open the file by looking only at the "yyyy-mm-dd" and ignoring the
"-hh-mm-ss"? I don't know how to utilize the left function in a file name or
use wildcards in the Workbooks.Open procedure.

Any help would be greatly appreciated.

Sam

 

[JMB]

One way you could approach it is to use filesearch. I just used today's date
in the filename, change to whatever date you need/want. Also, change .Lookin
to the proper folder. If there is more than one match, it will prompt the
user to select the proper file. Change to whatever you want it to do in that
case.


Sub Test()
Dim wkbTemp As Workbook
Dim FName As String

With Application.FileSearch
.NewSearch
.LookIn = "I:\Excel"
.SearchSubFolders = False
.Filename = "CashVariance" & _
Format(Date, "yyyy-mm-dd") & "*"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
Select Case .FoundFiles.Count
Case 0: MsgBox "File Not Found"
Case 1: Set wkbTemp = Workbooks.Open(.FoundFiles(1))
Case Else
FName = CStr(Application.GetOpenFilename)
If FName <> "" And UCase(FName) <> "FALSE" Then _
Set wkbTemp = Workbooks.Open(FName)
End Select
End With

End Sub