Passing data to and from Excel
Looking back to a previous article covering how to store and pass Excel data, I realized that the solutions discussed there were still relying on Excel or other
office applications being the 'transmission medium'. Certainly the use of JSON
serialization allowed the data to be 'self describing' in a more liightweight
way than say XML, but nevertheless I was looking for a way to pass data around
that didn't necessarily need Excel to be involved, even though it was the data
source. The download companion for this article is here and can be found under the section 'Hiding data in Excel Objects and JSON serialization'
Before you run the example you should consider where to allow Excel generate
it's temporary browser files, and change the constant in the ExampleCode module
accordingly. By default it is set to Public Const cCookieHtml = "D:\googlechart/cookie.html". There may be issues with browser security also, since it wants to run a locally
generated .html file.
Using a cookie as a tracking mechanism
In that previous article, there are various examples of hiding data in cells and shapes, and the example
provided used the illustration of how to create a hidden tracking mechanism for
how many times a sheet had been opened as an example of the technique. I'm going
to expand on that in this article, this time adding a cookie as one of the tracking
mechanisms.
In principle, this is rather straightforward
- Generate a web page that can read cookies
- Navigate to the web document and Interpret the current cookie data
- Update and rewrite the cookie
This translates into a worksheet open event that looks like this
Sub wbOpenCookieVersion()
'workbook has opened
Dim cc As cCookie
Set cc = New cCookie
' get current contents, setup opening data, write it back
With cc
.init cCookieHtml
.putCookie (openingData(.getCookie).Serialize)
End With
Set cc = Nothing
End Sub
Naturally there are some classes here that need explanation. The interesting methods
of cCookie are as follows. The createCookieJar method is of course the method
that generates the web page that can read and write cookies. I wont reproduce
that here as you can read it in the downloadable example, but later in the article
I will go through the web page it generates.
Public Function getCookie() As String
' with an initialized cookie, start up a browser and get it
createCookieJar "get"
getCookie = doCookie
End Function
Public Function putCookie(content As String) As String
' with an initialized cookie, start up a browser and get it
createCookieJar "put", content
putCookie = doCookie
End Function
Private Function doCookie() As String
Dim cb As cBrowser
Set cb = New cBrowser
' browse to it
With cb
.init
.Navigate pHtmlName
doCookie = .ElementText(pCookieName)
.kill
End With
Set cb = Nothing
End Function
You will also see another class here, cBrowser, which is a generally useful class
for navigating to web pages using an Internet Explorer application object.
Option Explicit
Private pHtml As String
Dim pIeOB As InternetExplorer
' everything to do with accessing web pages from Excel
Public Property Get Browser() As InternetExplorer
Set Browser = pIeOB.Application
End Property
Public Sub init()
Set pIeOB = CreateObject("InternetExplorer.Application")
End Sub
Public Sub Navigate(fn As String)
' bring up the web page requested
pHtml = fn
With Browser
.Navigate2 pHtml
Do: DoEvents: Loop Until .ReadyState = READYSTATE_COMPLETE And Not .Busy
End With
End Sub
Public Property Get ElementText(eName As String) As String
Dim e As IHTMLElement
ElementText = "Did not find element " & eName
With Browser.Document
For Each e In .all
If e.ID = eName Then
If e.innerText = "null" Then
ElementText = ""
Else
ElementText = e.innerText
End If
Exit Function
End If
Next e
End With
End Property
Public Sub kill()
Browser.Quit
Set pIeOB = Nothing
End Sub
Openingdata().Serialize will take the cJobject class created from the initial value
of the cookie, update it and reserialize back to JSON to be written back to the
cookie. For more information on this class please refer to a prior article.
Private Function openingData(s As String) As cJobject
' create serialization object
Dim cj As cJobject
Set cj = New cJobject
If Len(s) > 0 Then Set cj = cj.deSerialize(s)
If cj.Key <> cKeyName Or Not cj.isValid Then
Set cj = New cJobject
cj.init Nothing, cKeyName
End If
' setup the data relevant to opening
With cj.Add("lastaccess")
.Add ("username"), Environ("USERNAME")
.Add ("startedat"), Now
End With
' will need these later so add them in case they dontr exist already
With cj.Add("summary")
.Add ("timeopen")
.Add ("countopen")
End With
Set openingData = cj
End Function
The generated html file from createCookieJar from the .putCookie (closingData(.getCookie).Serialize)
action is as follows. (Not a pretty site but luckily we dont have to read it).
<!DOCTYPE html>
<!-- saved from url=(0021)http://www.mcpher.com -->
<html><head><script type="text/javascript">
function putContent(s) {document.getElementById("cookiecjobject").innerHTML
= s ;}
function processCookie()
{putContent(putCookie("cookiecjobject", '{ "hiddendata": { "lastaccess":
{ "username": "fhk647", "startedat": "2/22/2011
4:15:24 PM", "finishedat": "2/22/2011 4:36:26 PM" },
"summary": { "timeopen": "1831", "countopen":
"3" } }}',365));}
function putCookie(name,value,days) {if (days) {var date = new Date();date.setTime(date.getTime()+(days*24*60*60*1000));var
expires = "; expires="+date.toGMTString();}else var expires = "";document.cookie
= name+"="+value+expires+"; path=/";return (value);}
function getCookie(name) {var na = name + "=";var ca = document.cookie.split(';');for(var
i=0;i < ca.length;i++) { var c = ca[i]; while (c.charAt(0)==' ') c = c.substring(1,c.length);
if (c.indexOf(na) == 0) return c.substring(na.length,c.length); } return null;}
</script></head><body onload="processCookie();">
<div id="cookiecjobject">If you can read this then active content
is not enabled</div></body></html>
A note about Browser Security
One of the annoying things I came across while developing this, and the thing that
took the longest to figure out, was how to enable browsers execute active content
when it was embedded in a local .html file. I came across a concept called 'Mark
of the Web'. This is just a comment in the .html file that seems to tell the
browser to forget all it knows about local script security and run it anyway.
It's kind of bizarre, but it works. To add a 'Mark of the Web', introduce 2 lines
like this at the top of an .html file. It's a funny world.
<!DOCTYPE html>
<!-- saved from url=(0021)http://www.mcpher.com -->
Summary
If you have been reading this series of articles, you may have noticed a common thread.
Im trying to open up the use of Excel to other capabilities through linkages
to google visualization, roadmapping, embedding JSON . The more we can seamlessly bring in additional capabilites, the more effective
our use of Excel can be. I welcome feedback and comments to excel@mcpher.com
, via this forum, or through www.mcpher.com