Hello,

I have a shared database set up in Excel where approximately 30 users
contribute data, weekly. Each user has a specific (and unique to others)
record they are responsible for. The database is set up on a shared LAN
drive. Each user has been instructed to update the database "in-place".
That is, not to take a copy of it, update their record and resave the entire
file back to the original location. This is obviously to maintain security
and version control.

Recently, we've heard complaints from some users that their data entered the
prior week "reverted" back to old data. A couple of reasons for this come to
mind:
1. The user did not save the workbook when they finished entering data.
2. A user did not update the database "in-place" and thus overwrote other's
records and possibily old data.
3. Some technical problem with excel and shared workbooks unknown to me?
4. Others perhaps?

Question regarding # 2. How do I prevent a user from saving a copy,
updating the record and rewriting of the database back to its location?

Regards

Excel isn't really designed to do what you want to do. It's not a
collaboration tool, and doesn't handle multiple users well like a
conventional database. I suppose it's possible to time stamp when a change
is made to a workbook via a macro, but even there, that is easy enough to
circumvent, either on purpose or inadvertently, by not enabling macros upon
opening the workbook.

If you really need to be able to time stamp people's entries you need a
database, not an Excel spreadsheet.

Dave
--
Brevity is the soul of wit.


"Scott" wrote:

Hello,

I have a shared database set up in Excel where approximately 30 users
contribute data, weekly. Each user has a specific (and unique to others)
record they are responsible for. The database is set up on a shared LAN
drive. Each user has been instructed to update the database "in-place".
That is, not to take a copy of it, update their record and resave the entire
file back to the original location. This is obviously to maintain security
and version control.

Recently, we've heard complaints from some users that their data entered the
prior week "reverted" back to old data. A couple of reasons for this come to
mind:
1. The user did not save the workbook when they finished entering data.
2. A user did not update the database "in-place" and thus overwrote other's
records and possibily old data.
3. Some technical problem with excel and shared workbooks unknown to me?
4. Others perhaps?

Question regarding # 2. How do I prevent a user from saving a copy,
updating the record and rewriting of the database back to its location?

Regards

Scott,

The number one problem is that you are using a spreadsheet program, Excel,
instead of a database program, like Access. Excel simply doesn't have the
controls needed.

You also shouldn't overwrite existing data. You should add new records. So
if there is data for Scott for January 2007 it should stay in the database
forever, essentially, unless you made an error in its entry that needs to be
fixed. Otherwise, create a new entry for Scott for February 2007 and so on.

One possible solution would be to have a blank data entry workbook with a
single worksheet, that is saved in a folder designated as a data input
folder after the new data is input by each worker. But only one person
opens the database file, and runs a macro that 1) opens each of the files
in the data input folder 2) imports the data into the database 3) moves the
file out of the data input folder so that there isn't any duplication of
imported data.

Regarding question #2, insert code into the before save event that cancels
any SaveAs attempt:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If SaveAsUI Then
MsgBox "No saving under a different name, please."
Cancel = True
Else
MsgBox "Saving OK"
End If
End Sub

HTH,
Bernie
MS Excel MVP




"Scott" wrote in message
...
Hello,

I have a shared database set up in Excel where approximately 30 users
contribute data, weekly. Each user has a specific (and unique to others)
record they are responsible for. The database is set up on a shared LAN
drive. Each user has been instructed to update the database "in-place".
That is, not to take a copy of it, update their record and resave the
entire
file back to the original location. This is obviously to maintain security
and version control.

Recently, we've heard complaints from some users that their data entered
the
prior week "reverted" back to old data. A couple of reasons for this come
to
mind:
1. The user did not save the workbook when they finished entering data.
2. A user did not update the database "in-place" and thus overwrote
other's
records and possibily old data.
3. Some technical problem with excel and shared workbooks unknown to me?
4. Others perhaps?

Question regarding # 2. How do I prevent a user from saving a copy,
updating the record and rewriting of the database back to its location?

Regards

Scott,

Here's what a fellow MVP (Jan Karel Pieterse) has to say about shared workbooks:

