Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limited Access to Trial Workbooks (Office 2000)

    Can anyone out there tell me if there is a way to limit the access to open an Excel workbook?
    Companies/individuals have asked me for copies of a workbook I have developed. I expect that this program may have commercial opportunities in the future and I wish to protect my intellectual property, but I need feedback to ensure the workbook meets commercial requirements. I have password protected the workbook, but I would like to provide say 5 hours of access. Can this be done?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Yes and no.

    First, there is password protection on the workbook, and additionally you can hide and password protect the VBA modules. However, these are really not very robust. There are Excel password cracker programs out there that will crack the passwords in a few minutes to a few hours. In addition, if your code has any bugs in it that result in an error that is not handeled by an On Error statement, and the user clicks on Debug, he will be thrown into the VBA Editor with your code visible, even if you have password protected it (at least through XL 97, I don't know about 2000 or 2002).

    There is no automatic way to time limit the use of a Work book. However, you can put in your own code to do something like this, but it is pretty complex to implement. The easiest thing to do is limit the use to a fixed number of days after the first use.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Legare, thanks for your response, I note your responses to other queries and value your knowledge of Excel. I guess I will just have to trust my fellow man!!

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Legare, reading your reply again, I note that you indicated the best way was to limit the number of days the program could be used. Did you mean physically retrieve the program or is there a way for it to be programmed to restrict access after a period of days?
    Sorry, I misread or misunderstood your original message.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Not the best way, the easiest. Not the best, because it is fairly easy to defeat.

    What I would do would be to store the date in the Registry the first time the Workbook is loaded on that machine. Then each time it is opened, check to see if the date is past that date plus x days. If it is, then exit. This could be done in the workbook open event routine. Again, fairly easy to defeat.
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Thanks, I'll give it a try.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Legare,

    You knew this was coming! Would you have any tips, pointers, ideas, other references for those of us who are wondering how to write to/read from the registry?

    Thanks,

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    What I know I learned from a book on the Win32 API. Unfortunately, I have recently moved back to the USA after a few years living in Europe, and many of my books are in storage until the house we are building is completed. I don't remember the exact name of the book or the Author's name.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Michael,

    You can read and and write to the registry with th euae of API calls, as VBA provides the following functions :

    SaveSetting,
    GetSetting,
    GetAllSettings, and
    DeleteSetting.

    To write an entry for a given application (Workbook) you use SaveSetting : for example<pre> SaveSetting AppName:="Test", Section:="General", key:="Starts", Setting:=x</pre>

    could be used store the number of times the aaplication called Test was started, where x is the number. To retrieve that piece of information you would use something like :<pre> NoStarts = Val(GetSetting(AppName:="Test", Section:="General", key:="Starts"))</pre>

    You coulkd have as many sections as you want per application and as many Keys within those sections. The only possible drawback with this method, is that you can store your values in :<pre> HKEY_CURRENT_USERSoftwareVB and VBA Program Settings</pre>

    Do check the Helpfile on those functions and view your experiments with Regedit at the above Key. Compared to the API approach it is much simpler, but if you need to deal with other keys in the registry, you will need to resort to the appropriate API function calls.

    Andrew C

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    To protect the code, you might want to look at <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrbuildingcomaddinsforofficeapplications.asp>CO M add-ins</A>. But it's available for Office 2000- so make sure your client has 2000 as well.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Thanks Andrew...that goes a long way towards getting me started in the right direction. Thanks especially for the example.

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    I have had to implement a time bomb sort of routine for one project I did. It had to work on xl97 and xl2000 so I wrote my own. I only wanted the workbook to be accessible until a certain future date. I made the dates configurable by storing them as named variables (which I hid) and tested the current date against this future date on workbook open. I also tracked the date of last open since I didn't want someone to simply change the date on their computer to extend the time they can use the workbook. This isn't a bullet proof system but good enough for Excel since it's not secure anyway.

    Someone else said that xl97 still shows the user the VBA code on an error even if the workbook is protected (I didn't know that). I do know however, that this does not happen in xl2000. I'd still protect your code and at least apply one of the free digital signitures (selfcert.exe comes with Windows 2000 and maybe earlier versions I don't know). At least that way the user will know if anyone other than you messed with your code. Also write your name and some copyright notice in each module as wel as adding a splash screen on workbook open with some warning text.

    Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  13. #13
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Deb, can you tell me what did your "Timebomb"do? Did it activate a Macro that made the workbook malfunction, clear all data, or someway prevented it from being opened again? This sounds like a solution that would suit my particular needs.
    Many thanks

  14. #14
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    I thought you might ask <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I stripped out all the proprietary code from the workbook I did that used the time bomb and attached it here as 'sampletimebomb.xls'. It has three sheets, Sheet1 shows a flow chart with the logic, Sheet2 is empty, and hidden sheet EXPIRE which contains the fields that configure the time bomb. When the bomb expires, it displays a message and closes the workbook. This check is done on workbook open.

    The password for the code is 'woody'. If the workbook is ever opened w/o macros enabled, this hidden sheet is still inaccessible and the code is still hidden so the user can't remove the time bomb and then later re-open the workbook with macros enabled.

    Tell me if this works for you. It's not fool proof but it's good enough for most cases.

    Deb <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Attached Files Attached Files

  15. #15
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limited Access to Trial Workbooks (Office 2000)

    Yes easy to defeat by just editing the registry, that's why I chose to use named vars inside of the worbkook with the time bomb (see my attached .xls file in response to the originator). I then hid the names and did my checks on WB open. I'm sure it's not fool proof either but more so than using text in the registry. Plus it works with xl97 and xl2000.

    Deb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •