Question on shared workbooks (not a stupid question, I promise).

**swoop99** · 06-16-2011, 06:19 PM

ok, here goes. I have a shared workbook that is going to be accessed by another workbook which always opens read only. Users of the R/O workbook will hit a macro button located within it and what then happens is the shared workbook is opened, a specific cell within it will be changed, the shared workbook saved and then closed. However, every user doing this will be saving to a different cell.

All this happens in VBA code very quickly.

The question I have is about how excel saves to a shared workbook.

I know that when it's actually saving, excel locks the shared workbook until the save action is finished. That's a not a problem to deal with, I can just put in code that while .fileislocked = true to wait. However, what I need to know is exactly what is saved when a user opens a shared workbook, changes 1 cell and saves it. Does Excel save the entire workbook or does it just save that cell?

The workbook will be accessed by around 30 people at the same time, saving single cell changes to it around every 2 minutes (each).

I also know that in the shared workbook options I can have an option to either have a dialog box appear when two users try to save to the same cell, or just have the changes being saved overwriting whatever is there. That also shouldn't be an issue as no two users will ever write to the same cell at the same time in the setup I have, it'll always be different cells.

However.....because I'm not keeping the shared workbook open all the time on each users PC I need to know how the save event works. Is it possible that two users could open the shared workbook at the same time, one of them saves data to a cell and a split second later the other one saves to a different cell.....will the second save overwrite the change to the first cell?

The options in shared workbooks kind of imply that it won't.....for example, the option of seeing changes other people have made when you save. But it doesn't state it explicitly, therefore before I implement this way of doing things I'd like to know if it will actually work or if I need to think of something else.

I hope I've made this clear enough, it's causing my poor, tired brain to overheat.

**Roger Govier** · 06-16-2011, 06:25 PM

HI

IMO don't do it!!
Shared workbooks are a disaster waiting to happen.

IF you have multiple inputs to a file, you need to have individual record locking, which is something Excel does not provide.

Use Access instead and store your data in an Access table.
By all means use Excel to pull data from that table for reporting.

Just my 2 cents!!

**swoop99** · 06-17-2011, 06:13 AM

Alrighty, after much wailing and banging of head against wall I've decided that instead of re-writing 160,000 lines of code to use Access (er, and learning how to do that in the first place) does anyone see any issues with this:

The shared workbook being used as a data dump to be not a shared workbook anymore.

[code]

Function IsFileLocked(filePath As String) As Boolean
On Error Resume Next
Open filePath For Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
IsFileLocked = True
Err.Clear
Else
IsFileLocked = False
End If
End Function

----------------------------------------------------------------------
[code]

and in my main code:

[code]

path = range("D10").value
Application.DisplayAlerts = False

100
If IsFileLocked(path) Then GoTo 100

Workbooks.Open (path)
Sheets("db").Select
Cells(user, 20).Value = testresult
ActiveWorkbook.Save
ActiveWindow.Close

Application.DisplayAlerts = True

[code]

In english:

Before a user opens it to write to a cell it first checks to see if it's locked, ie another user is saving something. If it is then keep checking until it isn't, then you can save.

**martindwilson** · 06-17-2011, 08:09 AM

one of them saves data to a cell and a split second later the other one saves to a different cell.....

if this happens the second one would be held up untill the the first one had finished saving and then the warning message would pop-up as usual ,well thats what generally happens
but it could fail completely,bloat the workbook up to 10 meg + or so and create an unspecified file with a name like ab123c23