Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time-limit use of a workbook (2000 on)

    I'm looking for a way to limit the use of a workbook for a customer who has concerns it may 'fall into the wrong hands'.

    The VBA project is password protected so keeping that locked is (hopefully) not a problem. The issue is that it is used to download information from a piece of hardware, and that information itself could be useful for a competitor.

    Ideally, it would be limited to something like seven days use from being issued. I have thought along the lines of loading the issue date into a very-hidden worksheet and comparing it to the current date, but one has to assume someone may twig to this and simply set the date back on their pc. (Perhaps also log the last date used and if the 'current date' is earlier, use that as a block...?)
    Loading something into the registry could be got round by simply running the workbook on a different pc, as would limiting the number of times the workbook is opened.
    Password protection only goes so far - if a competitor gets a hold of the workbook in the first place, the chances are they will have the password as well.

    Any thoughts on this from anyone? Perhaps a password combined with the date...? Ultimately, I would like to handover the workbook to the customer, and allow them run a setup routine to start the 'clock' running, and this routine could be protected by a secure password as it would not be released to an end user.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time-limit use of a workbook (2000 on)

    > Perhaps also log the last date used and if the 'current date' is earlier, use that as a block...?

    That sounds like a good idea.
    When the client initializes the workbook, the current date/time is stored in A1 in the very hidden sheet.
    Each time the workbook is opened subsequently, store the current date/time in the next available cell (A2, A3, ...).
    If the new date/time is earlier than the previously recorded one, there is something fishy (except at the end of daylight saving time).
    You can also use this to track the number of times the workbook is opened, it doesn't matter on which PC.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    This is a shot in the dark, really, and depends on the workbook always being on a machine that has internet access. There are a number of websites that give the current time (for example the World Clock link in the top-right hand corner of the Lounge). I don't know if it's possible to capture the "actual" time from the internet rather than using the system time. You'd obviously have to make sure that the website you use isn't just giving you your computer's system time!

    Just a thought.
    Waggers
    If at first you do succeed, you've probably missed something.

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    Thanks - nice idea, but internet access is most likely not going to be available. The pc is most likely going to be a laptop as the equipment being checked is out on a public highway and, by definition, could be quite remote from any wi-fi hotspot. Interesting thought though if it was possible - you could simply check into a website for 'licence verification' rather than depending on a date...

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    Yes, that would save a lot of effort in trying to capture the time! You have given me another idea though. You could have a seperate file to act as a "key", stored somewhere else on the computer, and check that the "key" file exists whenever your workbook is opened.

    It doesn't necessarily have to be a file made specifically for this purpose - for example, some companies have corporate screensavers or desktop wallpaper so you could check for a particular image file.

    As you said in your original message, the danger is that the competetor coud also get hold of the "key" file - but combining this with the date thing might help. There might even be some way of telling which company the operating system is licenced to, but that's a bit advanced for me!
    Waggers
    If at first you do succeed, you've probably missed something.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Time-limit use of a workbook (2000 on)

    Leif,
    I see a few problems if you are that worried:
    1. A VBA project password can be changed with relatively cheap software. You would really need to make a dll.
    2. A hidden worksheet can be unhidden easily (unless you protect the workbook but that password is even easier to break).
    I'm not entirely clear from your post whether the customer is worried that the information may fall into the wrong hands or that the workbook will. If it's the latter, then you could have a setup routine, separate from the workbook, that creates an encoded registry entry based on the date and machine name. If the entry is not there or is incorrect, the workbook will not run. That way, an employee cannot simply email a copy of his workbook to someone else.
    Just a thought.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    Leif,

    You already have some far superior ideas but as this will be my 600th post and, for once, I can actually contribute something vaguely relevant I offer the following code (placed in ThisWorkbook) that I used to at least slow down someone hacking into one of my workbooks:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Protect password:="insert as required"
    ActiveSheet.Protect password:="insert as required"

    End Sub

    Private Sub Workbook_Open()

    Dim CurrentDate As Date, ExpireDate As Date
    CurrentDate = Date
    ExpireDate = #insert as required#
    If CurrentDate < ExpireDate Then
    ActiveWorkbook.Unprotect password:="insert as required"
    ActiveSheet.Unprotect password:="insert as required"
    Else
    MsgBox "time limit on sheet has expired"
    End If

    End Sub</pre>


    Ken

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time-limit use of a workbook (2000 on)

    So now you're stuck with being a 5StarLounger for the next 600 posts <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> stuck for another 4 years (at current rate of posting), <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I can live with that.

    Ken

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Time-limit use of a workbook (2000 on)

    Rory, is the Workbook so easily hack-able if you use file-open level protection, such as (Excel 2003, from the macro recorder, naturally):

    ActiveWorkbook.SetPasswordEncryptionOptions PasswordEncryptionProvider:= _
    "Microsoft Enhanced DSS and Diffie-Hellman Cryptographic Provider", _
    PasswordEncryptionAlgorithm:="RC4", PasswordEncryptionKeyLength:=128, _
    PasswordEncryptionFileProperties:=True,
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    daftish question, but what's the strength of the password protection on extracting a zipped-up file - if that might be a runner, only distribute the workbook zipped?

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Time-limit use of a workbook (2000 on)

    I haven't kept touch on that issue. The older DOS PKZIP was notorious for very weak password encryption, easily broken, but I do not know how strong the encryption is in recent versions of WinZip or PKZIp.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Time-limit use of a workbook (2000 on)

    John,
    My assumption was that Leif's client was worried about someone giving the workbook to an outside company and I assume that someone might have the password to open it. I don't recall whether that level of encryption was available in Excel 2000.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time-limit use of a workbook (2000 on)

    I would suggest that if someone has a copy of the workbook and wants the information badly enough, they will retrieve it. Consequently, it may be best to not tempt them to go hunting for the code. With this in mind, I'd be inclined to work some type of scheme whereby once the workbook is opened, it displays fictitious but credible data. On activation of a hidden VBA process and subsequent keying in of a different password the data will be corrected. Some finite time after opening the workbook, if the second password has not been supplied; destroy the data correcting process, leaving the viewer with the belief that he is viewing valid data. This will also require an additional hidden process to confirm the validity of the data.
    Regards
    Don

  15. #15
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time-limit use of a workbook (2000 on)

    Thanks for all the ideas everyone, it has given me plenty of food for thought. I'm meeting the customer on Tuesday to find out exactly how protective they want to be, and I may then have a clearer idea of what can and cannot be achieved.

    I'm limited as to how much I can reveal, but basically the equipment being monitored is situated by roadsides and therefore accessible to all and sundry, albeit not legitimately. Typically, the end user would connect to it using the serial port on a laptop and download statistics and performance information, and my understanding is that it is this downloaded information that my client is keen to restrict. (The reason Excel is used is that the data can then be easily manipulated into charts, exported to csv files, etc.)

    I now appreciate that it would not be easy to prevent hacking by a determined end-user, so I need to find out what they consider the real threat to be. As we also control the software on the 'host' equipment, we may even be able to tie in that end to block unauthorised access.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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