Monitor Worksheet Changes via RSS

I love RSS. If you have a website and don’t have a feed, I don’t follow it. I monitor stuff via Google Reader and it’s a great way to keep up on a lot of sites. So it should only follow that monitoring spreadsheet changes via RSS would be great too. Right? Well, not really, but that never stopped me before.

First some caveats. This is just an experiment and not meant for general use. There may actually be some bugs in it, if you can believe that. If you want to modify the code for your own use, be warned that if you monitor too many cells it might be slow. Or you might get so much information that it’s worthless. OK, now that that’s over.

An RSS feed is an XML file that sits on a web server. This blog has such an XML file. When I post this blog entry, WordPress will update the XML file with an entry for this post. Occasionally Google Reader will check the XML file and see if there’s anything new. If there is, it will display the new stuff for anyone who has subscribed to the feed.

It’s fairly trivial to create an XML file, even from Excel. The potentially difficult part is putting that file on a web server. Oh, except for one little thing. I have a webserver right on my computer and you might too. Mine is called “C:\Users\dick\Dropbox\Public\”. Did I trick you? Dropbox has a Public folder and you can get a “public link” from any file in that folder, including the XML file we’re about to create. What a simple way to publish something to the intertubes. Here’s how you get that public link.

Alright, enough screwing around. Let’s get to the code.

I start with a class module called CChange (and its parent CChanges). CChange has the following read/write properties:

Address - the cell address we're watching for changes and a way to uniquely identify the instance.
OldValue - the value in the cell before it changed.
NewValue - the value in the cell after it changed.
Modified - a time stamp when the changed occurred.

I’ve named a range on the sheet called RSSWatch. When the workbook opens, a CChange object is created for every cell in that range.

Sub Auto_Open()
   
    Set gclsChanges = New CChanges
   
    gclsChanges.Initialize
   
End Sub

Public Sub Initialize()
   
    Dim clsChange As CChange
    Dim rCell As Range
   
    For Each rCell In Sheet1.Range(gsNAMEDRNG).Cells
        Set clsChange = New CChange
        With clsChange
            .Address = rCell.Parent.Name & "!" & rCell.Address
            .OldValue = rCell.Value
            .NewValue = .OldValue
        End With
        Me.Add clsChange
    Next rCell
   
End Sub

These CChange objects are just sitting out there waiting to record any changes. They all have a Modified date of 12:00:00 AM (because I didn’t set anything) and in this state they won’t be written to the XML file. In the ThisWorkbook module, I use the Workbook_SheetChange event to monitor my range for changes. You might notice that I switch pretty liberally between sheet-specific references and general references. For instance, in the above Initialize method, I limit my range to Sheet1. When I went to code the event, I thought that someday I would want this to monitor different ranges on different sheets, so I used a Workbook level event. It’s totally inconsistent, but it will be helpful if you just ignore it.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
    Dim clsChange As CChange
    Dim rCell As Range
    Dim rRng As Range
   
    On Error Resume Next
        Set rRng = Sh.Range(gsNAMEDRNG)
    On Error GoTo 0
   
    If Not rRng Is Nothing Then
        If Not Intersect(Target, rRng) Is Nothing Then
            For Each rCell In Target.Cells
                Set clsChange = gclsChanges.Change(Sh.Name & "!" & rCell.Address)
                If Not clsChange Is Nothing Then
                    clsChange.NewValue = rCell.Value
                    clsChange.Modified = Now
                End If
            Next rCell
        End If
    End If
   
End Sub

For every cell that has changed, I find it’s CChange brother and change the NewValue and Modified properties. I haven’t written the XML file yet. I still just have a bunch of CChange objects, except that at least one of them has a Modified property that will make it eligible to be included in the file. Changes get made and recorded and only the last value and time are saved. Then, when the workbook is saved, any eligible CChange objects are written to the file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
    If Not gbDEBUG Then
        gclsChanges.WriteRSS
    End If
   
End Sub

And that’s pretty much all there is. Just kidding, there’s lots more. Before we look at more code, let’s take a look at what the file might look like after a few changes.

