How to prevent users to write to a text file (over a network share) at the same time using VBA

Question

I have a situation where 3 or 4 people are trying to write in a text file at the exact same time. I would like allow only 1 user at the time writing to the file.

I tried:

Dim FNum As Integer

FNum = FreeFile()
Open "\\SHARE\Logs\File.log" For Append As FNum
Print #FNum, Now() & "> The user " & GetUserName() & " wrote something."
Close #FNum

And it prevents many conflicts but not all.

In my File.log, I notice that there ware some writes at the same time:

2022-10-31 11:35:22 > The user USER1 wrote something.
omething.
2022-10-31 11:35:23 > The user USER2 wrote something.
2022-10-31 11:35:27 > The user USER3 wrote something.
2022-10-31 11:36:02 > The user USER4 wrote something.
R6 wrote something.
2022-10-31 11:36:11 > The user USER7 wrote something.

Conflicts lines below are incompletes (only some characters from the end of the line are written).

Is there a way to prevent this from happening? I want the first user to access the file to be allowed to write to it and the others should get an error trying to write to the file until the first user finishes his write.

Thank you!

I thought that opening the file would lock it until the write was over but apparently users over network are not aware that the file was already open.

Answer 1

By default, VBA opens files in shared mode. You can specify the lock mode when opening a file:

FNum = FreeFile()
Open "\\SHARE\Logs\File.log" For Append Lock Write As FNum
   Print #FNum, Now() & "> The user " & GetUserName() & " wrote something."
Close #FNum

Like Jaspal said in the comments, you can also use locks after opening using the Lock statement. This is mainly useful if you want to lock specific segments of the file but not the entire file, or are doing more with a file than just open-write-close.

Answer 2

Try using FileSystemObject instead. It has different behavior and it might handle the lock better. I've never seen your issue when using this method.

Sub OpenTextFileTest()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    With fso.OpenTextFile("\\SHARE\Logs\File.log", ForAppending, True, TristateFalse)
        .Write Now() & "> The user " & GetUserName() & " wrote something."
        .Close
    End With
End Sub