How can i allow multiple pushes of data to a single shared workbook?

* Office 2007 *

Good Afternoon All,

The Project was to have multiple workbooks (lets call these StartBooks) for different areas of the business showing similar metrics, and these would be rolled up to a single document that holds aspects of the originals along with a lot of information from around the business (Lets call this FinishBook).

Creating all of those was fine; however an issue came about when those using the StartBooks tried to push their updates at the same time.

My users where receiving a couple of messages, however they were all based on the same issue of file is read only, the err details are

Number: 1004
Desc: <File Name> is read-only. To save a copy, click OK, then give the workbook a new name in the Save As dialog box

Is there something that I’m missing with regards to multiple users trying to push their data to a single shared Workbook?

The Pushing of data works fine if only one person is doing it from their StartBooks; it also works perfectly if there is someone in the FinishBook viewing it. The error only happens if multiple users push their data at the same time.

Any suggestions would be greatly appreciated, and if there should be any questions about this please do not hesitate to ask.
Who is Participating?
ScriptAddictConnect With a Mentor Commented:
You'll get a better response on this if you post the workbook.  We love to dig into a sheet and troubleshoot, but hate to try and guess.  

I've searched a bit on this and all I get is that it is likely related to networking.  

You could build it some vba that checks if it is busy before attempting a save and only going forward if it isn't busy.  Even in pure enviroments like sharepoint, it requires users "check-out" files for use.  I think the reality is you can only have one person writing to it at a time.  
hitsdoshi1Connect With a Mentor Commented:
Give it a shot over probably need some extra vba coding.....
ScriptAddictConnect With a Mentor Commented:
Everything I read still leaves me to believe that only one person can write to this file at a time, even though many people can be working on at once.  I think my first solution to have your start sheets check and see if the file is busy before attempting to save, and then if it is, to try again after a set time is going to be your best bet.

Great Reference

Hope that helps!

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

KyakyeConnect With a Mentor Author Commented:
Well obviously I have been looking into this issue, and so far have not found a suitable solution.

Unfortunately I cannot post the workbooks in question due to they hold sensitive business data.

So far I have come across a couple of possible work around for this, they are;

1 - Using objExBk1.ReadOnly to return a true/false, however I do not think this is working in this situation as it always seems to return false
2 - Capture the event and bump the code back up the process to repeat and try again.

I have opted for a third option - Do Nothing. Basically the chance that two users will be sending their updates at the same time (or within a few seconds) is slim, and so I have educated the users that if they see a certain message please re-run the macro by clicking the button.
KyakyeAuthor Commented:
Thank you for your help, and i have split the answer points around.
KyakyeAuthor Commented:
Also i fortgot to mention i think the issue here is when multiple updates are trying to save the same file with the same name at the same time, thus as far as i know there is no way around this.
If it becomes a big problem for you just let us know and I'm sure one of the expert vba coders will be able to put together something that should help.

KyakyeAuthor Commented:
Thank you SA, I know a fair bit if vba but things like this often leave me a little perplexed.

Thanks again

* post sent from iPhone
I would use option number 2 just capture the error do an

on error goto TryAgain

Open in new window

It's a little dangerous because it could loop endlessly if someone is keeping the file locked all the time.  But that's not very likely, I might toss in a counter and if the counter is reached just have it toss out a msgbox that says the file is unable to be written to, and then tell who using their excel user ID  :)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.