Excel VBA Test if workbook is open if so bring to the front

[healey21]

I sometimes open a lot of workbooks and want to work on a specific workbook if it is open if not then I need to open it, if it is open how can I make that the active window workbook I am trying this out but can't get it switch windows if it is open or open if it is closed.

Sub isFileOpen()
Dim wkBk As Workbook

Set wkBk = Nothing
On Error Resume Next
Set wkBk = Workbooks("Invoice number vba.xlsm")
On Error GoTo 0

If wkBk Is Nothing Then
MsgBox "The File is not open!"
Else
wkBk = Workbooks.Open("L:\Invoice number vba.xlsm")
MsgBox "The File is open!"
End If

End Sub

 

[JoeMo]

Try this:

Sub isFileOpen()
Dim wkBk As Workbook
If WorkbookOpen("Invoice number vba.xlsm") Then
    MsgBox "The File is open!"
Else
    Set wkBk = Workbooks.Open("L:\Invoice number vba.xlsm")
End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[healey21]

Thanks Joe

This works if the workbook is closed, but doesn't bring to the front if it is already open, and I am not sure what needs to adjusted this maybe the 3rd book I have open or maybe 10th, Is there away to bring it to the front to be the activeworkbook rather than just using Ctrl + Tab until it appears?

 

[taurean]

Try adding following line:

Sub isFileOpen()
Dim wkBk As Workbook
If WorkbookOpen("Invoice number vba.xlsm") Then
    MsgBox "The File is open!"
    Workbooks("L:\Invoice number vba.xlsm").Activate
Else
    Set wkBk = Workbooks.Open("L:\Invoice number vba.xlsm")
End If
End Sub

 

[healey21]

Thank you for the extra line Shrivallabha this works lovely now.