extracting date that is part of file name

Thanks in advance for your suggestions.

I have an excel file named "1st 05-01-13 MB.xlsx"
The date will vary each month: could be "1st 06-03-13 MB.xlsx" next month for example. The file name syntax is the same everymonth except the date change.

I would like to extract the string "05-01-13" ( in this case) and load it as the value for the variable "currentmonth" in my macro that builds a report.

is there a way to do this when initializing the variable without having to maually type it in each month after viewing the current month's file?

Here is the code line where i would want to insert the variable "currentmonth"

Sheets("NOGC").Copy After:=Workbooks("1st " & currentmonth & " MB.xlsx").

Thanks, these forums have been a great help as I learn the vba coding.

Something like this

Sub test()
    Dim currentmonth
    currentmonth = GetCurrentMonth("1st 05-01-13 MB.xlsx")
    MsgBox currentmonth
End Sub

Function GetCurrentMonth(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d{1,2}\-){2}\d{2}"
        If .test(txt) Then GetCurrentMonth = .Execute(txt)(0)
    End With
End Function

is the file name in a cell? or is it just a file name in a folder? does the macro that you are using already call this file name?

Thanks all, I will try the suggestions made and post a reply later.

Scott- the file "1st 05-01-13 MB.xlsx" is the open file that I am running the macro on. That is, I open the workbook, run the macro which modifies some pages in the workbook to generate a report page as one of the worksheets in the workbook itself.

I don't think I defined my problem well enough so I am going to close this thread and try again. Thanks to you all