Activating Workbook by Naming Convention Not Working As Expected

[ERLoft]

The hospitality industry work I'm doing is based on a report that is converted from a .pdf file and saved with a filename of the report date - so today's report is 012814.xlsx, tomorrow will be 012914.xlsx, etc.

I'm working on a macro that will be run from another workbook, pulling data from this report. Since the filename changes on a daily basis, I'm trying to get the macro to check to see if the proper workbook is open, and if so, switch to it. If it's not open, it will prompt the user to open it.

I've tried two different approaches, but neither have worked as I expected. This version is based on help I received here that checks for a specific named workbook:

Sub FindHandFWorkbookTest()

Dim wb As Workbook
Dim bFlag As Boolean
Dim sName As String

For Each wb In Application.Workbooks
     If wb.Name Like "######" Then
             bFlag = True
             sName = wb.Name
             Exit For
     End If
Next wb

    If bFlag = True Then
        Workbooks(sName).Activate
        Else
            MsgBox ("Your History and Forecast spreadsheet is not currently open, please open it now")
            FileToOpen = Application.GetOpenFilename _
            (Title:="Please select your history and forecast report", _
            FileFilter:="Excel Files *.xlsx (*.xlsx),")
    
            If FileToOpen = False Then
                MsgBox "No file specified.", vbExclamation, "Duh!!!"
                Exit Sub
            Else
                Workbooks.Open Filename:=FileToOpen
            End If
    End If

End Sub

I also tried this method:

Sub FindHandFWorkbookTest()

Dim wb As Workbook
Dim bFlag As Boolean
Dim sName As String

For Each wb In Application.Workbooks
    If Len(wb.Name) = 6 And IsNumeric(wb.Name) Then
            bFlag = True
            sName = wb.Name
            Exit For
    End If
Next wb

    If bFlag = True Then
        Workbooks(sName).Activate
        Else
            MsgBox ("Your History and Forecast spreadsheet is not currently open, please open it now")
            FileToOpen = Application.GetOpenFilename _
            (Title:="Please select your history and forecast report", _
            FileFilter:="Excel Files *.xlsx (*.xlsx),")
    
            If FileToOpen = False Then
                MsgBox "No file specified.", vbExclamation, "Duh!!!"
                Exit Sub
            Else
                Workbooks.Open Filename:=FileToOpen
            End If
    End If

End Sub

Yet, when I have open one of these reports that has this type of filename, it's still prompting me to open the file, so the bFlag condition is never being set to True. I'm at a loss as to why?

 

[ERLoft]

Got it.

So for anyone who searches. There were issues with each of the macros above.

Since the Workbook.Name property returns the file extension, the first macro didn't work because it was looking for something 6 numbers long. 012514.xlsx is 11 characters long and is alphanumeric plus a dot.

The second macro didn't work because again, the file name isn't a total of 6 characters and isn't completely numeric.

The way I got it to work was this:

Sub FindHandFWorkbookTest()

Dim wb As Workbook
Dim bFlag As Boolean
Dim sName As String

For Each wb In Application.Workbooks
    If wb.Name Like "######.*" Then
            bFlag = True
            sName = wb.Name
            Exit For
    End If
Next wb

    If bFlag = True Then
        Workbooks(sName).Activate
        Else
            MsgBox ("Your History and Forecast spreadsheet is not currently open, please open it now")
            FileToOpen = Application.GetOpenFilename _
            (Title:="Please select your booking pace spreadsheet", _
            FileFilter:="Excel Files *.xlsx (*.xlsx),")
    
            If FileToOpen = False Then
                MsgBox "No file specified.", vbExclamation, "Duh!!!"
                Exit Sub
            Else
                Workbooks.Open Filename:=FileToOpen
            End If
    End If
    
    With Sheets("Sheet1")
        Range("H3").Select
    End With

End Sub