I’m sure you’re all experts on XML, so I’ll spare you the details, but the general hierarchy goes like this

rss
  channel
    title
    link
    description
    language
    lastBuildDate
    ttl
    item - one or more of these guys
      title
      link
      description
      pubDate
    /item
  /channel
/rss

Now that you know what the file looks like, let’s write one. I called the WriteRSS method from the Workbook_BeforeSave event.

Public Sub WriteRSS()
             
          Dim xmlDoc As MSXML2.DOMDocument
          Dim xmlChannel As MSXML2.IXMLDOMElement
          Dim xmlLastBuild As MSXML2.IXMLDOMElement
          Dim xmlItem As MSXML2.IXMLDOMElement
          Dim clsChange As CChange
          Dim dtMax As Date
         
10        If Me.HasChanges Then
20            If Not Me.FileExists Then Me.CreateFile
             
30            Set xmlDoc = New MSXML2.DOMDocument
40            xmlDoc.Load gsPATH & Me.Filename
50            Set xmlChannel = xmlDoc.SelectSingleNode(gsXRSS).SelectSingleNode(gsXCHANNEL)
60            Set xmlLastBuild = xmlChannel.SelectSingleNode(gsXBUILD)
70            Me.LastBuildDate = ConvertDate(xmlLastBuild.Text)
80            dtMax = Me.LastBuildDate
             
90            For Each clsChange In Me
100               If clsChange.ShouldWrite Then
110                   Set xmlItem = clsChange.xmlItem(xmlDoc)
120                   xmlChannel.appendChild xmlItem
                     
130                   If clsChange.Modified > dtMax Then dtMax = clsChange.Modified
140               End If
150           Next clsChange
             
160           Me.LastBuildDate = dtMax
170           xmlLastBuild.Text = Format(Me.LastBuildDate, gsFMTDATE)
180           FormatXMLDoc xmlDoc
190           xmlDoc.Save gsPATH & Me.Filename
200       End If
         
End Sub

Generally, I’m creating an XML file if it doesn’t exist, reading that file in, appending Items to it for any changes, and writing that file back out. The first thing I do is make sure there’s something to write. In line 10, I call the HasChanges property, which loops through all the CChange instances to see what’s eligible. If nothing has changed, there’s no need to create the XML file.

Line 20: If there isn’t already a file, I need to make one. I check to see if it’s out there.

Public Property Get FileExists() As Boolean
   
    FileExists = Len(Dir(gsPATH & Me.Filename)) > 0
   
End Property

Public Sub CreateFile()
   
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xmlRss As MSXML2.IXMLDOMElement
    Dim xmlVer As MSXML2.IXMLDOMAttribute
    Dim xmlChannel As MSXML2.IXMLDOMElement
    Dim xmlNode As MSXML2.IXMLDOMElement

    Set xmlDoc = New MSXML2.DOMDocument
   
    Set xmlRss = xmlDoc.createElement(gsXRSS)
    Set xmlVer = xmlDoc.createAttribute(gsXVER)
    xmlVer.Value = gsRSSVERSION
    xmlRss.Attributes.setNamedItem xmlVer
   
    Set xmlChannel = xmlDoc.createElement(gsXCHANNEL)
   
    Set xmlNode = xmlDoc.createElement(gsXTITLE)
    xmlNode.Text = Me.Filename
    xmlChannel.appendChild xmlNode
   
    Set xmlNode = xmlDoc.createElement(gsXLINK)
    xmlNode.Text = gsLINK
    xmlChannel.appendChild xmlNode
   
    Set xmlNode = xmlDoc.createElement(gsXDESC)
    xmlNode.Text = "Changes made to " & ThisWorkbook.Name
    xmlChannel.appendChild xmlNode
   
    Set xmlNode = xmlDoc.createElement(gsXLANG)
    xmlNode.Text = gsLANG
    xmlChannel.appendChild xmlNode
   
    Set xmlNode = xmlDoc.createElement(gsXBUILD)
    xmlNode.Text = Format(Now - 1, gsFMTDATE)
    xmlChannel.appendChild xmlNode
   
    Set xmlNode = xmlDoc.createElement(gsXTTL)
    xmlNode.Text = glTTL
    xmlChannel.appendChild xmlNode
   
    xmlRss.appendChild xmlChannel
   
    xmlDoc.appendChild xmlRss
   
    xmlDoc.Save gsPATH & Me.Filename
   
