Prevent building shortcuts to an Excel file

[Jerbinator]

This doesn't exactly have to do with Excel, but it's an Excel file I'm working on and I hope you all can help me anyway.

My bosses want me to put an Excel database I've built into a public folder that is accessible by anyone in the office network. But they don't want me to allow people to make shortcuts to this particular file. The reason is because the shortcut first gets created in the network folder, and then when the user drags it to the desktop it makes a copy, leaving the original shortcut in the main folder. After a week, everyone in the office has built shortcuts, sometimes two or three, and suddenly there are fifty million shortcuts clogging this folder for no good reason.

My bosses also don't want me to allow copies of the file to be made, because then we would have multiple copies of this gigantic database flooding our network files and crashing our system. Now, there's probably no way to prevent people from saving the file under a new name and thereby making a copy of it, but I'm hoping there IS a way to prevent people from saving it to their own hard drives. Is there?

Again, this doesn't exactly have to do with Excel, but I would appreciate any advice you all have to give. Thank you!

[Special-K]

Sounds like Group Poilcy related but that would be handled by your network administrators.
I doubt you can stop people saving anything to their hard drive the way you want.
Preventing files saved to hard drive would no doubt prevent temporary files being saved as well.

[Aardigspook]

Ok, I had to root around in some files written by an ex-colleague who was a bit of a VBA guru. This code prevents users saving the file unless they have the password:

Please Login or Register  to view this content.

However, you probably want to allow users to save the file, but disable SaveAs - so I had a bit of a play with the code and came up with this, which seems to work. (Health warning - I'm not on a network at the moment, so haven't been able to test it on network drives / Sharepoint / etc.)

Please Login or Register  to view this content.

As with any macro, if a user disables macros on opening the file, this won't work.


Instructions for installing the code:
- Press Alt-F11 to open the VBA editor
- On the left-hand side near the top, right-click 'ThisWorkbook' and select 'View Code' (or just double click 'ThisWorkbook')
- Paste in the code above on the right-hand side
- Save the file as a Macro-enabled workbook (.xlsm)


Hope that's of some use to you.