Multi-user Excel, anyone?

RobertSF

Board Regular
Joined
Jun 20, 2017
Messages
104
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.

Code:
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).

Code:
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,714
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

Board Regular
Joined
Jun 20, 2017
Messages
104

ADVERTISEMENT

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

RobertSF

Board Regular
Joined
Jun 20, 2017
Messages
104
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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

Board Regular
Joined
Jun 20, 2017
Messages
104
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,774
Messages
5,638,262
Members
417,018
Latest member
Khan kashaf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top