VBA Question? If Workbook is Open

[mayoung]

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!!!

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

 

[Micron]

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?

 

[Micron]

maybe along these lines

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

 

[mayoung]

maybe along these lines

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?

 

[Micron]

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.

 

[mayoung]

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?

 

[Micron]

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

     For Each wb In Application.Workbooks
         'If InStr(1, "Parts On BackOrder", wb.Name) > 1 Then MsgBox wb.Name
         Debug.Print wb.Name
     Next

 

[mayoung]

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

     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

 

[Micron]

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...

 

[mayoung]

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...

That works for what I need... Thank You So Much!!!