When working with files in VBA code, you may need to know if a file is already open. A function can return TRUE if a file is currently open and FALSE if it isn’t.
Here is a simple function that you can use.
Function FileIsOpen(sName As String) As Boolean 'returns TRUE if the file is already open ' use as If FileIsOpen("testfile") Then Dim wb As Workbook On Error Resume Next Set wb = Workbooks(sName) FileIsOpen = (Err.Number = 0) Set wb = Nothing End Function |
The code that uses the function could look something like this.
Sub TestFile() If FileIsOpen("testfile") Then MsgBox "File is open" 'do stuff Else MsgBox "File is not open" 'tell user to fix something End If End Sub |
Functions are useful in VBA as you can re-use them in different projects.
Dear Neale
Why am I not able to use the Function as a worksheet Function?
Hi Sandeep
I got it work as a worksheet function.
Have you saved the function in a module.
Regards
Neale
And when I use the Function by using the given Sub, it returns “File is not open” via a msgbox, even though the file is actually open?
I just retested and it works ok – see code i used below.
Function FileIsOpen(sName As String) As Boolean
‘returns TRUE if the file is already open
‘ use as If FileIsOpen(“testfile”) Then
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(sName)
FileIsOpen = (Err.Number = 0)
Set wb = Nothing
End Function
Sub test()
If FileIsOpen(“free_rego_list”) Then
MsgBox “file is open”
Else
MsgBox “file is NOT open”
End If
End Sub