Mike MacDonagh's Blog

Somewhere in the overlap between software development, process improvement and psychology

Tag Archives: excel

Manipulating web content in Excel, or, My Excel can haz ajax?

Yes. Well, kinda. Not really. Er… maybe. Hey look, it works!

There’s not much better than Microsoft Excel for mucking about with big grids of stuff really easily. I’ve often used Excel to “screen scrape” or at least “DOM scrape” web content for various reasons. One of the most common is to get stuff from a web app that doesn’t provide a “download to csv” option or a decent API. Another reason is to grab stuff from an RSS feed to mix in to whatever my spreadsheet is doing, or just to use it as a REST client. So I offer you the following generic solution, I’ll base this one around the RSS example…

Wikipedia, the absolute source of all knowledge and truth, says that for something to be AJAX “the use of JavaScript and XML is not actually required, nor do the requests need to be asynchronous”. Despite this  “AJAX” is generally considered to be an initialism and acronym for “Asynchronous JavaScript and XML”. Taking both of these into consideration I must conclude that AJAX means whatever we want it to mean. For me it’s when I’m using an XMLHttpRequest object in one way or another to yank some xml/html/whatever from a web server.

In Excel I’m not using javascript asynchronously but I am using xml and even a XMLHttpRequest object via com interop so for me that qualifies as doing ajax in Excel.

How to do it:

1. Avoid late binding of com objects and help yourself out by adding a reference (code editor: Tools -> References…) from the Excel app to a library that will provide a XMLHttpRequest object. Happily, “Microsoft XML, v6.0″ provides such a beastie for us.

2. Write some Ajaxy code to use the object like this:

Private Sub MMD_DoSomeAjaxyStuff()

'declare some useful vars
Dim req As XMLHTTP
Dim doc As DOMDocument
Dim url As String

'Some things to process the XML
Dim entries As IXMLDOMNodeList
Dim entry As IXMLDOmNode

'The RSS to get, naturally
uri = "https://mikemacd.wordpress.com/feed/"

'make a new XMLHttpRequest object
Set req = new XMLHTTP

'Load the xml
req.Open "GET", uri, , "", ""
req,Send
While req.ReadyState <> 4
  DoEvents 'yeah I know...
Wend

'Read the XML
Set doc = New DOMDocument
doc.loadXML req.responseText

'Do something with the RSS
Set entries = doc.getElementsByTagName("item")
For i = 0 To entries.Length - 1
    'Do something with the RSS like stick it in a cell
    Set entry = entries.Item(i)
    Cells(i + 1, 1).Value = entry.childNodes(1).Text
Next i

End Sub

3. w00t :D

Knowledge Centered Support in my project

Kelly Drahzal recently published this great presentation on Knowledge Centered Support which made me think a bit on the nature of support mechanisms. I’m currently engaged in rolling out a large and complex enterprise tool (Rational Portfolio Manager) and associated governance, portfolio management and project management practices in a large and complex client.

One of the things we need to do to get these pracitices and the tool embedded in an organisation is manage support. Our support takes two forms, tools support and process support. Normally when a person thinks they’re asking for one of them they’re actually asking for the other :P One of the interesting things about the support that my rollout team provides to the practitioners is that ultimately it’s a transient function – we won’t be the long term support team on this product, in fact support will be handed over to the centralised support function and the rollout team (comprised of external consultants (some IJIers, an IBMer and some independents) and contractors) will dissapate into the ether from whence it came. So obviously, as per Kelly’s presentation we’re very keen on knowledge centered support – we don’t want to waste our time, effort and brain power by re-recreating the answers to people’s problems.

So what do we actually do to try and avoid some of these problems and do some knowledge based support? We’re a transient support function so we don’t have and super tools or even specialist knowledge base management skills. What we do have is a highly skilled team and a number of communication channels.

We capture all support requests in a humble excel spreadsheet, regardless of their communication channel and categorise the requests into a number of categories. (Of course this gave me an excuse to write some cunning macros to keep everything updated automatically).

As well as providing lovely graphs the spreadsheet captures the issue and the response. As a result the team can all see who had what problem and how it was resolved. As problems are solved knowledge is created, capturing it in a spreadsheet is all well and good, and can be searched on by the support team but it’s not great in terms of sharing that knowledge broadly. (As it happens the support spreadsheet is publicly accessible via a guest account on our config management repository – but that doesn’t mean anyone is looking!)

To share the knowledge we communicate it through many channels. Sometimes it’s apparent that our education has been lacking some good guidance so we update the education programme (training courses, open surgeries, lunch ‘n’ learns). We have a wiki where we can post new bits of information, a message board/forum, emailing lists, laminated desk drops, a FAQ on the wiki and also some mentoring guides. One of the functions of our team is to mentor practitioners in the adoption of practices and tools and to do that we have a number of mentoring packages that we give to adopting teams. Ensuring that the mentors are all saying the same thing, giving the same solution to the same problem is important. One of the best ways of doing this it to get the mentors together to talk to each other, run through scenarios and gain consensus on the common answers. We also document these scenarios, sometimes in the practitioner facing User Guide and sometimes through mentor guides.

Follow

Get every new post delivered to your Inbox.

Join 345 other followers

%d bloggers like this: