macro for activate workbook with partial name

[shahdelsol]

So I am using this code to activate a workbook with partial name and it works great. What I am trying to add is if workbook cannot be found I would get a msg box that says " file is not open"

Dim wb As Workbook

For Each wb In Application.Workbooks
   If wb.Name Like "partial name*" Then wb.Activate
Next wb

Here is what I added that didn't work:

If wb is nothing then 
 MsgBox "file is not open"

 

[JoeMo]

Try:

Dim Ct As Long
For Each wb In Application.Workbooks
    If wb.Name Like "partial name*" Then
        Ct = Ct + 1
        wb.Activate
        Exit For
    End If
Next wb
If Ct = 0 Then MsgBox "File not open"

 

[LockeGarmin]

I would suggest that you put this function in a different module (I would suggest naming it 'Workbooks' or 'mWorkbooks') and make sure that the 'Option Compare' highlighted in red is at the top of your workbook (above all functions and subs). The reason for this is because the LIKE operator in VBA responds to the Option Compare Text by being case insensitive (conversely Option Compare Binary is the default and makes the LIKE operator case sensitive). Workbooks in the Excel.Application.Workbooks collection likewise are case insensitive and I'm pretty sure you don't want to have to try to guess workbook pattern names by case sensitivity.

[COLOR=#ff0000]Option Compare Text[/COLOR]

Public Function GetWorkbookByNamePattern(Pattern As String) As Workbook

Dim WB As Workbook
  
  For Each WB In Application.Workbooks
    If WB.Name Like Pattern Then
      Set GetWorkbookByNamePattern = WB
      Exit Function
    End If
  Next WB
  
  Set GetWorkbookByNamePattern = Nothing
  
End Function

Then back in your other code you can call the method like this:

Dim WB As Workbook

Set WB = GetWorkbookByNamePattern("partial name*")

If WB Is Nothing Then
  MsgBox "file is not open"
Else
  WB.Activate
End If

 

[shahdelsol]

Thank you Joe, This worked Great!

Try:

Dim Ct As Long
For Each wb In Application.Workbooks
    If wb.Name Like "partial name*" Then
        Ct = Ct + 1
        wb.Activate
        Exit For
    End If
Next wb
If Ct = 0 Then MsgBox "File not open"

 

[shahdelsol]

Great and Thank you for your help

I would suggest that you put this function in a different module (I would suggest naming it 'Workbooks' or 'mWorkbooks') and make sure that the 'Option Compare' highlighted in red is at the top of your workbook (above all functions and subs). The reason for this is because the LIKE operator in VBA responds to the Option Compare Text by being case insensitive (conversely Option Compare Binary is the default and makes the LIKE operator case sensitive). Workbooks in the Excel.Application.Workbooks collection likewise are case insensitive and I'm pretty sure you don't want to have to try to guess workbook pattern names by case sensitivity.

[COLOR=#ff0000]Option Compare Text[/COLOR]

Public Function GetWorkbookByNamePattern(Pattern As String) As Workbook

Dim WB As Workbook
  
  For Each WB In Application.Workbooks
    If WB.Name Like Pattern Then
      Set GetWorkbookByNamePattern = WB
      Exit Function
    End If
  Next WB
  
  Set GetWorkbookByNamePattern = Nothing
  
End Function

Then back in your other code you can call the method like this:

Dim WB As Workbook

Set WB = GetWorkbookByNamePattern("partial name*")

If WB Is Nothing Then
  MsgBox "file is not open"
Else
  WB.Activate
End If