I have written a small application using Excel vba to update a .txt file, whereby when the user submits an submission, a counter would update the .txt file so that we would know the current running number, and also keeping track of the daily submission.
The .txt file only stores the current number. (i.e. +1 to the current number for every submission).
However, i notice that it causes error when 2 users submitting a submission at the same time. May I know how can i do a check to see if the .txt file is opened, and lock it for just the user only? Or to only allow one update at a time, the rest would be blocked.
Please advise, thank you so much!
Below is the code written to update the .txt file
Sub TextFile_FindReplace(ByRef FileCount As Long)
Dim TextFile As Integer
Dim FilePath As String
Dim fileDate As String, todDate As String
Dim text As String, textline As String
FilePath = "c:\fileNoCount.txt"
todDate = Format(Now(), "dd/mm/yyyy")
TextFile = FreeFile
Open FilePath For Input As TextFile
Line Input #TextFile, fileDate
Close #TextFile
If DateValue(fileDate) <> todDate Then
Open FilePath For Output As TextFile
Print #TextFile, todDate
Print #TextFile, "1"
Close TextFile
Else
Open FilePath For Input As TextFile
Do Until EOF(1)
Line Input #TextFile, textline
text = text & textline
Loop
Close TextFile
FileCount = textline + 1
Open FilePath For Output As TextFile
Print #TextFile, todDate
Print #TextFile, FileCount
Close TextFile
End If
End Sub
