If you want to create an time based evaluation version of your excel project, you need some way to determine what day it is. One way, of course, is to use the built-in functions like Now to query the system time. That also means that one way an evaluator can use your product forever is to change his system clock whenever your program checks – usually once at start up.
An alternative to using the system clock is to use the web. You can query time.gov to get the official US time. I’m pretty sure you can get the “official” time in any country from this website by changing the GMT offset in the URL.
Dim ieApp As Object
Dim i As Long
Dim sDate As String
Dim lFirstCom As Long
Const sURL = “http://www.time.gov/timezone.cgi?Pacific/d/-6”
Const lElement As Long = 36
Const ieREADYSTATE_COMPLETE As Long = 4
Set ieApp = CreateObject(“InternetExplorer.Application”)
ieApp.navigate sURL
Do
DoEvents
Loop Until ieApp.ReadyState = ieREADYSTATE_COMPLETE
sDate = Left(ieApp.Document.all(lElement).innertext, 40)
lFirstCom = InStr(1, sDate, “,”)
GetRealDate = CDate(Right(sDate, Len(sDate) – lFirstCom))
End Function
I don’t want to give the impression that this solves all of your security problems. I’m going on the assumption that you already plan to develop in Excel and are aware that just about anyone can have access to your code. If you already made that choice, then this technique will be one more hurdle for people to jump before they steal your stuff.
I need some error checking in here to avoid the run time error when there’s no internet access. I couldn’t decide if I would return a date that’s really far into the future, or just some recognizable date. I think the future is the way to go, like
GetRealDate = #1/1/3012#
Else
lFirstCom = InStr(1, sDate, “,”)
GetRealDate = CDate(Right(sDate, Len(sDate) – lFirstCom))
End If
Back when I had my first PC and I wasn’t even a teen, I did not pay for my shareware. I got around the date checker by coding my first and only app in C which did nothing more than spit the current date out. Then I ran a batch file to output this into a text file, change the date to an earlier time, run the shareware app, and then change the date back after I was done by feeding that text file into the date prompt. Even back in those DOS days, having the wrong date on your clock was more trouble than it was worth.
I can think of one common situation where that method would fail while the system-clock method would be foolproof: at a job with a paranoid IT staff. I do not have access to much on my PC at work; both the net and the system clock are off limits. While the system clock method would work since I’m not allowed to change it, the internet method wouldn’t work since you wouldn’t be able to reach the server. Maybe both methods are needed.
Even better, you could set it up to try the internet method and, if that fails, fallback to the system clock. Then set a counter in the registry to limit the number of times this fallback can be used. That should at least keep casual theft down. If anyone wants to go to the trouble of defeating it anyway, they can have it. There can’t be too many willing to do it.
Hi Dick,
This errors out for me (type mm on CDate), because at 40 characters, the sDate string starts to go into the “Click here to refresh” message. (Maybe because Friday is two letters shorter than Thursday??)
I changed the line to :
sDate = Split(ieApp.Document.all(lElement).innertext, vbNewLine)(1)
and that seemed to work better.
My experiences of getting data from the web through parsing the html are generally unfavourable – I once wrote an app to feed live sports scores into the excel status bar, but had to change it slightly every time because the website formatting kept altering.
Regards
Charlie
Charlie – I knew that Left function would come back to bite me, I was just too lazy to do it right. I like your fix and I’m going to update it.
“My experiences of getting data from the web”
Mine too. I tried to get sports results from cbssportsline and sometimes it was table 25 and sometimes it was table 26. If they would just name their elements, it would be so nice.
This particular site has been at element 36 for about 3 years, so I’m counting on a little stability. I need a good, quick program to find the right html element based on some criteria. I think I can make a good one, and a quick one, but not both.