(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.
1 |
Const cnsURLTimeline As String = "https://api.twitter.com/1.1/statuses/user_timeline.json" |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Public Function get_timeline_basestring(strNonce As String, strTimestamp As String, strUsername As String, intCount As Integer, blnExcludeReplies As Boolean) As String Dim tmpBase As String tmpBase = "count=" & CStr(intCount) tmpBase = tmpBase & "&" & "exclude_replies=" & LCase(CStr(blnExcludeReplies)) tmpBase = tmpBase & "&" & "oauth_consumer_key=" & cnsOauthConsumerKey tmpBase = tmpBase & "&" & "oauth_nonce=" & strNonce tmpBase = tmpBase & "&" & "oauth_signature_method=" & cnsMethod tmpBase = tmpBase & "&" & "oauth_timestamp=" & strTimestamp tmpBase = tmpBase & "&" & "oauth_token=" & cnsOauthToken tmpBase = tmpBase & "&" & "oauth_version=" & cnsOauthVersion tmpBase = tmpBase & "&" & "screen_name=" & strUsername get_timeline_basestring = Trim(cnsAPIMethodG & "&" & UrlEncode((cnsURLTimeline)) & "&" & UrlEncode(tmpBase)) End Function |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
Public Function get_timeline(strUsername As String, intCount As Integer, blnExcludeReplies As Boolean) As Boolean 'create nonce Dim strNonce As String strNonce = get_GUID() 'create timestamp Dim strTimestamp As String strTimestamp = get_timestamp() 'create basestring Dim strBase As String strBase = get_timeline_basestring(strNonce, strTimestamp, strUsername, intCount, blnExcludeReplies) 'create composite key Dim strKey As String strKey = cnsOauthConsumerSecret & "&" & cnsOauthTokenSecret 'calculate oauth_signature Dim strOauthSig As String strOauthSig = UrlEncode(Base64_HMACSHA1(strBase, strKey)) 'create authorization header Dim strHeader As String strHeader = get_header(strNonce, strTimestamp, strOauthSig) 'get status Dim objRest As WinHttp.WinHttpRequest Set objRest = New WinHttp.WinHttpRequest Dim strGetData As String strGetData = "count=" & CStr(intCount) & "&exclude_replies=" & LCase(CStr(blnExcludeReplies)) & "&screen_name=" & strUsername objRest.Open cnsAPIMethodG, cnsURLTimeline & "?" & strGetData, False objRest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" objRest.setRequestHeader "Authorization", strHeader objRest.send objRest.waitForResponse If objRest.Status = "200" Then Dim objResp As Object Set objResp = JSON.parse(objRest.responseText) Dim intZ As Integer For intZ = 1 To intCount Debug.Print objResp.Item(intZ).Item("created_at") & " - " & objResp.Item(intZ).Item("text") 'Or output to rows in spreadhseets, rows in in a table, etc... Next intZ get_timeline = True Else get_timeline = False End If Set objResp = Nothing Set objRest = Nothing End Function |
Below is a sample of the last five tweets pulled from Stephen Colbert’s Twitter 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!
Awesome, Greg!
Loads of thanks!
I will test it when I’m home.
Firewall at work kind of ruins everything.. 😛
Markus
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
Glad you found it useful!