End Sub

You’ll need to set a reference to Microsoft XML, v6.0 or similar. CreateFile sets up everything in the file that’s not an Item, like the title, link, description, language, etc. The basics of XML generation are 1) create a new node and 2) append it to its parent node.

Line 40: I read in the existing file or the one I just created. The Load method populates the XMLDOMDocument with all the hierarchies and data. Line 50 and 60 go find specific nodes in the file that I’m interested in. I want the Channel node because I’ll be appending Items to it. I want the lastBuildDate node so I can compare that to CChange Modified properties and only write new changes.

Line 70: The RSS example I got off the web showed dates formatted like Tue, 06 Mar 2012 21:28:01 CST. Neither Excel’s CDate or Datevalue functions can convert that to a date, so I have to strip off the day and the timezone, which I do in a separate function.

Line 80: Ultimately I want to make my lastBuildDate in the XML file equal to the latest date of all the CChange objects. I’m initializing the maximum variable here. If I don’t, every cell’s Modified would be greater than this date.

Line 100: For every CChange, I check to see if it needs to be written to the file.

Public Property Get ShouldWrite() As Boolean
   
    ShouldWrite = Me.Modified >= gclsChanges.LastBuildDate
   
End Property

If it’s been modified since the last time I wrote the file, it gets written this time. I really should also check to see if OldValue is different than NewValue. Right now if you change a cell and then change it back, it will still show up as a change. Sounds like a V2 enhancement.

The rest of the loop creates an Item, appends it to the Channel, and updates the maximum Modified date. The xmlItem property returns an object that can be appended.

Public Property Get xmlItem(xmlDoc As MSXML2.DOMDocument) As MSXML2.IXMLDOMElement
   
    Dim xmlReturn As MSXML2.IXMLDOMElement
    Dim xmlSubItem As MSXML2.IXMLDOMElement
   
    Set xmlReturn = xmlDoc.createElement(gsXITEM)
   
    Set xmlSubItem = xmlDoc.createElement(gsXTITLE)
    xmlSubItem.Text = Me.Address
    xmlReturn.appendChild xmlSubItem
   
    Set xmlSubItem = xmlDoc.createElement(gsXLINK)
    xmlSubItem.Text = gsLINK
    xmlReturn.appendChild xmlSubItem
   
    Set xmlSubItem = xmlDoc.createElement(gsXDESC)
    xmlSubItem.Text = Me.Description
    xmlReturn.appendChild xmlSubItem
   
    Set xmlSubItem = xmlDoc.createElement(gsXPUBDATE)
    xmlSubItem.Text = Format(Me.Modified, gsFMTDATE)
    xmlReturn.appendChild xmlSubItem
   
    Set xmlItem = xmlReturn
   
End Property

Not much to this – make a node and append it. The Description property is a read-only property that makes a nice English sentence describing what happened.

Line 160: I change the LastBuildDate to equal the max, then change that node in the XML file.

Line 180: This code I stole from VB Helper. It adds the line breaks and indentation that, while not necessary, is really helpful when debugging. No, I didn’t write this code perfectly the first time.

Finally I save the modified XML document. Dropbox publishes to the web and Google Reader reads it. Here’s what the last change looks like in the reader.

If I ever look at this again, here’s what I’d do in version 2

  • Put all the changes in one Item rather than one per cell
  • Check to see if a cell was changed and changed back and exclude it
  • Get the timezone from the Windows API rather than hardcoding it in a constant
  • Modify to use mulitple ranges on multiple sheets
  • Put the code in an add-in and look for workbooks to monitor
  • Change the Description to be easier to read

