Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson
Services‎ > ‎Excel Liberation‎ > ‎

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, "<!DOCTYPE html>"
        Print #pHtmlHandle, "<!-- saved from url=(0021)http://www.mcpher.com -->"
        ' create script
        Print #pHtmlHandle, "<html><head><script type=" & quote("text/javascript") & ">"
        Print #pHtmlHandle, "function putContent(s) " & curly("document.getElementById(" & quote(pCookieName) & ").innerHTML = s ;")
        Print #pHtmlHandle, "function processCookie()"
        Select Case method
            Case "get"
                Print #pHtmlHandle, curly("putContent(getCookie(" & quote(pCookieName) & "));")
            Case "put"
                Print #pHtmlHandle, curly("putContent(putCookie(" & quote(pCookieName) & _
                    "," & squote(Replace(content, vbCrLf, "")) & "," & CStr(pDays) & "));")
            Case Else
                MsgBox "Unknown method - " & method
        End Select
        Print #pHtmlHandle, _
            "function putCookie(name,value,days) " & _
            "{if (days) {var date = new Date();" & _
            "date.setTime(date.getTime()+(days*24*60*60*1000));var expires = " & _
            quote("; expires=") & "+date.toGMTString();}else var expires = " & q & q & ";" & _
            "document.cookie = name+"; quote("=") & "+value+expires+" & quote("; path=/") & ";return (value);}"
        Print #pHtmlHandle, _
            "function getCookie(name) {" & _
            "var nameEQ = name + " & quote("=") & ";" & _
            "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(nameEQ) == 0) return c.substring(nameEQ.length,c.length); " & _
            " } return null;}"
        
        ' run script
        Print #pHtmlHandle, "</script></head><body onload=" & quote("processCookie();") & ">"
        ' create div
        Print #pHtmlHandle, "<div id=" & quote(pCookieName) & ">If you can read this then active content is not enabled</div></body></html>"
        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