Test if a Specific workbook is open

[cprince]

Hi All,

Can someone provide me with some code that tests if a certain workbook is open, If it s open 'do something' if not, Msgbox "File not open..."

 

[agihcam]

this code will test if the file name TEST.XLS is open or not;

Sub test_if_open()
   Dim File As String
    File = "TEST.XLS"
        If IsWbOpen(File) Then
            MsgBox "Open"
                Else
                    MsgBox "File Not Open"
        End If
End Sub
Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
End Function

 

[sweater_vests_rock]

hi c.

here is an example which should help.

cheers. ben.

Sub GetWorkbook()

    Dim wbOpen As Workbook, strWorkbook As String

'   Suppress errors
    On Error Resume Next
    
    strWorkbook = "Book1" '<- change this to your workbook name
    Set wbOpen = Workbooks(strWorkbook)
    
'   Test if workbook is open
    If wbOpen Is Nothing Then
'       Workbook is not open
        MsgBox "Can't find it, sorry!"
    Else
'       Workbook is open
        MsgBox "I got it!  Here you go!"
        wbOpen.Activate
    End If
    
    On Error GoTo 0
    Set wbOpen = Nothing
    
End Sub

 

[cprince]

Got it ...

Thanks Guys,