Thursday, December 09, 2004

Checking a file is not open by another user in Excel VBA

This is the code I use to identify whether or not a file is already opened by another user.
It checks the Random Access Read property of the Open method.

The currentfile variable is the variable you need to replace with a valid file name Eg "C:\exceltestfile.xls".

Sub check_file_available()
'Macro writen by www.jethromanagement.biz 28 April 2003
'check the file isnt already open
Dim checkavailable
Dim currentfile As String
filenumber = FreeFile
On Error GoTo accesserror
Open currentfile For Random Access Read Lock Read Write As #filenumber
GoTo noerror:
accesserror:
checkavailable = True
MsgBox "The " & currentfile & " file is currently being modified by another user. This process will terminate.", vbOKOnly + vbExclamation, "File open error"
noerror:
Close #filenumber
End Sub


Posted by: Jethro | 1:40 AM |