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

dianaha

New member
Local time
Today, 13:37
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, 20:37
Joined
May 7, 2009
Messages
19,175
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, 13:37
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, 20:37
Joined
May 7, 2009
Messages
19,175
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, 13:37
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, 20:37
Joined
May 7, 2009
Messages
19,175
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, 13:37
Joined
Jun 3, 2021
Messages
16
Okay thanks...

Are there any settings concerning the errors messages?
 

dianaha

New member
Local time
Today, 13:37
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, 20:37
Joined
May 7, 2009
Messages
19,175
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
Today, 05:37
Joined
Mar 14, 2017
Messages
8,738
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....
 

Access or E

New member
Local time
Today, 08:37
Joined
Aug 29, 2022
Messages
12
How does one find the Excel lock file? It is not like Access which generates an .laccdb or .ldb lock file.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:37
Joined
Feb 28, 2001
Messages
27,001
To the best of my knowledge, Excel doesn't have a lock file like Access does.

Access uses the .LDB or .LACCDB file to hold partial file locks because it willingly shares the file. It just doesn't want two people to diddle with the same file section at the same time, so it holds block-locking data. Therefore, the Access lock file is sort of a fine-structure lock as opposed to a whole-file lock.

Excel doesn't like to share workbooks at all. In effect they are SHARED READ EXCLUSIVE WRITE. There is no Excel-flavored partial lock file because Excel doesn't support partial locking. Therefore no need for the supplemental lock file. It only uses whole-file locks.

If there IS a whole-file lock, it is held by the Windows O/S on the same computer where the Excel file itself is located. But it is in the Windows executive scratchpad area and cannot be directly accessed unless you are writing highly privileged code.

Arnel's code takes advantage of the fact that the Windows file system manages all whole-file locks. It will strictly honor the sharing requests AND will strictly reject any that try to violate the sharing requests and usage rules.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:37
Joined
Sep 21, 2011
Messages
14,051
You do get the filename prefixed witrh ~$
1705228049543.png
 

Users who are viewing this thread

Top Bottom