Checking if a Workbook is Open in any Excel Instance
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:
- The window handle of the window to search in
- The window handle of the child window that the search begins from
- The class name of the window we are searching for
- The title or caption of the window we are searching for
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:
- Have another Excel application in which case mWnd will be non-zero or
- Have not found the workbook window in which case cWnd will be zero
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.
