Macro Modification Needed for Workbook Open

mayoung

Active Member
Joined
Mar 26, 2014
Messages
259
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I currently have this code to check if a workbook is open before proceeding to run the rest of the code. This works great when looking at the whole workbook name. How can this be modified to just look at the beginning
of the workbook name minus the date (Current Hilliard WIP)? Any help would be appreciated. Thank You

VBA Code:
Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb    As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Code:
    Dim xRet   As Boolean
    
    xRet = IsWorkBookOpen("Current Hilliard WIP-01142023.xlsm")
    
    If xRet Then
        MsgBox "Found it, Current Hilliard WIP Is Open, Ok To Continue", vbInformation
        
        End If
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,
untested but try this modified version of the function

VBA Code:
Private Function IsWorkbookOpen(ByVal wbName As String) As Boolean
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        IsWorkbookOpen = UCase(wb.Name) Like UCase(wbName) & "*"
        If IsWorkbookOpen Then Exit For
    Next wb
End Function

and to call it

VBA Code:
Const BookName As String = "Current Hilliard WIP"

    If IsWorkbookOpen(BookName) Then
        MsgBox BookName & " Is Open, Ok To Continue", vbInformation
    End If

Dave
 
Upvote 0
Solution
Hi,
untested but try this modified version of the function

VBA Code:
Private Function IsWorkbookOpen(ByVal wbName As String) As Boolean
    Dim wb As Workbook
    For Each wb In Application.Workbooks
        IsWorkbookOpen = UCase(wb.Name) Like UCase(wbName) & "*"
        If IsWorkbookOpen Then Exit For
    Next wb
End Function

and to call it

VBA Code:
Const BookName As String = "Current Hilliard WIP"

    If IsWorkbookOpen(BookName) Then
        MsgBox BookName & " Is Open, Ok To Continue", vbInformation
    End If

Dave
Thank You
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

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
Back
Top