Two computers trying to access a excel file in a third computer (server) to write. Problems with the method

Question

I've made a POS system for a friend. The POS system is a excel workbook that runs in a desktop computer with a touchscreen. For every sale it accesses a file in the server called products.xlms were I update products quantities.

The problem arises when a second terminal tries to access (to write) the same file at the same time in the server, because if the first computer is alreday using the file, the second computer will open it in Read only mode.

I thought I made a walkaround usign the following function that I found here at stackoverflow:

Function IsWorkBookOpen(filename As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open filename For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0:     IsWorkBookOpen = False
Case 70:    IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

This function opens the file to check if is being used, and closes returning an Error. Depending the Error number we are able to know if its open or not.

Then I call the function whenever I want to open the file, and it will wait a second and try again if the file is open already:

Check:
Ret = IsWorkBookOpen("PATH\products.xlsx")
If Ret = True Then
    Application.Wait Now() + TimeSerial(0, 0, 1)
    GoTo Check
End If
Workbooks.Open("PATH\products.xlsx")

However, It doesn't work for me like this because interactions are pretty quick and the best way to show it is to explain the problematic scenario:

• Terminal 1 checks if file is open: Opens file, closes, no error then variable = False. Then opens the file (2nd time) to work with.

• If terminal 2 opens the file just when terminal 1 closed it for the first time, BUM, I have a problem beacuse it will think is unused (and thats true! It was actually unused in that fraction of time), and proceed to open it again (In Read only Mode cause is actually being used).

Hope is clear, I will try to clarify if not. Any suggestion, workaround?

Thanks

Answer 1

I should note that I agree with Mark Butler; you're definitely making this hard on yourself by using Excel as the "database". However, what's done is done, so here's what I would try...

Sub YourSub()
    Dim WB As Workbook
    Dim YourFile As String

    'Note the ~$ in front of the file name
    YourFile = "C:\Users\USERNAME\Documents\~$Book1.xlsx"
    Do While IsFileOpen(YourFile)
    Loop

    'File should be available to you now
    Set WB = Workbooks.Open(Replace(YourFile, "~$", ""), ReadOnly:=False, Notify:=False)

End Sub
Function IsFileOpen(fPath As String) As Boolean
    Dim FSO As Object 'FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(fPath) Then
        IsFileOpen = True
    End If
End Function

The logic behind this code is that when another user has an Excel file open, Excel creates a 'lock file' with the ~$ prefix. This code checks for whether that lock file exists and if it doesn't it opens the file. This will be far more efficient than the workaround you posted which has to open the entire file each time it needs to check whether the file is in use (not a big deal now, but when you have thousands of rows of data it becomes a much bigger deal).

However, big caveat here, sometimes lock files won't be deleted after the file is closed. In a situation like that, your application(s) would enter an infinite loop since the lock file would perpetually exist. One way to avoid this would be to add some sort of counter so that once the loop hit the count maximum you set (e.g. 100000) it would open the file anyway and check for read only that way.

Another option would be to create a text file with your code immediately before you open the Excel file. Then when you're finished with Excel you delete the text file (basically emulating the 'lock file' I mentioned earlier). This would still be efficient and wouldn't rely on an Excel lock file. To do this try this code:

Sub YourSub()
    Dim WB As Workbook
    Dim CheckFile As String
    Dim YourFile As String

    CheckFile = "C:\Users\USERNAME\Documents\OpenCheck.txt"
    Do While IsFileOpen(CheckFile)
    Loop

    'File should be available to you now
    YourFile = "C:\Users\USERNAME\Documents\YourFile.xlsx"

    Set WB = Workbooks.Open(YourFile, ReadOnly:=False, Notify:=False)
    'And then when you're done with the excel file
    WB.Close SaveChanges:=True
    Kill CheckFile   
End Sub
Function IsFileOpen(fPath As String) As Boolean
    Dim FSO As Object ' FileSystemObject
    Dim TS As Object ' TextStream

    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(fPath) Then
        IsFileOpen = True
    Else
        On Error GoTo AlreadyCreated
        Set TS = FSO.CreateTextFile(Filename:=fPath, overwrite:=False)
        TS.Close
    End If
ExitFunc:
On Error GoTo 0
Exit Function

AlreadyCreated:
    IsFileOpen = True
    Resume ExitFunc
End Function

Obviously the text file would have to be saved to your server. I don't have any means right now to test this approach, but as far as I know it should work well.

Answer 2

I found a workaround. Instead of checking first and then opening, I will open the file and then check if it is ReadOnly mode, with the code below:

Dim wkb As Workbook
Check:
'disable alerts to skip the excel message "file been used, Open Read Only?"
Application.DisplayAlerts = False
Set wkb = Workbooks.Open((Config.Range("O2").Value) & "\clientdb.xlsx", Notify:=False, ReadOnly:=False)
Application.DisplayAlerts = True
If wkb.ReadOnly Then
    wkb.Close
    'Wait code here
    Application.Wait Now() + TimeSerial(0, 0, 1)
    GoTo Check
End If

If is ReadOnly it means is open somewhere, then it will wait 1 second and try again, until it opens the file as write enabled.

The user Reen the Winter, who posted an answer has some credit because he help me think.

Thanks you all for the responses.

Answer 3

The problem is, that Worksbooks.Open opens the workbook too. You open the workbook in the IsWorkbookOpen Function and again to write the actual values. You have to put the two things together.

Try to open it via Workbooks.Open ReadOnly:=False, Notify:=False, that will throw an error like your function, which you can check. Like:

Dim wkb As Workbook
On Error Resume Next
Do
    'Clear existing (old) Error-Code
    Err.Clear
    'Try to open
    Open "Path/test.xlsx" For  Input Lock Read Write As #ff
    Set wkb = ActiveWorkbook

    If Not Err.Number = 0 Then
        'Workbook is opened from another client, put Wait-code here
    End If
    'If Workbook is open on this client, Error-code is 0 and the loop exits
Loop Until Err.Number = 0
'Write the Values, use wkb
On Error GoTo Errorhandler 'Its always good to catch Errors in an Errorhandler
'Write the Values, use wkb 

I can't test the Error-Numbers, so you have to check that for yourself.

Answer 4

Suggestion: Don't use Excel for multi-user scenarios. In fact, don't use Excel for anything except spreadsheets. It's a fantastic spreadsheet application and not much good when forced to do anything much else; database applications, POS systems etc. are not what it was designed for. Square pegs, round holes and all that.

If you are keen to stick with MS Office, why not use Access for this?

Create a front-end app which runs on each of the touchscreen terminals, and connect it to a back-end database which is stored centrally. This will save you so much headache in the future.