Forum

prevent two users f...
 
Notifications
Clear all

prevent two users from editing the same file at the same time

3 Posts
2 Users
0 Reactions
92 Views
(@stevenbehr1)
Posts: 92
Estimable Member
Topic starter
 

Afternoon All,

I have found a VBA code that would prevent 2 or more using a excel file

When opening a file a InUse_Yes.txt that would drop in together with the excel file. 

I looked at this VBA file which may have some merit to its name but these is a run time error 52 Bad File Name or Number.

Option Explicit

Const FileControlFolder = "FileControlFolder = C:DatabaseFilesInUse"""
Dim FileControlSubFolder As String

Private Sub Workbook_Open()

If ThisWorkbook.ReadOnly = False Then

FileControlSubFolder = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

If Dir(FileControlFolder & FileControlSubFolder & "InUse_YES.txt") <> "" Then
MsgBox "It seems that the " & FileControlSubFolder & _
" file is being used by another user. Please come back later.", _
vbCritical, "More than one user accessing the file."
ThisWorkbook.Close SaveChanges:=False
Else
Name FileControlFolder & FileControlSubFolder & _
"InUse_NO.txt" As FileControlFolder & FileControlSubFolder & "InUse_YES.txt"
TextFile_Create (FileControlFolder & FileControlSubFolder & "" & Environ("UserName") & ".txt")
End If

FileControlSubFolder = vbNullString

End If
End Sub

 

Thanks

 

Steve

 
Posted : 19/01/2021 11:01 pm
(@debaser)
Posts: 838
Member Moderator
 

Your Const line is completely wrong. It should be:

Const FileControlFolder As String = "C:DatabaseFilesInUse"

 
Posted : 20/01/2021 5:54 am
(@stevenbehr1)
Posts: 92
Estimable Member
Topic starter
 

Yes thanks Velouria,

 

I found that out just as i finished work and changed that very line.

 

Now I get the error in filename not defined????

Name FileControlFolder & FileControlSubFolder & _
"InUse_NO.txt" As FileControlFolder & FileControlSubFolder & "InUse_YES.txt"
TextFile_Create (FileControlFolder & FileControlSubFolder & "" & Environ("UserName") & ".txt")

 

Still figuring it out!

 

Thanks

 

Steve

 
Posted : 20/01/2021 6:10 am
Share: