Multi-user macro on network drive?

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

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 wrote:
>
> 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
>
> --
> xangelusx
> ------------------------------------------------------------------------
> xangelusx's Profile: http://www.excelforum.com/member.php...o&userid=25504
> View this thread: http://www.excelforum.com/showthread...hreadid=389481

--

Dave Peterson

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

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

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.


xangelusx wrote:
>
> Thanks for the reply, Dave. Marking it as read-only, even via the
> explorer window, will still prompt/alert the user when they open the
> work book up. And, I agree that it would make more sense to only open
> the work book when they need to use the macro, but these are after all
> "users" and we know that users don't like to do things like that. Even
> if it saves them time in the long run they still think even one extra
> click up front is one click too many.
>
> For the moment, I have just create separate copies of the worksheet on
> the network drive for each user. Then when I need to make a change to
> the macro I just edit a "master" copy and copy it to all of the users
> folders so that the update will be available the next time they open
> Excel. It's a decent work around, but I'd still like to find away to
> do it with a single read-only (or password protected) file that doesn't
> 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 and
> redistributing...
>
> --
> xangelusx
> ------------------------------------------------------------------------
> xangelusx's Profile: http://www.excelforum.com/member.php...o&userid=25504
> View this thread: http://www.excelforum.com/showthread...hreadid=389481

--

Dave Peterson