Excel

Check If a File Is Already Open

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Bob Phillips

Description:

Simple function that checks whether a file is already open within the host application, returning True or False accordingly. 

Discussion:

Good programming practice suggests that it is wise to check before taking certain actions. One such check is before opening a file, check whether it is already open or not. The code below provides a simple function that does this, returning True or False. There is also a very simple test program to demonstrate the function in use. It is possible to run the function from a worksheet formula (=IsFileOpen("C:\MyTest\volker2.xls")) although I struggle to see any valid use of this. 

Code:

instructions for use

			

Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error GoTo 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub test() If Not IsFileOpen("C:\MyTest\volker2.xls") Then Workbooks.Open "C:\MyTest\volker2.xls" End If End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, select the target project (probably VBAProject(name.xls) where name is the name of the spreadsheet)
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert>Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file.
 

Test the code:

  1. Run the sample program 'test'.
 

Sample File:

No Attachment 

Approved by Jacob Hilderbrand


This entry has been viewed 296 times.

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