****************
General remarks.

I frequent the crashes/gpf's newsgroup regularly. Due to the number of reports
about problems with shared workbooks I always advise against using the feature
in the first place, especially in the case where the goal is to enable
simultaneous editing of the data. The kinds of problems I have seen reported
a

- (partly) loss of edited data
- changes not saved
- corruption of file, with sometimes complete loss of file

Now I don't know the status or relative frequency of problems with shared
workbooks, nor do I know if stability has improved over the versions up to
2007, but I tend to say: wanna share data? use Access or any other multi-user
centric application rather than Excel.
*****************

HTH,
Bernie
MS Excel MVP


"Scott" wrote in message
...
Hello,

I have a shared database set up in Excel where approximately 30 users
contribute data, weekly. Each user has a specific (and unique to others)
record they are responsible for. The database is set up on a shared LAN
drive. Each user has been instructed to update the database "in-place".
That is, not to take a copy of it, update their record and resave the entire
file back to the original location. This is obviously to maintain security
and version control.

Recently, we've heard complaints from some users that their data entered the
prior week "reverted" back to old data. A couple of reasons for this come to
mind:
1. The user did not save the workbook when they finished entering data.
2. A user did not update the database "in-place" and thus overwrote other's
records and possibily old data.
3. Some technical problem with excel and shared workbooks unknown to me?
4. Others perhaps?

Question regarding # 2. How do I prevent a user from saving a copy,
updating the record and rewriting of the database back to its location?

Regards

Long story short is that Excel isn't designed as a collaborative tool.
So it shouldn't be used as such. You don't drive a screw with a
hammer, after all.

On Jan 24, 9:28 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Scott,

Here's what a fellow MVP (Jan Karel Pieterse) has to say about shared workbooks:

****************
General remarks.

I frequent the crashes/gpf's newsgroup regularly. Due to the number of reports
about problems with shared workbooks I always advise against using the feature
in the first place, especially in the case where the goal is to enable
simultaneous editing of the data. The kinds of problems I have seen reported
a

- (partly) loss of edited data
- changes not saved
- corruption of file, with sometimes complete loss of file

Now I don't know the status or relative frequency of problems with shared
workbooks, nor do I know if stability has improved over the versions up to
2007, but I tend to say: wanna share data? use Access or any other multi-user
centric application rather than Excel.
*****************

HTH,
Bernie
MS Excel MVP

"Scott" wrote in ...



Hello,

I have a shared database set up in Excel where approximately 30 users
contribute data, weekly. Each user has a specific (and unique to others)
record they are responsible for. The database is set up on a shared LAN
drive. Each user has been instructed to update the database "in-place".
That is, not to take a copy of it, update their record and resave the entire
file back to the original location. This is obviously to maintain security
and version control.

Recently, we've heard complaints from some users that their data entered the
prior week "reverted" back to old data. A couple of reasons for this come to
mind:
1. The user did not save the workbook when they finished entering data.
2. A user did not update the database "in-place" and thus overwrote other's
records and possibily old data.
3. Some technical problem with excel and shared workbooks unknown to me?
4. Others perhaps?

Question regarding # 2. How do I prevent a user from saving a copy,
updating the record and rewriting of the database back to its location?

Regards- Hide quoted text -- Show quoted text -

Dave,

Unfortunately, this hammer has an attachment that appears to be a screwdriver....

Bernie
MS Excel MVP


You don't drive a screw with a
hammer, after all.

That's a good way of putting it.

On Jan 24, 9:46 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Dave,

Unfortunately, this hammer has an attachment that appears to be a screwdriver....

Bernie
MS Excel MVP



You don't drive a screw with a
hammer, after all.- Hide quoted text -- Show quoted text -

Thanks all for your ideas. Yes, perhaps it's time to transistion to a
database, especially if we think that this may grow in size and
users....Thanks again. Scott

"Bernie Deitrick" wrote:

Dave,

Unfortunately, this hammer has an attachment that appears to be a screwdriver....

Bernie
MS Excel MVP


You don't drive a screw with a
hammer, after all.