Working with the del.icio.us API in VBA
September 30, 2009 @ 7:45 AM by JP • 1 views • No Comments »
Automate Excel has some useful code samples (as well as a cool domain name which I'd love to own). One of them is a workbook containing code for importing del.icio.us bookmarks into Excel. Unfortunately, the code seems to be broken and the blog hasn't been updated in months. So I downloaded the workbook, made a few changes, and humbly present to you my version of the workbook.
Instead of WinHTTP, it uses the usual XMLHTTP object I've used in the past with Twitter and TinyURL. Of course, all of this is based on Chandoo's original code found at Write Your Own Twitter Client using Microsoft Excel.
Besides actually working, the code is shorter and faster than the previous effort. I'm having fun with web APIs, so periodically you'll see more code like this. And like the Twitter app, this workbook could be developed into an add-in which could be used to add new bookmarks, delete bookmarks, follow trends etc, using the API described at del.icio.us.
Screenshot:
Here is the entire import code:
Dim Username As String
Dim Password As String
Username = txtUsername.Value ' username textbox
Password = txtPassword.Value ' password textbox
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", "https://" & Username & ":" & Password & _
"@api.del.icio.us/v1/posts/all", False
xml.Send
Application.DisplayAlerts = False
ActiveWorkbook.XmlMaps("delicious").ImportXml xml.ResponseText
Application.DisplayAlerts = True
A few new features/additions:
- Go button stays disabled until username and password are typed, no more error msgs about missing username or password
- Exit button has Cancel Property set to True, so just hit 'Esc' to close form
- No early bound references
And of course the workbook is unprotected (just like the original) so you can study and copy the VBA code.
One caveat: The code assumes that the username/password combination are correct. I didn't test it to find out what would happen if one or the other is incorrect, and I'm not aware of any part of the API (like Twitter's) that lets you validate login credentials. What I would do is test out bad usernames or passwords and parse the XML response looking for error messages.
FYI: I tried to contact the owner of automateexcel.com but didn't get a response.
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA (or subscribe via email). Keep Reading »
Previous Post: Finding values in an array without looping
Next Post: All about Comment shapes
Still not working for me
You need to be more specific. What isn't working? Did you step through the code to see where the error occurs?
actually I just try the old one again and it seems to be working.
I got an error that said that something is wrong in the comments (does that make any sense to you?)
It look like both of them working now (the old and the new one)
Thanks!
I get the message
-2147467529 – Method 'open' of object 'IXMLHTTPRequest' failed.
No data is returned.
On what line does the error occur?
-2147221080 – Method 'XMLMaps' of object '_Workbook' failed
What line causes the error?
Guys, you can just paste https://api.del.icio.us/v1/posts/all
in firefox, save the data as an .xml file and open it in Excel