Multi-user Excel, anyone?

[RobertSF]

You know how you can put a spreadsheet on a shared drive, and then anyone can use it, but only one person can use it at a time?

Well, I just got around that limitation.

I work for a huge law firm. We send our closed case files to offsite storage, but we frequently have to bring some of those files back because the attorney needs something from the file, or the case went to a higher court, or something. Among other things, I order and deliver these cases. People send me emails requesting the case they want, and I place the order, and email them back to tell them I've ordered them. When the files arrive, I have to email people again to tell them that their files have arrived. It's a lot of email back and forth for basically nothing, and I got sick and tired of it.

I envisioned a system where users would launch an application and enter their offsite file requests. On the other side, I would launch the application, see those offsite file requests, order them, and then set statuses so that users could check and see if their file had been ordered, received, delivered, or what. We have a network, of course, but we don't have any server database, and we don't even have Access. All we have is Excel, so here's what I did to allow everyone to access the same spreadsheet without error messages or having to open the spreadsheet read-only. Here's how I did it.

First, I put a spreadsheet called server.xlsx on the server. This spreadsheet has no macros. It just contains the data.

Separately, on everyone's workstation, I put a spreadsheet called client.xlsx. This spreadsheet has all the VBA code. When a user needs to request an offsite file, he or she launches the application, which presents the user with a form. When the user fills out the form and submits it, the code opens the server spreadsheet, enters the row of data, closes the server spreadsheet, and shows the user a "done" message. Bam, bam, milliseconds.

On my side, I launch the application, get my little dashboard, and click the button for new requests. The code behind the button opens the server spreadsheet, grabs the new requests, copies them to my local client spreadsheet, and closes the server spreadsheet. Again, bam, bam, milliseconds. After I place the order for these offsite files, I again launch the application. I mark these requests as ordered, and now the users can check the status of their file requests. At all times, the client spreadsheet, under VBA control, opens the server spreadsheet only as long as it takes to get or put data.

I'm basically treating the server spreadsheet as if it were a database. For example, here are two functions to open and close the server spreadsheet.

Public Sub ServerConnect(Optional argMode As String)    
    If LCase(argMode) = "w" Then 'open for write
        Workbooks.Open FileName:=appData.ServerFQN
    Else ' open for read
        Workbooks.Open FileName:=appData.ServerFQN, ReadOnly:=True
    End If
End Sub

Public Sub ServerClose(Optional argMode As String)
    If LCase(argMode) = "s" Then 'save and close
        If Workbooks(appData.ServerFile).Sheets(1).ListObjects(1).ShowAutoFilter = True Then
            Workbooks(appData.ServerFile).Sheets(1).ListObjects(1).AutoFilter.ShowAllData
        End If
        Workbooks(appData.ServerFile).Save
    End If
    Workbooks(appData.ServerFile).Close SaveChanges:=False
End Sub

(appData is a user defined variable I use to keep app data).

Here's how I use it. This code deletes a request record. It opens the server spreadsheet in "write" mode, arm-waving ensues, and then it closes the server spreadsheet in "save" mode (the arm-waving finds the record and deletes it).

Public Sub DeleteRequest()

    Dim tbl As ListObject
    Dim key As Range
    Dim deleteRow As Range
    
    ServerConnect "w"
    
    Set tbl = Workbooks(appData.ServerFile).Sheets(1).ListObjects(1)
    Set key = tbl.ListColumns(1).Range.Find(gRecord(1, RecFlds.RecID))
    Set deleteRow = key.EntireRow.Resize(1, kFieldCount)
    Application.DisplayAlerts = False
    deleteRow.Delete
    Application.DisplayAlerts = True
    
    ServerClose "s"
    
End Sub

Like I said, milliseconds.

Of course, this is not real multi-user. Collisions are still possible; it's just that they're very unlikely in an ordinary office environment, where maybe a dozen people access a resource in what seems to be "at the same time."

For our purposes, this works great! The users like it because they can check on their file requests without having to wait for me to email them back.

Truthfully, I always knew it would work in theory, but I was skeptical about it working in the practice. But it worked fine. There's a blinking of the screen as the application opens and closes the server spreadsheet, but the user experience is remarkably smooth, even on PCs of ordinary power.

 

[mole999]

2010 >> http://blog.contextures.com/archives/2012/07/24/shared-workbook-limits-in-excel-2010/ (Sharing)

2016 >> https://support.office.com/en-ie/ar...-feature-49b833c0-873b-48d8-8bf2-c1c59a628534 (Co Authoring)

 

[Kyle123]

You could avoid even more collisions if you used ADO as you wouldn't need to open the workbook - it's still not collision proof, but it should be faster

 

[RobertSF]

ADO, huh? I was thinking of using VB.Net with an Access file (I have Access at home, but not at work), but I'll give ADO a try. I just found this article, which should be enough to get started. https://technet.microsoft.com/en-us/library/ee692882.aspx

 

[RobertSF]

Thanks, mole999. I've been to contextures.com, but hadn't seen that particular article.

 

[RobertSF]

Ok, I finished changing the code from opening the workbook to using ADO. I really appreciate the tip. It is much faster! It's also completely invisible to the end user.

The Excel driver is functional but has some limitations. It cannot delete rows from a sheet, and maybe it was me, but I couldn't get the Recordset.Update method to work -- no error, it just didn't do anything. On the other hand, you can use SQL! (just not to delete) For our purposes, it's great!

 

[Fazza]

It is not just you, Robert, the Excel driver can not delete records. Maybe you can work around that by populating a field to record the status? That is, DONE, or DELETED, or FALSE, or whatever works for you. Another way is you can have an mdb file instead of the "server.xlsx" file- then you can truly delete records. To do this, no one need have MS Access; you just use an mdb file. You can btw create this from VBA - via ADOX.

Recordset.Update method? Can you just use an UPDATE query?

Sounds like a great result. Well done.

 

[RobertSF]

Thanks, and yes, I eventually found it documented. Fortunately, a delete would be required very infrequently, so I got around it by using brute force. The code opens the spreadsheet, finds the row, and deletes it. Just having SQL was great. Coming from PHP, I'm comfortable building SQL commands.

 

[Fazza]

Excellent. I'm a big user/fan of SQL in Excel. As I like to say, a little SQL can do a lot of work.

I'm guessing with the success of this project you'll find some more where you can also dramatically improve work flows, and enjoy doing the work along the way.