VBA Question? If Workbook is Open

mayoung

Active Member
Joined
Mar 26, 2014
Messages
259
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a file called Parts On BackOrder-093022.xlsm. The beginning is always the same Parts On BackOrder

In the Below Code I have a Function that checks to see if a workbook is open. The function works great if looking for the whole workbook name. But how do I modify it for checking for a partial name that begins with Parts On BackOrder
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

Sub CheckWorkBook()

    Dim xRet As Boolean
    
    xRet = IsWorkBookOpen("Parts On BackOrder-093022.xlsm") '<----How do I change this?

    If xRet Then
    ActiveWorkbook.Save
    ActiveWorkbook.Close
        
    Else
        MsgBox "Parts On Backorder Is Already Closed",vbExclamation
    
    End If
    End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
That code relies on being able to 'target' a wb by exact name, which as you discovered, will only work for that name. I can only imagine being able to do this if you loop over the workbooks collection and see if any of them match your partial name value. What should happen if 2 or more wb beginning with "Parts On BackOrder" are open?
 
Upvote 0
maybe along these lines
VBA Code:
Sub testIfOpen()
Dim wb As Workbook

With Application.Workbooks
   If .count > 0 Then 'doubtful that this could ever not be at least 1, but just being safe...
      For Each wb In Application.Workbooks
         If InStr(1, "Parts On BackOrder", wb.Name) > 1 Then MsgBox wb.Name
      Next
   End If
End With

End Sub
 
Upvote 0
maybe along these lines
VBA Code:
Sub testIfOpen()
Dim wb As Workbook

With Application.Workbooks
   If .count > 0 Then 'doubtful that this could ever not be at least 1, but just being safe...
      For Each wb In Application.Workbooks
         If InStr(1, "Parts On BackOrder", wb.Name) > 1 Then MsgBox wb.Name
      Next
   End If
End With

End Sub
It appears to be looping but never brings up the message with the name? The Parts On BackOrder workbook is open?
 
Upvote 0
I suspect this should be
If InStr(1, "Parts On BackOrder", wb.Name) > 0 Then MsgBox wb.Name

I had that in the code but somehow ended up with a 1 in my post.
 
Upvote 0
I suspect this should be
If InStr(1, "Parts On BackOrder", wb.Name) > 0 Then MsgBox wb.Name

I had that in the code but somehow ended up with a 1 in my post.
Hmmm That does not work either?
 
Upvote 0
Then can only think that there is something amiss about the comparison of the string values.
comment out the If line and sub this one & see what you get so that it looks like
VBA Code:
     For Each wb In Application.Workbooks
         'If InStr(1, "Parts On BackOrder", wb.Name) > 1 Then MsgBox wb.Name
         Debug.Print wb.Name
     Next
 
Upvote 0
Then can only think that there is something amiss about the comparison of the string values.
comment out the If line and sub this one & see what you get so that it looks like
VBA Code:
     For Each wb In Application.Workbooks
         'If InStr(1, "Parts On BackOrder", wb.Name) > 1 Then MsgBox wb.Name
         Debug.Print wb.Name
     Next
It List all the open workbooks name.

Service Tools.xlsm
Book1
ServiceFile.xlsm
Parts On BackOrder-093022.xlsm
 
Upvote 0
I continue to be frustrated by the inability to remember which comes first - string being searched vs string being sought. I had it backwards 🤬🤬
InStr(1,wb.Name, "Parts On BackOrder") > 0

Sorry...
 
Upvote 0
Solution
I continue to be frustrated by the inability to remember which comes first - string being searched vs string being sought. I had it backwards 🤬🤬
InStr(1,wb.Name, "Parts On BackOrder") > 0

Sorry...
(y) That works for what I need... Thank You So Much!!!
 
Upvote 0

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

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