multiuser Excel application

[unluckyuser]

Pondering the design of a front end that is forms driven and unchanged by the end user, who knows nothing of Excel. So if there's a crash, that file cannot be corrupted and even if it were, it's easily restored. The multi user data file will be in a different location. The only time I see any risk of corrupting is during the file writing process, when a field is changed and the file is saved. How would I prevent multiple people from trying to save simultaneously? Or is this really even a concern?

 

[jkpieterse]

Sounds like a database application to me. Much better served using a platform like MSAccess.

 

[unluckyuser]

It is, and I know that. Unfortunately, the company only has Excel and won't budge on anything else.

 

[jkpieterse]

IIRC you only need one subscription with msAccess installed, the user base only needs the runtime

 

[dmt32]

How would I prevent multiple people from trying to save simultaneously?

Hi
As already stated, a database probably more suited to what you are trying to do but if your users will have access to the common workbook on your corporate network (LAN or WAN) then for each user to write data from their template file and avoiding write conflicts, you would need to first check that the workbook is not open write mode.

You should be able to do with simple function place in standard module in each template workbook

Function FileInUse(ByVal FileName As String) As Boolean
    
    'check if file already open read/write
    On Error Resume Next
    Open FileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = Err <> 0
    On Error GoTo 0
    
End Function

You call the function in the code for each template workbook that posts the users data as follows:

Const strFileName As String = "S:\Users\unluckyuser\documents\shared filename.xlsx"
    
    If Not FileInUse(strFileName) Then
        'open file
        'do stuff
        'close & save file
    Else
        'inform user
        MsgBox "File In Use"
    End If

You will need to specify the correct filepath / filename but hopefully, suggestion will do what you want.

Hope Helpful

Dave

 

[jkpieterse]

@dmt32 Does this work reliably if the file is on either OneDrive or Sharepoint and in co-Authoring mode?

 

[dmt32]

@dmt32 Does this work reliably if the file is on either OneDrive or Sharepoint and in co-Authoring mode?

Unlikely for any of those situations which is why I stated LAN or WAN network.
Solution is a cut down version I implemented about 15+ years ago for my daughter with a similar requirement to OP at her place work where 250 users across UK submitted timesheet data each week to a common workbook - no conflict issues were ever reported.

Dave