Solved Detect through ACCESS if an Excel file is already opened (1 Viewer)

dianaha

New member
Local time
Today, 08:11
Joined
Jun 3, 2021
Messages
16
I have tried different ways to open/modify/save an Excel file which is already opened.
With the Workbook.Open() method, it opens it. But after modification, when I want to save and close it, of course it ask to SAVE AS the document.. I don't want to create a new Excel file.
I would like to avoid to open an Excel document which is already opened.. For example get a value of a Boolean variable called "IsOpenedFile" , which is true when it is already opened and false in the other case.

I have tried the code bellow.
Unfortunatly, when I previously open the excel file and run the code, the error handling doesn't seem to work, because I have still the window appearing with error number 70 saying that the access is refused (the error occurs at the Open statement... line n°5)

I have no idea how to solve the problem, can someone help?


Code:

Function IsOpenedFile(MyWorkbook As String)

MyWorkbook = "C:\Users\Username\Documents\File_name.xls"

Dim FileNumber As Long

On Error Resume Next
FileNumber = FreeFile()
Open MyWorkbook For Input Lock Read As #FileNumber
Close FileNumber
ErrorNumber = Err
On Error GoTo 0

Select Case ErrorNumber
Case 0: IsOpenedFile = False
Case 70: IsOpenedFile = True
Case Else: Error ErrorNumber
End Select

End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:11
Joined
May 7, 2009
Messages
14,982
Code:
Function IsOpenedFile(MyWorkbook As String)


Dim FileNumber As Long

On Error Resume Next
FileNumber = FreeFile()
Open MyWorkbook For Input Lock Read As #FileNumber
Close FileNumber
ErrorNumber = Err
On Error GoTo 0

Select Case ErrorNumber
Case 0: IsOpenedFile = False
Case 70: IsOpenedFile = True
Case Else: Error ErrorNumber
End Select

End Function

you call the function:

Code:
Dim strFile As String
strFile="C:\Users\Username\Documents\File_name.xls"
If IsOpenedFile(strFile)=False
    'excel file is not open
    'open it here

Else
    msgbox strfile & " is already opened by someone else!"
End If
 

dianaha

New member
Local time
Today, 08:11
Joined
Jun 3, 2021
Messages
16
Hi,
Yes I have already a very similar code (with Msgbox) where I call this function, but the error occures in the function IsOpenedFile().

My problem is actually to avoid the message error (I still do not understand why the message appears even if I use the "On Error Resume Next Statement) and skip the line when it is not possible to open the document.

I am also not sure if this line:
Open MyWorkbook For Input Lock Read As #FileNumber

is actually usable in Access, does someone know more about this statement?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:11
Joined
May 7, 2009
Messages
14,982
it tries to Open the file and prevent other from reading from it.
if someone has already opened the file, an error occurs.
 

dianaha

New member
Local time
Today, 08:11
Joined
Jun 3, 2021
Messages
16
Okay thanks!
So it is what I have planed, it will lead to an error if the document is already opened.

How to skip the skip the error but treat the error depending on the type of error?

I use the "On error " statement, but I have still the windows which appears with the error n°70, which I mention in my code in the "select case" statement.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:11
Joined
May 7, 2009
Messages
14,982
i just tested it with the excel file open and i did not get the error
you are having.

btw you need to define ErrorNumber As Long.
 

dianaha

New member
Local time
Today, 08:11
Joined
Jun 3, 2021
Messages
16
Okay thanks...

Are there any settings concerning the errors messages?
 

dianaha

New member
Local time
Today, 08:11
Joined
Jun 3, 2021
Messages
16
I have found my problem,
It was in the error handling setting in VBA options... the option of "stop to all errors" was choosen instead of "stop only on non managed errors".
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:11
Joined
May 7, 2009
Messages
14,982
slightly modified:
Code:
Function IsOpenedFile(MyWorkbook As String)
Dim ErrorNumber As Long

Dim FileNumber As Long

On Error Resume Next
FileNumber = FreeFile()
Open MyWorkbook For Input Lock Read As #FileNumber
Close FileNumber
ErrorNumber = Err
On Error GoTo 0

Select Case ErrorNumber
Case 0: IsOpenedFile = False
Case 70: IsOpenedFile = True
Case Else: IsOpenedFile = ErrorNumber
End Select

End Function
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:11
Joined
Mar 14, 2017
Messages
6,062
You should skip all that and use one simple line of code to test for the lock file temp file which exists anytime in Excel file is open....
 

Users who are viewing this thread

Top Bottom