Multi-user macro on network drive?

[xangelusx]

I have a small set of users (currently 4) that use a macro that I
maintain to format downloaded data. The macro changes often enough
that it is inconvenient to go to each PC and update the macro in each
users personal.xls file.

Instead, I have placed a new document on a shared network folder that
they all have access to and have created shortcuts to the document in
each users XLStart folder. My hope was that I could then just update
the macro in the file on the network drive and all users would have the
updated version the next time they loaded Excel. (The document is hidden
in Excel)

However, I have found that if one of the users has Excel open, and
another user tries to open Excel on their computer, they get a message
saying that the shared file is in use and would they like to open the
file as read only.

I then tried to save the file using the Read-Only Recommended flag
(Save As -> Tools -> General Options), but that still throws up a
message about the file being read-only whenever anyone opens Excel.

So, is there any way to force an Excel document to open as read-only
without prompting?

Also, of lessor concern, is there a way to protect it from modification
without prompting for a password when Excel opens?

Thanks,
Chris Bloom

 

[Dave Peterson]

Personally, I wouldn't put shortcuts to a file that isn't always used in
anyone's XLStart folder.

But what I would do is put a copy of the workbook on that network drive (keep a
nice copy for yourself for updating and historical purposes).

Use windows explorer to mark that workbook as Readonly (excel will respect
that).

Tell the users to just open that workbook when they need the macro to import the
data.

=======
But if you like the way you have it setup, try marking the file readonly in
windows explorer.

Since the file is readonly, it won't be tied up by a user when you want to
update it. You can delete it and replace it with the new workbook (same name,
but with the updates).

I guess I just don't like opening workbooks that I don't alway need. And even
if I import the data once a day or even once an hour, I'd just open the workbook
when I needed it.

 

[xangelusx]

Thanks for the reply, Dave. Marking it as read-only, even via th
explorer window, will still prompt/alert the user when they open th
work book up. And, I agree that it would make more sense to only ope
the work book when they need to use the macro, but these are after al
"users" and we know that users don't like to do things like that. Eve
if it saves them time in the long run they still think even one extr
click up front is one click too many.

For the moment, I have just create separate copies of the worksheet o
the network drive for each user. Then when I need to make a change t
the macro I just edit a "master" copy and copy it to all of the user
folders so that the update will be available the next time they ope
Excel. It's a decent work around, but I'd still like to find away t
do it with a single read-only (or password protected) file that doesn'
prompt the users at start-up.

A user in another Excel forum mentioned saving the macro as an Add-in
but I don't see how that would fix the issue with updating an
redistributing..

 

[Dave Peterson]

I've never been prompted when I use windows explorer to mark a workbook
readonly. Well, maybe the password to open--but not the password to modify.

Excel has always respected that windows setting and knows that the best it can
do is open the file in readonly mode.

I'd try that again.

(I don't see the difference with the .xls vs .xla, either.)

If you have to go with separate installs (I still don't think you need to...):

Jan Karel Pieterse has a nice install program included in the .zip file for his
(with Charles Williams and Matthew Henson) name manager utility.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp/

And if you use names in any of your workbooks, keep that name manager utility.