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 thoughts on “Monitor Worksheet Changes via RSS

  1. 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. 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. 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.

  4. 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? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.