(also see my related posts Tweeting with VBA and Using Twitter’s OAuth Tool)

In the comments section of my previous post about using VBA to post to Twitter, Markus had asked about using VBA to pull a user’s timeline. Much of the code I posted before can be fairly easily modified and reused, but there are a few more significant modifications that have to be made in order to get everything to work (primarily when it comes to parsing the JSON response). So, without further ado…

Here’s a quick summary of the modifications we’ll need to make:

    • The request goes to a specific URL (https://api.twitter.com/1.1/statuses/user_timeline.json). As before, we’ll define a string constant (cnsURLTimeline) to hold this URL.
    • The request uses the GET method, so we’ll have to make sure to use the proper method in constructing our base string, and make sure we construct our HTTP request properly.
    • When pulling user timelines, there are a number of optional parameters you can include, such as only pulling a specified number of recent tweets, filtering out retweets and replies, Twitter username, etc… These optional parameters are listed on the ‘GET statuses/user_timeline’ API documentation page. We’ll have to incorporate these parameters into our base string calculations, and into our HTTP request. For this example, I’ll utilize three of these parameters:
      • screen_name – to specify whose timeline I’m pulling)
      • count – to specify the number of tweets to pull per request. Twitter currently limits you to retrieving 200 tweets per request, and 300 requests every 15 minutes when authenticating from a registered app (as we are).
      • exclude_replies – to pull only the initial Tweets on a user timeline (and filter out subsequent replies/conversation threads
    • The response we’ll receive is in JSON format, and the rather simple JSON parsing technique used previously seemed inadequate to parsing the longer/more complicated response we’ll get here. Accordingly, I have incorporated additional JSON parsing code. As before, this code only uses native Microsoft components/libraries/add-ins.
    • Twitter timelines with VBA by Greg Williams is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
    • Code is supplied as-is. All use is at your own risk.

 

JSON PARSING MODULES AND REFERENCES

In order to parse our JSON response later on, we’ll use a slightly modified version of Michael Glaser’s VB6 JSON parser and Steve McMahon’s StringBuilder class. I’ve posted a .bas file for the JSON parser and a .cls file for the StringBuilder class, which you can simply import into your VBA project.

You’ll also need to add references (by going to to Tools->References in the VBA Editor) to “Microsoft Scripting Runtime” and “Microsoft ActiveX Data Objects 2.8 Library”.

 

CONSTANTS

We’ll add one new constant to store the URL for the timeline request.

 

TWITTER FUNCTIONS

As we saw previously, different Twitter API calls require slightly different base strings. For this example, we’ll use the get_timeline_basestring function, which constructs a properly-formatted base string when using the three query parameters (count, exclude_replies, and screen_name) mentioned above. NOTE: as mentioned in my previous post, from what I’ve seen, Twitter base strings must be constructed in a very specific order. When incorporating other parameters, use the OAuth tool in your Twitter developer account to “preview” the base string that Twitter expects, and make sure you’re constructing it properly in your VBA code.

 

Now that we have our base string, the Twitter call is pretty similar to everything we’ve done before. Note that since this is a GET request, the parameters get appended to the request URL with the ‘?’ character.

Below is a sample of the last five tweets pulled from Stephen Colbert’s Twitter timeline

timeline

Note that the tweet text may contain URL-encoded characters and backslash escape characters. There are plenty of VBA URL-decode functions out there, and stripping out the backslashes shouldn’t be too difficult.

Comments/questions welcome!

3 Thoughts on “Twitter Timelines with VBA

  1. Markus on June 20, 2013 at 10:42 pm said:

    Awesome, Greg!
    Loads of thanks!
    I will test it when I’m home.
    Firewall at work kind of ruins everything.. 😛

    Markus

  2. Matte Black on June 25, 2013 at 4:14 am said:

    Hi Greg

    Just a note to say thanks for posting this – it’s made the whole oAuth process very easy to follow, and I’ve managed to adapt your code to solve a thorny problem. You’re a star!

    Cheers – Matte Black

Leave a Reply

Your email address will not be published. Required fields are marked *

Post Navigation