Cookies and Excel

Using cookies straight from Excel Get it now

Why would you want to do this? You know what cookies are, and think of them as being a mechanism for web sites to store information about you so they can personalize your experience. As I’ve shown in other sections there are various mechanisms to allow you to pass information between sessions in Excel, and especially if you use an encoding mechanism like JSON, you can make that data very descriptive.

However, in all those cases we are talking about Office Application data interchangeability, and not opening up the connectivity to other things. Let’s say that you wanted to create a web page to be reporting on some activity in an Excel Sheet. Being able to read a cookie that had been maintained by Excel would be very useful. Using a cookie also frees you from having to store the information in a particular sheet. So let’s look at how this is done.

The downloads section on hiding JSON data provides a fully functional example in the workbook trackingdata.xlsm.

Approach
Here is the approach I’ve taken
  • Create a temporary web page that has the capability to read and write cookies
  • Navigate to that page to read data from the target cookie, deserialize the JSON data encapsulated in the cookie.
  • Update the cJobject , serialize, create another temporary web page that can write cookies, and update the cookie.
How it’s done
The example we are using is a tracking mechanism that gets updated each time an Excel file is opened or closed 
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

openingdata() was covered in another section and is simply the setup of the data at the beginning of the tracking session, and .Serialize is a method of the cJobject class. Lets look at that cCookie class in more detail. Essentially, both getCookie () and putCookie() use createCookieJar() to generate some javascript which will be executed through doCookie(). This will cause the value of the cookie to be placed in the DIV element, so it can be read as an element of the navigated to document.

Option Explicit
Private pHtmlHandle As Long
Private pHtmlName As String
Private pCookieName As String
Private pDays As Long

' everything to do with creating cookies in excel
Const cFailedtoGetHandle = -1
Public Property Get htmlName() As String
htmlName = pHtmlName
End Property
Public Property Get cookieName() As String
cookieName = pCookieName
End Property
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
Public Sub init(Optional hn As String = "cookiecjobject.html", _
Optional cn As String = "cookiecjobject", _
Optional ds As Long = 365)
pHtmlName = hn
pCookieName = cn
pDays = ds
End Sub
Public Sub createCookieJar(method As String, Optional content As String = "")
' create the script to get or put content
If createHtmlFile Then
' add 'mark of the web to allow to run locally
Print #pHtmlHandle, ""
Print #pHtmlHandle, ""
' create script
Print #pHtmlHandle, ""
' create div
Print #pHtmlHandle, "

If you can read this then active content is not enabled

"
Close #pHtmlHandle
End If
End Sub
Private Function createHtmlFile() As Boolean

pHtmlHandle = getHandle(pHtmlName)
createHtmlFile = (pHtmlHandle <> cFailedtoGetHandle)

End Function
Private Function getHandle(sName As String) As Integer
Dim hand As Integer
On Error GoTo handleError
hand = FreeFile
Open sName For Output As hand
getHandle = hand
Exit Function

handleError:
MsgBox ("Could not create file " & sName)
getHandle = cFailedtoGetHandle
End Function
Private Function quote(s As String) As String
quote = q & s & q
End Function
Private Function squote(s As String) As String
squote = qs & s & qs
End Function
Private Function q() As String
q = Chr(34)
End Function
Private Function qs() As String
qs = Chr(39)
End Function
Private Function curly(s As String) As String
curly = "{" & s & "}"
End Function

Finally, let's look at the cBrowser class which is invoked by the doCookie method of the cCookie class. This is a general purpose browser handling class.

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