Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 dont 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 doesnt 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 companys 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 doesnt 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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. -- Regards, Tom Ogilvy "Berj" wrote: 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 dont 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 doesnt 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 companys 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 doesnt 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 doesnt open, it doesnt. Thats that. They dont even know such a newsgroup exists in which I am asking questions now! Now, about the serial number: I wouldnt 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. Thats 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 wont 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. Youll see that the file will open just fine. Now change 9984 to 9985 lets say, save and re-open the file. It wont 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
Berj wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 -- Regards, Tom Ogilvy "semiopen" wrote: Berj wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file security
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 Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "semiopen" wrote: Berj wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File security Excel encryption | Excel Discussion (Misc queries) | |||
Excel 2007 .xlsm file. Macro Security options not available. | Excel Discussion (Misc queries) | |||
Security warning when saving downloaded file from Excel | Excel Discussion (Misc queries) | |||
Help! Security when using a VBA macro to access an Excel File | Excel Programming | |||
Security discussion - how to effectively distribute an Excel file | Excel Programming |