CODEWITHSUNDEEP
vbaexcel
SanomaJean
SanomaJean

Reputation: 181

Tell if a workbook with a specific string in the name is open

I have a project where I have a dropdown filled with names of clients. If they select client "Anna" and there is a workbook that has "Anna" in the name, that workbook is opened. If not I want a msgbox to pop up saying "that client doesnt exist"

How can I tell if there is currently a workbook with instr("anna") open ?

Here is my current code which just looks at if there is only one workbook open (control workbook) but obviously they could have other stuff open so this isnt a long term solution. Thanks

strCurrPath = Application.ThisWorkbook.Path
lenStrCurrPath = Len(strCurrPath) + 9
lenstrCurrNameSelect = Len(strCurrNameSelect)

intTotal = lenStrCurrPath + lenstrCurrNameSelect

file = Dir(strCurrPath & "\Clients\")
While (file <> "")
    If InStr(file, Sheet1.strCurrNameSelect) > 0 Then
        Workbooks.Open (strCurrPath & "\Clients\" & file)
    End If
    file = Dir
Wend

If Workbooks.Count <= 1 Then
    MsgBox ("Could not find that client workbook. Check folder.")
Else 'DoNothing
End If

Upvotes: 0

Views: 46

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Simple example:

Sub Tester()

    Dim wb As Workbook
    Set wb = ClientWorkbook("anna")
    If Not wb Is Nothing Then
        MsgBox "Found matching workbook: " & wb.Name
    Else
        MsgBox "No matching workbook"
    End If

End Sub

Function ClientWorkbook(clientName As String) As Workbook
    Dim wb As Workbook, rv As Workbook
    For Each wb In Application.Workbooks
        If UCase(wb.Name) Like "*" & UCase(clientName) & "*" Then
            Set rv = wb
            Exit For
        End If
    Next wb
    Set ClientWorkbook = rv
End Function

Upvotes: 2

Related Questions