Many users adding to one workbook?

[Ed]

We have about 35+ people who need to enter individual information in a
master spreadsheet. Admin wants the individuals to see only their own
info, but Admin needs to be able to see everything. After presenting
this on just the Excel.Programming NG and then going back with more
ideas, it looks like we might want to try wrapping an Excel workbook
inside a VB6 program.

I'm thinking that when the user opens the VB program, it will ask for
his/her name and a password, reads that user's info from the Master
into a new Excel workbook that opens on the user's computer across the
network, lets the user make changes, then on close writes the changed
info back into the Master. The user never touches the Master and sees
no one else's info. The Master Excel workbook, though, is available
to Admin.

Some questions:

(1) I've never done anything across a network before. How difficult
will it be in a Windows XP environment to put a new workbook with this
info on the user's computer (vice just having it open on the server)?

(2) Is it difficult to have VB detect when the Master wb is already
open and tell the user to come back later?

Thanks.
Ed

 

[Ralph]

We have about 35+ people who need to enter individual information in a
master spreadsheet. Admin wants the individuals to see only their own
info, but Admin needs to be able to see everything. After presenting
this on just the Excel.Programming NG and then going back with more
ideas, it looks like we might want to try wrapping an Excel workbook
inside a VB6 program.

I'm thinking that when the user opens the VB program, it will ask for
his/her name and a password, reads that user's info from the Master
into a new Excel workbook that opens on the user's computer across the
network, lets the user make changes, then on close writes the changed
info back into the Master. The user never touches the Master and sees
no one else's info. The Master Excel workbook, though, is available
to Admin.

Some questions:

(1) I've never done anything across a network before. How difficult
will it be in a Windows XP environment to put a new workbook with this
info on the user's computer (vice just having it open on the server)?

(2) Is it difficult to have VB detect when the Master wb is already
open and tell the user to come back later?

Thanks.
Ed

[Note: pure opinion follows.]

A 'gatekeeper"?

Just like everything else in programming, there is a zillion ways to skin
this poor cat.

The easiest way is to create an ActiveX Exe service and and use COM++/MTS
from a local Client to communicate with it.

Answer to (1): No problem. Let the local client manage it.
Answer to (2): No. As it won't even be an issue.

More information is needed.

-ralph

 

[Ed]

We have about 35+ people who need to enter individual information in a
master spreadsheet. Admin wants the individuals to see only their own
info, but Admin needs to be able to see everything. After presenting
this on just the Excel.Programming NG and then going back with more
ideas, it looks like we might want to try wrapping an Excel workbook
inside a VB6 program.

I'm thinking that when the user opens the VB program, it will ask for
his/her name and a password, reads that user's info from the Master
into a new Excel workbook that opens on the user's computer across the
network, lets the user make changes, then on close writes the changed
info back into the Master. The user never touches the Master and sees
no one else's info. The Master Excel workbook, though, is available
to Admin.

Some questions:

(1) I've never done anything across a network before. How difficult
will it be in a Windows XP environment to put a new workbook with this
info on the user's computer (vice just having it open on the server)?

(2) Is it difficult to have VB detect when the Master wb is already
open and tell the user to come back later?

Thanks.
Ed

[Note: pure opinion follows.]

A 'gatekeeper"?

Just like everything else in programming, there is a zillion ways to skin
this poor cat.

The easiest way is to create an ActiveX Exe service and and use COM++/MTS
from a local Client to communicate with it.

Answer to (1): No problem. Let the local client manage it.
Answer to (2): No. As it won't even be an issue.

More information is needed.

-ralph-

Ralph -

My programming is limited to Word and Excel macros and a very little
bit of VB6, and I have no clue what you said! I'm also not a
"programmer" by job title, so I don't even have access to those
programs. I'm sure it's easy enough and a good way, and I appreciate
the pointer, but it sounds quite a bit beyond me at the moment.

Ed

 

[Mark Yudkin]

Excel is not designed to support this sort of application, and coding VB6
around it won't resolve this. From what you say about not benig a
programmer, you'd probably be better off with a non-programming solution.

1) Access is the simplest tool and the cheapest (yes I did see your post).

2) The Office Web Components (comes with Office) will give you a better
shared front-end.

3) If you have W2K3, download the latest Sharepoint services and use that
for a web-based solution. Otherwise you may have to try interfacing with SQL
Server 2005 / Express via ADO.

 

[Ed]

Excel is not designed to support this sort of application, and coding VB6
around it won't resolve this. From what you say about not benig a
programmer, you'd probably be better off with a non-programming solution.

