Prevent Copying of Excel Workbooks in 2000 and 2003.

[dazzag82]

Is there a way of preveting a user to copy an excel workbook to a different
folder location?

We have excel spreadsheets saved in our company network and the locations
are the one safe source of information. These workbooks contain data which is
linked to other workbooks so it is importnat the files are not renamed nor
moved to a different folder. No matter how much I communicate this, some
people insist on doing it.

I know there is VBA code which can disable the use of 'Save-As' so that the
files cannot be saved in a different location. However this does not prevent
someone from copying (CTRL +C) and pasting (CTRL +V) the file from one folder
to another. I need to keep the Save As function as I need to control the read
only and modify access passwords etc from the general options.

Apparently it is possible to write VBA code which contains the true location
of the file, and when the workbook is opened, it ckecks that if it was opened
from the specified location, and if not an error message or userform appears
informing the user that the workbook cannot be accessed and therefore
information cannot be populated. I have also been told that rather than a pop
up error message or userform, the worksheets which need accessed are hidden
and another worksheet will show instead with the error message.

Can anyone help with this. Thanks.

Darren

 

[Chip Pearson]

One way, though not fool proof, is to save the computer name and file
name the first time the workbook is opened and then compare those
values to the current values when the workbook is opened later. If the
values don't match, indicating that the file has been moved or
renamed, the workbook would then be closed.

For example, in the ThisWorkbook code module, use something like

Private Sub Workbook_Open()

Dim S As String
On Error Resume Next
S = ThisWorkbook.Names("CompName").RefersTo
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="CompName", _
RefersTo:=Environ("ComputerName"), Visible:=False
Else
S = Mid(S, 3, Len(S) - 3)
If StrComp(S, Environ("ComputerName"), vbTextCompare) <> 0 Then
MsgBox "You cannot use this workbook on this computer."
ThisWorkbook.Close savechanges:=False
End If
End If
S = ThisWorkbook.Names("InitFullName").RefersTo
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="InitFullName", _
RefersTo:=ThisWorkbook.FullName, Visible:=False
Else
S = Mid(S, 3, Len(S) - 3)
If StrComp(S, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
MsgBox "You cannot use this workbook from this location."
ThisWorkbook.Close savechanges:=False
End If
End If

End Sub

When you first distribute the workbook to a legitimate user, you'll
have to delete the defined names "CompName" and "InitFullName".

ThisWorkbook.Names("CompName").Delete
ThisWorkbook.Names("InitFullName").Delete

Then, when the user first opens the workbook, these names will be
created with the user's computer name and the full file name of the
workbook. If the workbook is copied to another computer, or another
folder on the same computer, or is renamed, the stored computer name
and/or stored full file name will not match the current values and the
workbook will close. Of course, the user can get around this by
disabling macros, but there ways of ensure that macros are enabled.
See http://www.cpearson.com/Excel/EnableMacros.aspx and
http://www.cpearson.com/Excel/EnableMacros2.aspx.


None of this is completely secure. Any experienced VBA developer can
rather easily circumvent this sort of protection. However, it might be
good enough to keep things in order in your circumstances.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Libby]

I don't know of a way to prevent copying. You'd have to get the folder where
workbook is stored set up so that people can't delete the file and then set
the macos so that the workbook is disabled if the path doesn't match where is
should be.