VBA Express : Excel - Check if Workbook Open

Excel

Check if Workbook Open

Ease of Use

Easy

Version tested with

97, 2000, 2002, 2003

Submitted by:

Zack Barresse

Description:

This checks to see if a specific workbook is open. If it is, the macro makes it the active workbook. If not, it opens it.

Discussion:

Often you may find yourself opening and closing the same spreadsheet many times over the course of a day, week or even month. This is a quick way to open it without having to search for it every time, or clutter your desktop with hollow shortcuts. It has error trapping so if the workbook is already open, it just makes it the active workbook, or else opens it. You MUST change the file path in the macro before using!

Code:

instructions for use

Option Explicit 'You must change these lines to your desired folder location! Public Const fpath = "C:\Documents and Settings\USER NAME\Desktop\" Public Const fname = "Book1.xls" Sub openMyFile() On Error Resume Next Workbooks(fName).Activate 'If Excel cannot activate the book, then it's not open, which will ' in turn create an error not of the value 0 (no error) If Err = 0 Then Exit Sub 'Exit macro if no error End If Err.Clear 'Clear erroneous errors Workbooks.Open fPath & fName End Sub

How to use:

Copy above code above.
From Excel, press Alt + F11 to open the Visual Basic Editor (VBE).
Select a file on left, choose Insert -> Module from the menu.
Paste the code into the window at right.
Change fPath and fName to your desired location and filename.
Press Alt + Q to close the VBE.
Save your file before any changes are made.

Test the code:

Press Alt + F8.
Double-click openMyFile.

Sample File:

openFileEx.zip 5.86KB

Approved by mdmackillop

This entry has been viewed 220 times.