VB Open file with partial file name

[hblbs]

Hello all,

I am trying to use the code below to open a file and run another macro with it. However I am having difficulty opening the file. The problem seems to be that as the file name is date and time stamped I have to use the entire file name to open it.

I was hoping the code below could be modified to allow me to open the file using just the date stamp without having to modify the file name. The file name are all in the following format "Filename_YYMMDD_HHMMSS.xls", but I was wondering if could be done by using "Filename_YYMMDD*.xls".

Private Sub cbReport_Click()
    Dim day As String
    Dim month As String
    Dim year As String
    Dim year_long As String
    Dim source_path As String
    Dim destination_path As String
    Dim template_path As String
    Dim outstanding_items As String
    Dim generic_destination_file_name As String
    Dim outstanding_items_filename As String
 
 
    day = tbDay
    month = tbMonth
    year = tbYear
 
    'Determine path of raw Caseload report data
    source_path = Range("Path!A1")
    'Determine generic name of raw file
    outstanding_items = Range("Path!A2")
 
'Determine year in 4 digit format
    year_long = "20" & year & ""
 
'Store source file name in variable outstanding_items_filename
    outstanding_items_filename = "" & outstanding_items & "" & year & "" & month & "" & day & ".xls"
 
    'Open file & outstanding_items_filename & (& outstanding_items & "" & month & "" & year_long & ".xls")
    'which contains raw stats
    Workbooks.Open Filename:= _
        "" & source_path & "\" & outstanding_items_filename & ""

Hopefully this makes sense.

Thanks in advance for any help.

 

[bensonsearch]

if its just the file yes.

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\Documents and Settings\bensonsearch\My Documents\" 'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file