Excel VBA to open a workbook that contains a string

[Kaniguan1969]

Hi,

I have a workbook that save in a folder. I wanted to search that file and open using excel VBA.

Below is the code i'm using. Btw, the report file is generated through SSRS daily and exported to excel. the filename has a timestamp.
How could i open that file using the string "BDO BPI Report" thank you in advance.




Sub SearchFile()
Dim FilePath As String
Dim wb as WOrkbook
Dim ws as WorkSheet

With Application
.DisplayAlerts = False
.AskToUpdateLinks = False
.EnableEvents = False
End With

'The file that i wanted to open is generated by an SSRS report
'the file name has a timestamp
FilePath = "R:\99 - Common\001. File\Report\BDO BPI Report_2014_08_06_093230.xlsx"
LDate = FileDateTime(FilePath)

If LDate < Date Then
MsgBox "The file is not yet updated...Modified date is less than the current date!", vbInformation
Exit Sub
End If

Set wb = Workbooks.Open(FilePath)
Set ws = wb.Sheets("Sheet1")
End Sub

[Kaniguan1969]

Dim ws As Worksheet
Dim wb As Workbook
Dim myFile As String
Dim myPath As String
Dim latestFile As String
Dim latestDate As Date
Dim lastModified As Date

myPath = "C:\Users\Report\TOH\"
myFile = Dir(myPath & "BDO BPI Report*.xls", vbNormal)

If Len(myFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

targetfile = myPath & myFile
Set wb = Workbooks.Open(targetfile)
Set ws = wb.Sheets("Sheet1")