Tags: allusers, available, copy, drive, eithernetwork, excel, file, local, microsoft, msdn, network, own, protect, shared, software, users

Protect file on a shared Network drive

On Microsoft » Microsoft Excel

4,868 words with 2 Comments; publish: Tue, 27 May 2008 00:37:00 GMT; (30678.13, « »)

I place a file on a network drive which is available to all

users.

I tell the users to copy the file to their own drive (either

network or local) and open the copy there. If they do so,

then code in the ThisWorkbook module will complete the

process.

By placing the file centrally on the common network

drive, I figure that updates will be easier to manage,

but, two questions please:

1. Can I detect the file being opened from its's

original location, and stop code executing,

prompting user to first copy the file.

2. Can I stop user from modifying the file's

properties (eg name) because that might

cause me a few problems!

I'm trying to introduce new users to a few little macros.

Longer term, when they're more comfortable, it will be

far better distributed as an addin, but until then......?

Distributed under Excel 2003 and XP Pro.

Regards.

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system (http://www.grisoft.com).

Version: 6.0.719 / Virus Database: 475 - Release Date: 12/07/2004

All Comments

Leave a comment...

  • 2 Comments
    • Hi Stuart,

      Stuart wrote:

      > I place a file on a network drive which is available to all

      > users.

      > I tell the users to copy the file to their own drive (either

      > network or local) and open the copy there. If they do so,

      > then code in the ThisWorkbook module will complete the

      > process.

      Instead of telling users to copy the file to their local drive, you could

      force the issue with ThisWorkbook.SaveCopyAs.

      > By placing the file centrally on the common network

      > drive, I figure that updates will be easier to manage,

      > but, two questions please:

      > 1. Can I detect the file being opened from its's

      > original location, and stop code executing,

      > prompting user to first copy the file.

      You could check ThisWorkbook.Path and see if it matches the network

      location. If so, display a message and stop executing.

      I would also recommend making the workbook read-only. In addition, if you

      have the ability to set NTFS permissions, you should set them in such a way

      that the workbook cannot be modified by any of the users. Because if the

      user disables macros, they will be able to do whatever they want with the

      workbook (including modifying and resaving).

      > 2. Can I stop user from modifying the file's

      > properties (eg name) because that might

      > cause me a few problems!

      No. You can protect aspects of the workbook (such as worksheet names, range

      values, etc), but you cannot stop them from renaming the workbook. You

      should try to make your code as generic as possible and avoid referring to

      workbook and sheet names.

      Regards,

      Jake Marx

      MS MVP - Excel

      www.longhead.com

      [please keep replies in the newsgroup - email address unmonitored]

      #1; Tue, 27 May 2008 00:39:00 GMT
    • Thanks for the ideas.

      Regards.

      "Jake Marx" <msnews.excel.itags.org.longhead.com> wrote in message

      news:uDPH88qaEHA.3664.excel.itags.org.TK2MSFTNGP12.phx.gbl...

      > Hi Stuart,

      > Stuart wrote:

      > Instead of telling users to copy the file to their local drive, you could

      > force the issue with ThisWorkbook.SaveCopyAs.

      >

      > You could check ThisWorkbook.Path and see if it matches the network

      > location. If so, display a message and stop executing.

      > I would also recommend making the workbook read-only. In addition, if you

      > have the ability to set NTFS permissions, you should set them in such a

      way

      > that the workbook cannot be modified by any of the users. Because if the

      > user disables macros, they will be able to do whatever they want with the

      > workbook (including modifying and resaving).

      >

      > No. You can protect aspects of the workbook (such as worksheet names,

      range

      > values, etc), but you cannot stop them from renaming the workbook. You

      > should try to make your code as generic as possible and avoid referring to

      > workbook and sheet names.

      > --

      > Regards,

      > Jake Marx

      > MS MVP - Excel

      > www.longhead.com

      > [please keep replies in the newsgroup - email address unmonitored]

      >

      Outgoing mail is certified Virus Free.

      Checked by AVG anti-virus system (http://www.grisoft.com).

      Version: 6.0.719 / Virus Database: 475 - Release Date: 12/07/2004

      #2; Tue, 27 May 2008 00:40:00 GMT