How do I stop an Excel file from being copied

J

John Driscoll

I want to allow a company to use a spreadsheet I have developed but do not
want them to copy or distribute it. Can I protect the file please? Please
return answers to: [email protected]
 
S

samer.kurdi

I have the same URGENT need. I was thinking about this for a while and
I have some questions:

1- Is it possible to REQUIRE enabled macros for a worksheet to open?
2- If it is, then perhaps opening the sheet will prompt checking for
the existence of a file somewhere on the network
3- If the file is not found, then the macro will force excel to quit

I realize that even if this is doable, it still will not be too
difficult to work around, esp as I want to prevent the DATA inside the
sheet from being copied, and it might be possible to access this data
outside the Excel environment. Still, this might be sufficient for my
purposes as the people who are using the sheet are not too technically
sophisticated.

Your input/thoughts will be greatly appreciated.
 
D

Dave Peterson

#1. You could use another workbook that contains a macro that opens the real
workbook. If macros are disabled for that helper workbook, then the real
workbook won't open. If macros are enabled, then macros will be enabled for the
real workbook.

#2. dim teststr as string
teststr = ""
on error resume next
teststr = dir("\\server\share\folder\filename.txt")
on error goto 0

if teststr = "" then
thisworkbook.close savechanges:=false
end if

'continue with code

#3. See #2.

But all this depends on the curiousity/honesty of the users. It isn't too
difficult to bypass the project's protection. Then the code could be modified
to avoid all this stuff.

If you really have intellectual property that you want to protect, I don't think
excel and VBA are the way to go.
 
H

Harlan Grove

Dave Peterson wrote...
#1. You could use another workbook that contains a macro that opens the real
workbook. If macros are disabled for that helper workbook, then the real
workbook won't open. If macros are enabled, then macros will be enabled for the
real workbook.
Click to expand...

Another alternative is using a do-nothing udf in most formulas. For
example,

Function nada() : End Function

This will always return a VBA variant Empty value which Excel treats
the same as the value of blank cells, so add it to formulas that should
return numbers and concatenate it at the end of formulas that should
return text. If the user opens the workbook with macros disabled, all
formulas containing this udf will evaluate as errors. Add a message
formula like

=IF(ISERROR(nada()),"Nice try. You have to enable macros for this
workbook to calculate correctly. Close it an reopen it with macros
enabled.","")
But all this depends on the curiousity/honesty of the users. It isn't too
difficult to bypass the project's protection. Then the code could be modified
to avoid all this stuff.
Click to expand...
....

Which is why the udf approach can be safer. While it may be easy to use
Edit > Replace to replace +nada() and &nada() with nothing, it gets
harder to deal with all the variations in which nada() could be used.
Add nada() calls to several defined names too. Still possible to
disable, but it can be made to take a LOT of effort to remove it from
all formulas and name definitions.
 
S

samer.kurdi

Thanks guys... these are just great ideas!. I think I will end up doing
a combination of these approaches: helper workbook that opens the real
workbook + checking for a file on network + a do-nothing function(
BRILLIANT idea, btw)
 
Top