Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Checking if a Workbook is Open in any Excel Instance

written by Mark Rowlinson - Last updated Oct 2004

Introduction

It is sometimes a requirement to check if an Excel workbook is open already. This is straighforward when dealing with just one application but if there are multiple instances then it is much harder.

The Current Excel Instance

When dealing with the current Excel instance things are easy. We simply set the error handling to on error resume next and attempt to set a reference to the workbook as follows.

Dim wb As Workbook 
On Error Resume Next 
Set wb=application.workbooks("WorkbookName.xls") 
On Error Goto 0 'now reset error handling
If wb Is Nothing Then MsgBox"Workbook is not open" 

This can easily be adjusted when automating Excel from a Visual basic app as follows.

Dim wb As Excel.Workbook 
On Error Resume Next 
Set wb=xlApp.workbooks("WorkbookName.xls") 
On Error Goto 0 'now reset error handling
If wb Is Nothing Then MsgBox"Workbook is not open" 

Where 'xlApp' is the variable containing a reference to your Excel application.

Checking all Excel Instances

The problem comes where there may be several Excel instances. Unfortunately 'GetObject' will return the same reference every time, there is no way to get it to loop through all instances. We therefore have to turn to the Win32 API. The following function will look for the given workbook name in all running Excel instances and return true if it finds it.

Declare Function GetDesktopWindow Lib"user32" () As Long 
Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ 
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long 
 
 
Function IsWorkBookOpen(strWBName As String) As Boolean 
     
    Dim dWnd As Long, hWnd As Long, mWnd As Long, cWnd As Long 
    dWnd = GetDesktopWindow 
    hWnd = FindWindowEx(dWnd, 0&,"XLMAIN", vbNullString) 
    mWnd = FindWindowEx(hWnd, 0&,"XLDESK", vbNullString) 
    While mWnd <> 0 And cWnd = 0 
        cWnd = FindWindowEx(mWnd, 0&,"EXCEL7", strWBName) 
        hWnd = FindWindowEx(dWnd, hWnd,"XLMAIN", vbNullString) 
        mWnd = FindWindowEx(hWnd, 0&,"XLDESK", vbNullString) 
    Wend 
    If cWnd > 0 Then 
        IsWorkBookOpen = True 
    End If 
     
End Function 
 
 

The function first retrieves a handle to the desktop window and stores it in dWnd. It then searches for a child window to the desktop with Class "XLMAIN". "XLMAIN" is the class of the main Excel application window. It finds this window using the function FindWindowEx. This takes 4 arguments:

Within the "XLMAIN" window there is another window called "XLDESK" that contains the workbook windows. We therefore use FindWindowEx again to find the window handle of this window. We now have a handle to the window in an excel application that is the parent to all the workbooks of that application. We therefore search this window for a workbook window that will have the caption of the workbook name - in addition the class name of the workbook window is "EXCEL7". If the workbook is open then this will return a handle to the workbook window, otherwise it will return 0.

Now the function tries to obtain a handle to the next Excel application in the z-order by again searching the desktop window but this time passing the previous window handle as the 2nd argument to the function. If another excel instance exists this will return the handle but if not it will return 0. We get the handle to the corresponding "XLDESK" window if applicable before then looping through again if we either:

Finally the function checks to see if the reason for exiting the loop was because no more applications were found or because the workbook was found. If the workbook was found it returns true.