I like this Public Dropbox folder. I’m thinking of using it as a version control system to keep my add-ins up-to-date from multiple computers. It’s a shame that some companies block it. Oh well, your thoughts on the RSS code are welcome.

You can download RSSChanges.zip

6 Comments

  1. macutan says:

    Hey Dick,

    I noticed you use dropbox… I saw the below post on lifehacker that you might find interesting, I tried it and it works.

    http://lifehacker.com/5893262/get-3gb-of-free-space-on-dropbox-by-using-camera-upload?tag=dropbox

    I have summarized the steps below (from different comments from that post) to allow you to get the 3GB.

    I know it might be offtopic but wanted to contribute in case you had not seen this promotion by dropbox.

    UPGRADE DROPBOX: http://dl-web.dropbox.com/u/17/Dropbox%201.3.23.exe

    1. cmd (run as admin)
    2. fsutil file createnew YOURMOVIE.avi 530000000 (do this step 5 times with different names
    3. put files in a folder called “DCIM”
    4. http://www.digital-digest.com/software/download.php?sid=470&ssid=0&did=1
    5. Create iso
    6. http://www.daemon-tools.cc/eng/downloads/dtproAdv
    7. Mount iso file created previously
    8. use dropbox option from autoplay

    Peace

  2. Paul Tupper says:

    I really like learning from your blog and this post piqued my interest since I too am a dropbox fan.

    I use dropbox on my home PC which has Win7 64-bit and Office 2010 and I have had some recent challenges with your example and one of my own beyond the conditional compilation step below:

    #If VBA7 Then ‘I’m using Office 2010
    Private Declare PtrSafe Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As Long)
    #Else ‘I’m still using Office 2007
    Private Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As Long)
    #End If

    Usually my 2010/64-bit conversion errors are in variable declarations changing to LongLong or variant, however, this is the first Property type mismatch I’ve encountered.

    Public Property Set Parent(obj As CChanges): mlParentPtr = ObjPtr(obj): End Property ‘type mismatch

    Any suggestions?

  3. Dick Kusleika says:

    Paul: I’m sadly ignorant of the 64-bit API calls, so this is pure speculation. This

    http://msdn.microsoft.com/en-us/library/ee691831.aspx

    seems to say ObjPtr returns a LongPtr for 64bit. Maybe that means mlParentPtr needs to be something other than a Long.

  4. Paul Tupper says:

    I set gsPATH to a shared folder of mine and I’ve got it to compile by changing the following code below:

    Code:
    Private mlParentPtr As Variant ‘Long ‘LongPtr
    ”””””””””””””””””””””””””””””””””””””””””””””””
    ’32/64-bit compatibility added. Paul Tupper 1/5/2012

    ‘Public Declare statements are not compatible between 32-bit and 64-bit versions of Excel. The 64-bit version
    ‘requires “PtrSafe” to be inserted into the declare statement. In order for the code to work in both environments
    ‘I have added a conditional compilation statement to check which version is in use.

    ‘Microsoft devised two compile constants to handle this:
    ‘ VBA7: True if you’re using Office 2010, False for older versions
    ‘ WIN64: True if your Office installation is 64 bit, false for 32 bit.
    ”””””””””””””””””””””””””””””””””””””””””””””””
    #If VBA7 Then ‘I’m using Office 2010
    Private Declare PtrSafe Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As LongPtr)
    #Else ‘I’m still using Office 2007
    Private Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As Long)
    #End If

    However, I tried running some of the test subs and got “Run-time error ’91′:Object variable or With block variable not set.” Obvoiusly, I need to spend some more time understanding it.

  5. Dick Kusleika says:

    You get that error on the same line as the type mismatch?

  6. Paul Tupper says:

    Actually, here: Set xmlChannel = xmlDoc.SelectSingleNode(“rss”).SelectSingleNode(“channel”) in TEST_xmldoc & TEST_format was stepping through ok before I hit F5 and it crashed. Sorry I haven’t had any free time to diagnose but hopefully eventually I will learn.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: