Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Copying of Excel Workbooks in 2000 and 2003.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Copying of Excel Workbooks in 2000 and 2003.
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] On Thu, 14 Jan 2010 07:17:01 -0800, dazzag82 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent Copying of Excel Workbooks in 2000 and 2003.
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. "dazzag82" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PROBLESM IN COPYING BETWEEN WORKBOOKS AFTER EXCEL 2003 UPGRADE | Excel Discussion (Misc queries) | |||
problem copying visible cells from 2000 to 2003 | Excel Discussion (Misc queries) | |||
Workbooks.Open 2000 vs 2003 | Excel Programming | |||
IN EXCEL 2000 HOW DO YOU PREVENT TEXT FROM GOING INTO THE NEXT COL | Excel Discussion (Misc queries) | |||
Prevent Save in Excel 2000 | Excel Programming |