Excel file security

[Berj]

Dear Anyone,

I have an Excel file which I have prepared to sell to a company. But that
company may copy and give it to another company to use. Naturally, I don’t
want that to happen. If the second company wants it, they should buy it from
me. Copyrighting is not a solution in my case. I live in a place where I can
reach my customers personally.
As I do not know any programming in Excel, I asked somebody in these forums
on the net to prepare a code in Excel which, before opening the Excel file,
reads the hard disk serial number, compares it to a fixed serial in the code
in order to open the file. The fixed serial number of the hard disk and the
code will be typed by me when the file is first copied to a new hard disk.
From then on, it works fine. But when it is copied to a new hard disk
(someone steals it), it doesn’t open because the hard disk serial will be
different of course.
Well, someone answered, and after a few adjustments, the code was made. Good.

This is the code:
========================================
Public WithEvents App As Application
'---------------------------------------------------------------
Function DiskVolumeId(Drive As String) As String
'---------------------------------------------------------------
Dim sTemp As String
Dim iPos As Long
iPos = InStr(1, Drive, ":")
Drive = IIf(iPos > 0, Left(Drive, iPos), Drive & ":")
sTemp = Hex(CreateObject("Scripting.FileSystemObject") _
.Drives.Item(CStr(Drive)).SerialNumber)
DiskVolumeId = Left(sTemp, 4) & "-" & Right(sTemp, 4)
End Function

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If DiskVolumeId("C:") <> "E06F-9984" Then
Wb.Close savechanges:=False
End If
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

Now, as I think of it, another possibility occurred to me. What if the
company’s computer was changed, or a problem happened and the hard disk has
to be formatted. In both these cases the hard disk serial number would
change, and the Excel file (even if it was backed up and restored to the new
hard disk) would not be opened. So, the company would lose the information
entered in the Excel file.

Now, I am in need of another addition to the already existing code which
works in the following way:

As the Excel file is opened, it checks and compares hard disk serial with
existing fixed serial in the code. If they match, the file is opened. If not,
instead of closing the file, it asks the question, “Serial number:â€. Here the
serial number of the new hard disk has to be entered. (Of course, an operator
would never guess that it is the hard disk serial number). From here on, two
possibilities exist:
a) If the serial number entered is exactly the same as the new hard disk
serial number, the Excel file opens, and the new serial is recorded in the
code such that when the file is opened again on the same hard disk, it never
asks for a serial number and opens the file without any problems.
b) If the serial number entered is not the serial number of the new hard
disk, it answers “wrong serial number†and doesn’t open the file without
giving a second chance.

I think maybe this code should work for any Microsoft Office document too.
I hope somebody understood me well and will reply soon.

Regards,
Berj

 

[Tom Ogilvy]

Who would you tell that it is the hard disk serial number that they need to
enter if they changed computer and were prompted for the serial number.

The company that bought the software? The same company that you worry will
give it or sell it to some other company. Then why wouldn't they tell them
to just enter their hard disk serial number?

In your request, you didn't say that you would want the code to also change
the hardcoded serial number so the user won't be prompted each time.

Why wouldn't I just disable macros, open your workbook and copy all the
sheets from your workbook into another workbook that doesn't have your code.
then I have an unprotected copy of your product.

 

[Berj]

Hi Mr. Tom,
First, thank you for answering.
I think you got it all wrong. Let me explain in another way.

Let me tell you that the place I live nobody thinks of Macros in Excel. If
the file doesn’t open, it doesn’t. That’s that. They don’t even know such a
newsgroup exists in which I am asking questions now!

Now, about the serial number: I wouldn’t tell anyone anything about a serial
number. Imagine that I copy the file on a computer, do the necessary “serial
number†entering of their hard disk. That serial number will be fixed in the
file. That’s that. Every time they try to open the file, it automatically
checks and compares both serial numbers (hard disk and the fixed one in the
file). If they match, the file opens and no one realizes that a comparison
was done before opening the file. And from then on the file opens normally on
that computer without asking anything. So, nobody will ever think of any
serial number.
But if they give the file to another company to use, it won’t open because
the new hard disk serial is not the same as I have entered in the first
computer. So, it asks for a serial number. If it does, nobody will ever think
that it is the hard disk serial that is being asked. They will simply think a
registration serial of some sort. They will call the first company and ask
about it. These people again will know nothing about any serial number.
That is when they will call me and ask me to activate it. I will go of
course, enter their hard disk serial to activate it, cash in for my file and
… bye.
Another thing, I am not trying to change the hard disk serial number to
anything else. No. It will remain the same.
Try the code I have sent for a whole workbook, replacing the E06F-9984 with
the serial number of your hard disk. You’ll see that the file will open just
fine. Now change 9984 to 9985 let’s say, save and re-open the file. It won’t
open.
In such a case, I want your program to ask for a serial number.
I hope it is better understood now.

By the way, how do you disable Macros for a file which cannot be opened?


Regards,
Berj

 

[Berj]

Dear Mr. Tom

This is my second message.
I just realized what you meant by saying "disabling macros".
I am going to put the file in "low security", so in this way, it is not
going to ask for any disabling macros. it is just going to open, or not open.
As I said in the previous message: the place I live nobody knows of these
things.

Best regards,
Berj

 

[Berj]

Dear Mr. Tom,

This is my second message to you.
I just realized what you meant by "disabling macros".
I am going to put the file in "low security", so it will just open the file
or not open the file.
As I told you before, the place I live, nobody knows of these things.

Best Regards,
Berj

 

[semiopen]

Dear Mr. Tom,

This is my second message to you.
I just realized what you meant by "disabling macros".
I am going to put the file in "low security", so it will just open the file
or not open the file.
As I told you before, the place I live, nobody knows of these things.

Best Regards,
Berj

But - security settings don't reside in the file - they reside in the
application and are not low by default.

-semiopen

 

[Tom Ogilvy]

for the Original Poster:

And I did try your code and it worked well until I disabled macros. then it
opened and I was free to do whatever I wanted.

Also, many places just set the security to High. In that case, not only
won't the realize you have such a macro in your workbook, it won't even run,
since macros are silently disabled for uncertified macros.

so they will freely share with their friends and never even know that the
workbook shouldn't open.

And as semiopen said, Security is set at the application level and stored
in the registry - not in the workbook. Code in a workbook can not change the
security setting when it is opening.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If DiskVolumeId("C:") <> "E06F-9984" Then
msgbox "a serious error has occured. Call Berj at 555-5555 for
help"
Wb.Close savechanges:=False
End If
End Sub

 

[semiopen]

I think that the original poster had the idea of setting security to
low when he is physicaly sitting at their computer "installing" the
file (reading that disk ID number). This would work for a client who is
*extremely* naive - but would be quite unethical (especially when done
to inexperienced users who would need the security umbrella the most).
It is possible to be a little more devious and a little more ethical at
the same time:
1) Get the client to set their security setting to medium - and tell
them to enable the macros whenever they open the file

2) Add some macros that are *essential* to the functioning of the
workbook - and not just essential to the non-copy ploy. This could be
achieved by doing nothing in a clever way - replacing some crucial
worksheet functions by dummy user-defined functions which do nothing
more than pass their parameters to the appropriate.
Application.WorksheetFunction. You can even give these functions
impressive sounding names if you want to throw a little bit of
marketing into the mix.

3) Password-protect the code so they can't tell what the macros are
*really* up to.

I still have some ethical qualms about that - but it would be a heck of
a lot nicer than turning off security altogether on the unsuspecting.

-semiopen