?
Solved

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

Posted on 2011-10-26
9
Medium Priority
?
235 Views
Last Modified: 2012-05-12
* 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.
0
Comment
Question by:Kyakye
  • 4
  • 4
9 Comments
 
LVL 9

Assisted Solution

by:hitsdoshi1
hitsdoshi1 earned 668 total points
ID: 37034833
Give it a shot over here.....you probably need some extra vba coding.....

http://forums.techguy.org/business-applications/669075-excel-shared-read-only-problem.html
0
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 1332 total points
ID: 37041388
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.  
0
 
LVL 11

Assisted Solution

by:ScriptAddict
ScriptAddict earned 1332 total points
ID: 37041441
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!

-SA
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Assisted Solution

by:Kyakye
Kyakye earned 0 total points
ID: 37045615
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.
0
 

Author Closing Comment

by:Kyakye
ID: 37068313
Thank you for your help, and i have split the answer points around.
0
 

Author Comment

by:Kyakye
ID: 37045642
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.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37045710
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.

-SA
0
 

Author Comment

by:Kyakye
ID: 37046272
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
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37073012
I would use option number 2 just capture the error do an

on error goto TryAgain
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  :)

-SA
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question