Looking for options to resolve performance issues in Shared Workbook

[EricL89]

Here's the situation:

• I've got an XLSM workbook in a shared network folder which is a trusted location.
• It's a shared workbook used by up to 7 different users at any given time; Office versions include 2007, 2010, and 2013.
• The workbook is slow to open, save, close, and run the macro. Occasionally, this results in "Not Responding".
• Every now and then, somebody will receive a notification that the file is locked by another user, even though they just saved some edits a few minutes prior.

With all of this happening, I'm looking for a resolution to make things faster and easier. I've tried researching for some resolutions, but I'm not sure which is best suited for my situation. I've seen one possibility mention having multiple workbooks feeding into a master workbook, which I'm not to sure how to do since the master would be the shared macro workbook. Additionally, we're using this workbook to keep track of prospect progress, so it would be easier if we only have the one workbook to worry about.

Another option I saw mentioned creating a document worksite, which would allow multiple users to make edits and see real-time updates, and this would all sync with a localized master file. This seems to be the best option so far, and it seems that SharePoint would be the way to go. Unfortunately, I'm not sure which SharePoint program would work for us, considering we have users with varying Office versions. I was originally looking at Windows SharePoint Services 3.0 with Service Pack 2, but it does not support the Windows 2011 Server we're using. SharePoint 2013 looks like it only works with Office 2013, which only two users have installed.

What would you recommend? SharePoint seems like the way to go, but I have no idea which one to go with. Otherwise, is it possible to create an online document worksite which would allow multiple users to make edits which would sync with a local file? Any help/insight would be greatly appreciated!

Thanks!

Eric

 

[BrianMH]

I hate shared workbooks because of all the issues you've mentioned and more. You would be better off with an access database set up in a split front end/back end format. I honestly don't think there is a practical way to make a shared workbook that doesn't have issues.

 

[EricL89]

I hate shared workbooks because of all the issues you've mentioned and more. You would be better off with an access database set up in a split front end/back end format. I honestly don't think there is a practical way to make a shared workbook that doesn't have issues.

I was actually just thinking about that. I don't have too much experience with Access, but it's a moot point; not everybody has Access. I, for one, am running Office 2007 and I've only got Word, Excel, and PowerPoint.

 

[BrianMH]

If you can get access installed so you can design the application then you can get a free access runtime so everyone can run it.

You could also design a frontend in excel and use access as a backend. This would require intermediate VBA skills though.

 

[EricL89]

So if just one person has Access, I can get everybody to run it by getting free Access runtime? Would it matter which version of Office was used to design the application (i.e. designed in 2013, free runtime for 2007)?

Additionally, this workbook is using a macro which sorts prospects from the master worksheet to another worksheet based off whether the prospect was sold or lost. The macro will go down the list and will only sort the prospects (one in each row) if columns A-J are filled. Column K is the determinant, "Yes" sends the prospect to the Sold worksheet, and "No" goes to Lost. If Column K is filled but there is an empty cell in A-J, it shows an error message saying that the prospect info is not complete. Furthermore, if you change the entry in column K of prospects already moved to Sold or Lost (i.e. changing Yes to No / No to Yes, or deleting the entry), the prospect will get moved to Lost/Sold or back to Master, respectively. All of this happens when you click the button attached to the macro.

Is Access capable of accomplishing all of this?

 

[BrianMH]

I'd personally design your app in access 2003 and then use that runtime for everyone. I've found 2003 has better performance over the network than newer versions.

Yes you can do all that sort of functionality but you do it in a different way. You store the data in tables with fields for their status and update that status. Then you can use queries and filters to get only the data you need. It is a quite different way of working than excel but it is a real database so once you get used to it then it works much better for this type of application.