Get Workbook Name From Available Open Workbooks

[Ark68]

Can someone help me find an Excel VBA solution to step through my open workbooks to find the one with a name (always) formatted as "mmm-dd (ddd) Data.xlsx", and return the name of that file?

I have 3 open workbooks and I need to find the file name of the one, for example, named "Jun-24 (Mon) Data.xlsx"

 

[JLGWhiz]

Sub t()
Dim wb As Workbook, nm As String
    For Each wb In Application.Workbooks
        If wb.Name = "Jun-24 (Mon) Data.xlsx" Then
            nm = wb.FullName
            MsgBox nm
        End If
    Next
End Sub

 

[Ark68]

Thanks JLGWhiz! Maybe I did a poor job of explaining my needs.

I am looking to find the name of the workbook thats open that has that format. This workbook is dynamic and will always have a different name. Only one workbook will ever be open that has a name with the format "mmm-dd (ddd) data.xlsx" and its the one I need the filename of.

In your solution, the name of the file is known which won't be the case.

 

[JLGWhiz]

Try looking for it with

If wb.Name Like "*Data.xlsx" Then
    MsgBox wb.Name
End If

Otherwise, you might have to use regex.

 

[Ark68]

Thank you. That was what I was uncertain about ... how to find it.

 

[JLGWhiz]

You're welcome,
regards, JLG