Multiple Apps

Check if Excel workbook is open

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Zack Barresse

Description:

Use in multiple Office Applications, test if a specific Excel file is open or not. Will return a boolean True or False. 

Discussion:

When using another program to interact with Excel, sometimes it may be necessary for a little more control over the applications at hand. If dealing with a specific Excel file that you wish to return data from, it's easier to use when that file is open. This code is designed to allow one to test whether a specified Excel file is currently open or not. A reference must be made to the Excel Object Model/Library, by going to the Visual Basic Editor (Alt + F11) | Tools (menu) | References | (check) Microsoft Excel 11.0 Object Library. Note that 11.0 is version 2003, 10.0 is 2002/XP, 9.0 is 2000, etc. Check your version by going to the Help (menu) | About Microsoft Excel. 

Code:

instructions for use

			

Option Explicit '---------------------------------------------------------- 'Must set a reference (Tools | References) to 'Microsoft Excel 11.0 (or relevent version) Object Library '-------------------- ' Version: ' 11.0 = 2003 ' 10.0 = 2002 or XP ' 9.0 = 2000 ' 8.0 = 1997 '-------------------- 'This will make use of 'Early Binding' and give us access 'to the Excel Object Model and it's intellisense. '---------------------------------------------------------- Function IsXLBookOpen(strName As String) As Boolean '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("Book1.xls") End Sub

How to use:

  1. Copy above code.
  2. In program of choice, press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Select desired file/Project on left (in bold).
  5. Choose Insert (menu) | Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Hit Alt + F8.
  2. Select 'TestFunction'.
  3. Click Run.
  4. Sample file includes: Access, PowerPoint, Word & Publisher file.
 

Sample File:

XLtest.zip 47.64KB 

Approved by mdmackillop


This entry has been viewed 166 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express