|
|
|
|
|
|
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:
|
- Copy the above code.
- Open any workbook.
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- In the left side window, select the target project (probably VBAProject(name.xls) where name is the name of the spreadsheet)
- Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert>Module.
- Paste the code into the right-hand code window.
- Close the VBE, save the file.
|
Test the code:
|
- Run the sample program 'test'.
|
Sample File:
|
No Attachment
|
Approved by Jacob Hilderbrand
|
This entry has been viewed 296 times.
|
|