Subject: Shared workbook for Writing

[Joel]

I posted this question on Saturday and didn't get a great anser. Thought I
would repost during the week when more people will read the posting.

I don't have a lot of experience with shared workbooks in Excel and not sure
what will happen if two people try to write the file at the same time.

1) What is the best method with Excel 2003 and Excel 2007 for sharing a
workbook and letting multiple users write file through Excel VBA? Also
simulataneously allowing users to read the file as a worksheet. Do you need
to lock the file during the writes. Obviously, the readers won't get any
updates to the file that are made will the book is opened.


2) Is it better to use Access instead of Excel? I would think more people
know how to use Excel than Access so Excel would be the preferable way of
going.

3) Would using GETOBJECT("book1.xls") from excel work? Can two users open
the workbook for writing and both be able to write data successfully?

 

[john]

Joel,
When sharing workbook data across network for multiple users I create a
database (using another workbook) that users read / write to / from.
The approach is a simple one:

1- user opens their copy of workbook which is populated (copied) from your
database(read only) with current data where required;
2- user submits new / updated data to database (read / write)

Database workbook is only ever open for a short moment. In read only mode,
no special checking is needed. When writing to database however, your
application needs to check if any other user is performing same task. There
are plenty of examples to check if a workbook is open read / write mode
available and here is one of them:

'Check if Read / Write File Already Open
If FileLocked(DatabaseFile) Then
Exit Sub
Else
Set DestWB = Workbooks.Open(DatabaseFile, Password:=passwrd)
End If


Function FileLocked(strFileName As String) As Boolean

'''''''''''''''''''''''''''''''''''''''''''''''''''
'Function tests if file open Read / Write
'''''''''''''''''''''''''''''''''''''''''''''''''''

On Error Resume Next
''''''''''''''''''''''''''''''''''''''''''''''''''''
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
''''''''''''''''''''''''''''''''''''''''''''''''''''
Open strFileName For Binary Access Read Lock Read As #1
Close #1

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' If an error occurs, the document is currently open.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
If Err.Number <> 0 Then
FileLocked = True
Err.Clear
End If
End Function
On the rare occasion that a conflict may occur, you just report to OP that
file is in use & ask them to try again.

A couple of years ago I created a timesheet application for charity my
daughter works for using above principles in Excel 2003. Each week, 250 staff
will submit a timesheet to the database & so far, above approach has not
failed. In fact system does more than just record time, Staff can submit
holiday requests, download calendars showing individual / department holiday
dates etc.
Using Access would have been easier but Excel was the required tool.
So it really depends on the complexity of what you are intending to do.
Excel can in most cases work well but may prove to be a vast amount of work
for you!

Hope of some help