27 | 06 | 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")
        Err.Clear
    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
Login

Sign up now and upload your code to the website.

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