|
|
|
|
|
|
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
Function IsXLBookOpen(strName As String) As Boolean
Dim i As Long, XLAppFx As Excel.Application, NotOpen As Boolean
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
For i = XLAppFx.Workbooks.Count To 1 Step -1
If XLAppFx.Workbooks(i).Name = strName Then Exit For
Next i
IsXLBookOpen = False
If i <> 0 Then IsXLBookOpen = True
If NotOpen Then XLAppFx.Quit
Set XLAppFx = Nothing
End Function
Sub TestFunction()
MsgBox IsXLBookOpen("Book1.xls")
End Sub
|
How to use:
|
- Copy above code.
- In program of choice, press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Select desired file/Project on left (in bold).
- Choose Insert (menu) | Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- Hit Alt + F8.
- Select 'TestFunction'.
- Click Run.
- Sample file includes: Access, PowerPoint, Word & Publisher file.
|
Sample File:
|
XLtest.zip 47.64KB
|
Approved by mdmackillop
|
This entry has been viewed 166 times.
|
|