21 | 08 | 2017

Is Workbook Open

 Use the function below to check if a workbook is open.

Function IsXLBookOpen(strName As String) As Boolean
'*** Code from VisibleVisual.com ********
     'Function designed to test if a specific Excel
     'workbook is open or not.
    Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
     'Find/create an Excel instance
    On Error Resume Next
    Set XLAppFx = GetObject(, "Excel.Application")
    If Err.Number = 429 Then
        NotOpen = True
        Set XLAppFx = CreateObject("Excel.Application")
    End If
     'Loop through all open workbooks in such instance
    For i = XLAppFx.Workbooks.Count To 1 Step -1
        If XLAppFx.Workbooks(i).name = strName Then Exit For
    Next i
     'Set all to False
    IsXLBookOpen = False
     'Perform check to see if name was found
    If i <> 0 Then IsXLBookOpen = True
     'Close if was closed
    If NotOpen Then XLAppFx.Quit
     'Release the instance
    Set XLAppFx = Nothing
End Function

Sub TestFunction()
    MsgBox IsXLBookOpen("c:/test.xls")
End Sub

Sign up now and upload your code to the website.

Help us to continue.....
Articles View Hits
Latest Articles