VBA check if workbook open

[FryGirl]

I found some code and with a slight modification I'm not getting the right results.

I have workbook names in A1:A8 in my master workbook. If the workbook with the name in A1:A8 is open, I would like B1:B8 to show the corresponding "Open" or "Closed".

Any thoughts on how to achieve this?

Sub IsWorkBookOpen()
    Dim wBook As Workbook
    Dim i As Long
    On Error Resume Next
    For i = 1 To 8
        Set wBook = Workbooks(Cells(i, 1))
        If wBook Is Nothing Then
            Cells(i, 2).Value = "Open" 'Not open
        Else
            Cells(i, 2).Value = "Open" 'Open
        End If
    Next i
End Sub

 

[mikerickson]

Try this UDF

Function IsWorkbookOpen(wbName as String) As Boolean
    On Error Resume Next
    IsWorkbookOpen = (LCase(Workbooks(wbName).Name) = LCase(wbName))
    On Error Goto 0
End Function

 

[dmt32]

Hi,
try this update to your code

Sub IsWorkBookOpen()
    Dim wBook As Workbook
    Dim i As Long
    On Error Resume Next
    For i = 1 To 8
        Set wBook = Workbooks(Cells(i, 1).Text)
            Cells(i, 2).Value = IIf(wBook Is Nothing, "Closed", "Open")
        Set wBook = Nothing
        Err.Clear
    Next i
End Sub

Dave

 

[FryGirl]

Thank you. Both of these worked great for my needs.