Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
What are my options on multiple users accessing and editing a MS Excel 2010 workbook on a network?
I am not an expert but I have self taught myself some VBA.
I work in a paper based office and I am currently making a large MS Excel workbook, with 5 sheets, to use as a control point for the paper files. This means all the data also has a diary date attached and the data is edited regularly (such as when work is complete/diary to check the file needs changing etc).
I have used VBA and an auto_close macro so the workbook data re-orders itself across all sheets via the date it needs to be worked and then saves, so that the next person in the document can work the next task in date order - kind of like a diary system. I'm also considering (probably ambitiously) if I can use VBA to link up the diary dates on the workbook with outlook to save time.
I want to role this out to an office of 20 and have considered sharing the document for "collaboration" but this seems like a gateway to hell when I have read up on it. I have also considered SharePoint, but trying to convince my IT department to shell are for this is unlikely. I have also considered MS Access but I think I would have the same issue. An Intranet option seems the most likely work-around from what my un-IT-educated internet trawling brings up.
Does anyone have any suggestions so that each user can edit the document without others being locked in it whilst the file retains the macro function?
Thank-you for any advice.
I work in a paper based office and I am currently making a large MS Excel workbook, with 5 sheets, to use as a control point for the paper files. This means all the data also has a diary date attached and the data is edited regularly (such as when work is complete/diary to check the file needs changing etc).
I have used VBA and an auto_close macro so the workbook data re-orders itself across all sheets via the date it needs to be worked and then saves, so that the next person in the document can work the next task in date order - kind of like a diary system. I'm also considering (probably ambitiously) if I can use VBA to link up the diary dates on the workbook with outlook to save time.
I want to role this out to an office of 20 and have considered sharing the document for "collaboration" but this seems like a gateway to hell when I have read up on it. I have also considered SharePoint, but trying to convince my IT department to shell are for this is unlikely. I have also considered MS Access but I think I would have the same issue. An Intranet option seems the most likely work-around from what my un-IT-educated internet trawling brings up.
Does anyone have any suggestions so that each user can edit the document without others being locked in it whilst the file retains the macro function?
Thank-you for any advice.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Please give some consideration to using Office 2016 for this task. Microsoft has added collaboration to Word and Excel (and possibly others).
I am very new to Office 2016 (it is new), but collaboration (more than one person having the same document open) is a feature of new the new Office suite.
I am very new to Office 2016 (it is new), but collaboration (more than one person having the same document open) is a feature of new the new Office suite.
Thanks John,
I'll take a closer look at 2016, but ultimately, it will be down to my IT department as to whether this is possible or not. I was hoping I could find a work-around without asking my business systems dept. to update the companies version of MS Office.
I'll take a closer look at 2016, but ultimately, it will be down to my IT department as to whether this is possible or not. I was hoping I could find a work-around without asking my business systems dept. to update the companies version of MS Office.
Please let us know. Office 2016 is a good match with Windows 10, which is why I suggested it. Office 2010 wants to keep the document locked (I have Office 2010 at clients).
I have also considered MS Access but I think I would have the same issueSo that you're aware, if you decide to move this to Access, you need a single Full version of Access for your development, but your users only need the free Access Runtime. You can develop the application and deploy it to your users, and they do NOT need anything other than the Runtime to run it. Access can handle multiple users (if configured and deployed correctly), and is generally a better choice than Excel if you must support multiple users.
My experience is quite dated on this subject. Nonetheless, in the past we wanted to have http-based interfacing for the users. That gets away from running app's on all the computers. But, right now, I can't tell you how to implement it.
Long ago we used PageMaker which made publishing a database via http quite easy.
At that time, Microsoft required a Server to support Access to do the same sort of thing.
My sense is that the world has moved on.....
Anyway, you might ask and consider if an http option isn't something worth considering?
Long ago we used PageMaker which made publishing a database via http quite easy.
At that time, Microsoft required a Server to support Access to do the same sort of thing.
My sense is that the world has moved on.....
Anyway, you might ask and consider if an http option isn't something worth considering?
It sounds like a split Access Database would do the trick. This is what I have done recently and works very well, with no sharing issues unless 2 people try to update the same record in the database at the same time, which can also be avoided.
Basically you have one backend Access database which stores all the data, and each user has a frontend file that links to the backend in realtime and updates with any changes they make. It's not at all difficult to implement and once you create the database, access has a wizard to split it for you.
Also I found that having a form show only the relevant data on the screen at one time made it much easier for the end users who are not at all tech-savvy. excel confused them to no end. We have 60 people accessing it at a time with no issues.
Basically you have one backend Access database which stores all the data, and each user has a frontend file that links to the backend in realtime and updates with any changes they make. It's not at all difficult to implement and once you create the database, access has a wizard to split it for you.
Also I found that having a form show only the relevant data on the screen at one time made it much easier for the end users who are not at all tech-savvy. excel confused them to no end. We have 60 people accessing it at a time with no issues.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial