J
Jean-Luc
I) Scenario: A 3Mb Excel 2007 workbook with a single tab sheet is shared
between 10 to 15 users who make changes to unprotected cells only and save
regularly after every change is made. Each user makes about 100 to 200 saved
changed over an 8 hour shift and a time span of 2-3 weeks. Networked PCs have
2GB RAM with users accessing 3 to 5 other programs besides Excel (Outlook,
Acrobat, LPS, Company Intranet site, Internet sites,...)
II) Issue/symptoms:
a. everything works smoothly for 1 hour or 2, then some users start to get
the message that the sheet is locked and they need to "try again later to
save".
b. the number of users who haev this problem grows rapidly from that point on.
c. at first, users who get out of the sheet and go back in, can save again
for a few minutes, until the same message starts appearing again.
d. after several users have been experiencing the same problem and gone out
and back in, it gets to a point where no single user can even get out and
back in, and a "locked by other user" message appears not allowing users to
even get out.
e. at this point, users can only get out by using the task manager and close
Excel completely. They can then get back in and save information for about 1
or 2 minutes, after which they cannot do anything else but close Excel
completely again via task manager.
III) What has been so far done attempting to "resolve" this problem:
a) Network configuration:
1. verified that all users have the same typ of PC (hardware/software) and
also have exactly the same priviledges/permissions
b) Excel settings: Review/Share workbook/Advanced tab
2. Track changes: reduced to 7 days from 30
3. Update changes: automatically to 5 minutes ("Save my change and see
other's")
4. Conflicting changes between users: "The changes being saved win"
b) File management practice:
5. once a week: make a copy of the history changes, unshare the file and
reshare it to start fresh.
6. 3 to 4 times a week: go to Review/Share workbook/Editing and remove
duplicate users [1 by 1 is the only way this can be done] whose name appear
several times after they have to exit Excel and go back in).
7. remind users to always acknowledge any error message popping up, i.e. not
leave it there on their sheet and go do something else.
8. remind users not to make copies of the sheet without changing its name,
but only its location, and then save their changes to that new copy of theirs.
IV) QUESTIONS:
1. Among these 8 measures listed, which do you believe actually works and
makes sense as having an impact?
2. Is there some type of "capacity limitation" inherent to Excel (offical or
not) to be aware of when using Excel shared workbooks? Is a 3MB file size an
issue as it grows to 4 to 10MB with its history? Are 10 to 15 users an issue,
or should 100 be working fine too? Is the 150 x 15 number of changes being
made any day to the file an issue or should it not be? etc.
3. Do you have other suggestions or tips on where to look to help resolve
this issue?
Thanks!
Jean-Luc
between 10 to 15 users who make changes to unprotected cells only and save
regularly after every change is made. Each user makes about 100 to 200 saved
changed over an 8 hour shift and a time span of 2-3 weeks. Networked PCs have
2GB RAM with users accessing 3 to 5 other programs besides Excel (Outlook,
Acrobat, LPS, Company Intranet site, Internet sites,...)
II) Issue/symptoms:
a. everything works smoothly for 1 hour or 2, then some users start to get
the message that the sheet is locked and they need to "try again later to
save".
b. the number of users who haev this problem grows rapidly from that point on.
c. at first, users who get out of the sheet and go back in, can save again
for a few minutes, until the same message starts appearing again.
d. after several users have been experiencing the same problem and gone out
and back in, it gets to a point where no single user can even get out and
back in, and a "locked by other user" message appears not allowing users to
even get out.
e. at this point, users can only get out by using the task manager and close
Excel completely. They can then get back in and save information for about 1
or 2 minutes, after which they cannot do anything else but close Excel
completely again via task manager.
III) What has been so far done attempting to "resolve" this problem:
a) Network configuration:
1. verified that all users have the same typ of PC (hardware/software) and
also have exactly the same priviledges/permissions
b) Excel settings: Review/Share workbook/Advanced tab
2. Track changes: reduced to 7 days from 30
3. Update changes: automatically to 5 minutes ("Save my change and see
other's")
4. Conflicting changes between users: "The changes being saved win"
b) File management practice:
5. once a week: make a copy of the history changes, unshare the file and
reshare it to start fresh.
6. 3 to 4 times a week: go to Review/Share workbook/Editing and remove
duplicate users [1 by 1 is the only way this can be done] whose name appear
several times after they have to exit Excel and go back in).
7. remind users to always acknowledge any error message popping up, i.e. not
leave it there on their sheet and go do something else.
8. remind users not to make copies of the sheet without changing its name,
but only its location, and then save their changes to that new copy of theirs.
IV) QUESTIONS:
1. Among these 8 measures listed, which do you believe actually works and
makes sense as having an impact?
2. Is there some type of "capacity limitation" inherent to Excel (offical or
not) to be aware of when using Excel shared workbooks? Is a 3MB file size an
issue as it grows to 4 to 10MB with its history? Are 10 to 15 users an issue,
or should 100 be working fine too? Is the 150 x 15 number of changes being
made any day to the file an issue or should it not be? etc.
3. Do you have other suggestions or tips on where to look to help resolve
this issue?
Thanks!
Jean-Luc