Thanks for the input, Mark. Actually, I think I'd be better off
shoving this project onto someone else's desk! It's cheaper for them
and an ego boost for me to have me do it, but I think I see endless
nightmares down the road!

Ed

 

[Steve Gerrard]

We have about 35+ people who need to enter individual information in a
master spreadsheet. Admin wants the individuals to see only their own
info, but Admin needs to be able to see everything. After presenting
this on just the Excel.Programming NG and then going back with more
ideas, it looks like we might want to try wrapping an Excel workbook
inside a VB6 program.

I'm thinking that when the user opens the VB program, it will ask for
his/her name and a password, reads that user's info from the Master
into a new Excel workbook that opens on the user's computer across the
network, lets the user make changes, then on close writes the changed
info back into the Master. The user never touches the Master and sees
no one else's info. The Master Excel workbook, though, is available
to Admin.

Some questions:

(1) I've never done anything across a network before. How difficult
will it be in a Windows XP environment to put a new workbook with this
info on the user's computer (vice just having it open on the server)?

(2) Is it difficult to have VB detect when the Master wb is already
open and tell the user to come back later?

You have already had lots of suggestions that you move to a database, and you
have even apparently signed off on the project. But, you may have it given back
to you, and your last question changes the picture.

It seems to me you are implying that even though there are 35+ users, you expect
only one of them to open the file at a time. Is that right? If so, then doing
this in Excel may be possible. Is the data that a given user can see on a
particular worksheet? Excel worksheets have a three-state Visible property:
Visible, Hidden, and VeryHidden. As the help file says about Visible:

"For a chart or worksheet, this property can be set to xlVeryHidden. This hides
the object so that the only way for you to make it visible again is by setting
this property to True (the user cannot make the object visible)."

So maybe you could set up the master spreadsheet so that it checked the users
name, then set all the off-limits sheets to xlVeryHidden. If you also protect
the code, then users would not be able to view the hidden data.

Also, once one user has the file open, others would just get the standard "read
only or wait" message from Excel. I don't think the "copy part of the file
locally, update back to the master" concept would work out very well myself.

 

[Ed]

Hi, Steve. Thanks for the input.

It seems to me you are implying that even though there are 35+ users, you expect
only one of them to open the file at a time. Is that right?

Not so much that I expect them to opn it one at a time. In fact, I
expect several to try to access it at either the beginning or the end
of the day.

I don't think the "copy part of the file
locally, update back to the master" concept would work out very well myself.

The concept here was that the actual workbook would then be open only
long enough to generate a copy for the user's use, or to write the
changes back. That would probably work as no two people are likely to
try to access the file at the exact same minute - not as likely,
anyway, as one user having it open for 10-15 minutes and someone else
wanting to get into it.

Excel worksheets have a three-state Visible property:
Visible, Hidden, and VeryHidden.
So maybe you could set up the master spreadsheet so that it checked the users
name, then set all the off-limits sheets to xlVeryHidden. If you also protect
the code, then users would not be able to view the hidden data.

That was an early idea. But we have a few that on any given day might
see hidden data and a forbidden project as a challenge, and have the
Excel skills to either break protection or really screw something up!
That's when the concept of giving them a copy to play with came up.

I'm not a programmer - I taught myself (with LOTS of help from the
NG!!) how to do macros just to make my job easier. So now I get calls
for help from various others, and sometimes I don't know how deep the
water is or how big the sharks are before I get into the project! I
thought I would ask first this time becuase - as I'm sure you know -
once you touch it, it's your baby!

Ed

 

[Peter T]

Unless users particularly need the master Workbook for use of formula
results elsewhere in the wb, besides their 'own' sheet, as others have said
Excel is probably not the right application). Or even if they do, maybe each
user could have their own dedicated workbook with two way links to the
master (though that might pose a different set of problems).

If the set-up is merely for a form type data input (and perhaps output
display) there are many other ways, again as others have suggested. Yet
another idea you might look into is Sharepoint, possibly in conjunction with
Groove. This/these appear to work particularly well in Office 97 from a demo
I saw recently, but don't ask me to expand!

Regards,
Peter T

 

[Stefan Berglund]

I'm not a programmer - I taught myself (with LOTS of help from the
NG!!) how to do macros just to make my job easier. So now I get calls
for help from various others, and sometimes I don't know how deep the
water is or how big the sharks are before I get into the project! I
thought I would ask first this time becuase - as I'm sure you know -
once you touch it, it's your baby!

Yes, in that respect software development is a lot like sex. Make one
mistake and you might wind up supporting it for many years.