Activating Workbook by Naming Convention Not Working As Expected

ERLoft

Board Regular
Joined
Feb 24, 2006
Messages
172
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:

Code:
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:
Code:
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?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

ERLoft

Board Regular
Joined
Feb 24, 2006
Messages
172
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,089
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top