Working with the API in VBA

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

Update 4/26/2010: You might be using the API or the workbook found here: Working with the API in VBA. According to the Yahoo Developer Blog, all Yahoo APIs (including delicious which is owned by Yahoo) will be converted to use YQL, their own version of SQL. While this will mean more flexibility, it will also mean that any existing API code may stop working at any time, and will most likely be unsupported. So I fully expect the delicious workbook to stop working. I will soon be publishing the last of the API code I was working on for, and that will be it until I get up to speed with YQL.

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


Import delicious links

Here is the entire import code:

Dim xml As Object  ' MSXML2.XMLHTTP
Dim Username As String
Dim Password As String

Username = txtUsername.Value ' username textbox
Password = txtPassword.Value ' password textbox

Set xml = GetMSXML
xml.Open "GET", "https://" & Username & ":" & Password & _
    "", False

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 but didn't get a response.

Download the workbook (82 KB)

About JP

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 to learn more about VBA. Keep Reading »

Related Articles:

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 16 Comment(s) on Working with the API in VBA:

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

  2. I get the message

    -2147467529 – Method 'open' of object 'IXMLHTTPRequest' failed.

    No data is returned.

  3. moreproblemo writes:

    -2147221080 – Method 'XMLMaps' of object '_Workbook' failed

    • What line causes the error?

      • The error is caused by the following line:
        xml.Sendxml.Open "GET", "https://" & Username & ":" & Password & "", False

        I think the issue might be due to the following note (in the delicious API information JP links to above):
        "To access data from accounts created using a Yahoo! ID, use the same API's as below, but change the path to /v2, and make HTTP requests using OAuth"

        And it requires more than just changing the /v1/ to /v2/, as you get the error message: "438 – Object doesn't support this property or method", I think because the OAuth method requires you to encrypt the data sent, as well as adding extra heders.

        • I'll check it out as soon as I get some free time (LOL), if you want to fix it first I'm sure everyone visiting this page will be grateful!

        • Hah, thanks JP – I'm already pushing the boundaries of my knowledge and am up against a brick wall, I think it's the "tricky last step" (quote from the official Delicious API documentation!) about creating a string, then encrypting it, applying an algorithm, then some other stuff…

          Will try to post my findings when I've collected my thoughts.

  4. moreproblemo writes:

    Guys, you can just paste
    in firefox, save the data as an .xml file and open it in Excel

  5. I downloaded the workbook and it worked. I don't see anything wrong with it, but I may decide to post some VBA samples for the delicious API anyway.

    • Just a quick question – do you have an 'old' delicious username, or an account created from December 2009? From browsing around, it looks like Delicious have mandated the use of Yahoo accounts since that time, which then forces API calls to use OAuth, rather than the /v1/ URLs in the workbook.

      • Dan,

        The code works as written. As an example, call this function with your username and password.

        Function CheckDeliciousLastUpdate(username As String, password As String) As StringDim xml As MSXML2.XMLHTTPDim result As StringSet xml = CreateObject("MSXML2.XMLHTTP")With xml .Open "GET", "https://" & username & ":" & password & "", False .SendEnd Withresult = xml.responseTextCheckDeliciousLastUpdate = resultEnd Function
        • I've got this error loads of times over the past week or two, so it's not a timing issue (unless it's always broken during the US day, which seems highly unlikely!)

        • Hmm, looks like the code was filtered out, 2nd attempt without the &GT/&LT signs

          ?xml version="1.0" standalone="yes"?
          result code="api temporarily unavailable" /
          !– uncompressed/chunked Sun Apr 11 11:49:41 PDT 2010 –

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel