Category Archives: Projects

(also see my related posts Tweeting with VBA and Twitter Timelines with VBA)

I’ve had a few inquiries/questions from people who have modified my VBA Twitter code, but have run into problems (namely, ‘401’ error responses when trying to authenticate). While I don’t promise the following will help absolutely everyone, I think it’ll prove useful.

When modifying the code to work with other Twitter APIs, you will likely have to pass additional parameters when creating your OAuth base string. Unfortunately, it doesn’t appear to simply be a matter of appending the new parameter to the end of the existing base string function! Depending on which API you’re using, Twitter seems to expect the base string parameters to be in a specific order. For example, when creating a base string for use with the statuses/update.json API, your base string parameters should be in the following order:

• oauth_consumer_key
• oauth_nonce
• oauth_signature_method
• oauth_timestamp
• oauth_token
• auth_version
• status/li>

Now, say you wanted to modify the code for use with the statuses/user_timeline.json API. You might think you could just 1) remove the status parameter, and 2) append the count, exclude_replies, and screen_name parameters to the end. If you simply do that, you’ll likely get 401 error responses. Instead, the parameters must be ordered as follows:

• count
• exclude_replies
• oauth_consumer_key
• oauth_nonce
• oauth_signature_method
• oauth_timestamp
• oauth_token
• auth_version
• screen_name

The easiest way to figure out what order parameters should be in for any particular API is to use the ‘OAuth Tool’ on your app’s page in your developer account.

The tool will pre-populate your keys and tokens, so all you need to worry about is specifying the proper request type (GET, POST, etc…), URL for the API you’re using, and whatever API parameters you’ll be including.  So, if I wanted to generate a sample base string for a call to statuses/user_timeline.json with count, exclude_replies, and screen_name parameters, here’s what I’d enter:

When you click the ‘See OAuth signature for this request’ button, you’ll get a sample of the exact base string Twitter expects you to use.

After you have this sample, do a Debug.Print of your base string function, and make sure that the parameters in the string you’re creating are in the exact same order as what Twitter expects (don’t worry if the oauth_nonce and oauth_timestamp differ…the important part is the parameter order).

ADDENDUM (7/10/13) – I’ve been looking in Twitter’s documentation for a definitive statement re: parameter order. Haven’t found it yet, but I have found a couple of other blog posts that mention similar problems. So, I still strongly recommend that you construct your base string ordering your parameters in the same order as they appear on the OAuth tool.

(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

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!

(also see my follow-up posts Twitter Timelines with VBA and Using Twitter’s OAuth Tool)

I’ve recently been working on a VBA-based project which incorporates the ability to post status updates to Twitter (i.e., tweeting).

In putting it together, I consulted various code samples, specs, API documentation, blog/forum posts, etc… I’m sharing my efforts here in the hopes of saving anyone else working on a similar project a bit of time and/or frustration. I’ve also added a couple of features that I hadn’t seen elsewhere (e.g., using v1.1. of the Twitter API, including images with tweets, integrating link shortening, etc…), so (hopefully!) I’m adding something new to the tweet-via-VBA body of literature.

A few preliminary notes:

• Everyone has their own way of naming variables/functions/subs, indenting, commenting code, etc…  If something I’ve done doesn’t suit you, make whatever changes you see fit.
• I wanted to avoid any third-party, non-MS dlls or add-ins. As a result, I did use the Microsoft Script Control (msscript.ocx), and a couple of .Net libraries (meaning you’ll have to have .Net Framework 1.1 or later installed on your machine).  One downside to this is that Microsoft Script Control can only be used on 32-bit versions of MS Office (2007, 2010, and 2013 should all be OK).  Either 32-bit or 64-bit Windows is fine, but the code as-is won’t run on 64-bit Office.  If you need to modify the code to run on 64-bit Office, I’ve include some links in the ‘Other Resources’ section which should enable you to do so.
• Make sure you have added references to Microsoft WinHTTP Services, Microsoft XML , and Microsoft Script Control to your project (Tools -> Add References in the VBA editor)
• You’ll find some obvious instances of code repetition (for example, the functions get_basestring, get_status_basestring, and get_media_basestring are largely identical).  As I was originally working this all out, it was easier for me to focus on the specific task at hand (i.e., plain text tweet, tweet with media, getting Twitter service status info) if I kept each function separate.  One could easily refactor the preceding functions into a single function, and there are likely additional opportunities for making the code more efficient/elegant/consistent.
• Tweeting 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.

Let’s get started!

WINDOWS API FUNCTIONS AND TYPE DECLARATIONS

First, we’ll need to define a couple of Windows API functions using the Declare keyword. We’ll also create some user-defined data types using the Type statement.

The #If VBA7 Then…#Else…#End If syntax, PtrSafe keyword, and LongPtr data type are all used to allow the code to run on 64-bit Office (see this MSDN article for more info).  Again, as mentioned above, the code as-is (which makes use of Microsoft Script Control), won’t run on 64-bit Office. But, if you decide to write/use alternatives that don’t need Microsoft Script Control (or in the seemingly unlikely event that Microsoft releases a 64-bit version of msscript.ocx), it’ll be handy to have your API function declarations 64-bit ready.

CONSTANTS

Next, we’ll define some constants. To get the necessary Twitter API keys and tokens, you’ll need to sign up with Twitter as a developer, then register an application.

In this example, I also use Google’s link shortener to shorten any links included with tweets. If you want to do the same, you’ll need to sign up as a Google developer and get an API key.

HELPER FUNCTIONS

Next, we’ll look at some of the helper functions we’ll be using when creating/sending our tweets.

The LocalTimeToUTC function converts a date value into UTC format. Thanks to Allen Wyatt for the code.

Twitter uses OAuth authorization to validate all requests. One of the required OAuth parameters is an epoch timestamp (i.e., the number of seconds elapsed since January 1, 1970 00:00:00 GMT). The get_timestamp function provides a timestamp string in the required format.

The EncodeBase64 function takes an array of bytes and converts it to a base64-encoded string. It’s used to convert binary data into an ASCII string. I thought this was a pretty slick technique; create an XML document (using the MSXML2.DOMDocument object), create a node and and assign the binary data to it. Then, simply read the same node’s Text property (MSMXL2.DOMDocument will handle the conversion automatically). Thanks to Tim Hastings for the code.

The Base64_HMACSHA1 function utilizes a couple of classes from the .Net System namespace to hash a string into a SHA-1 digest and calculate an HMAC authentication code using a secret key. (you can use the VBA CreateObject method to utilize any .Net classes that are ‘explicitly exposed as COM components’)  Thanks to HK1 (via StackOverflow) for the code.

The binary_file_to_string function takes a binary file (such as an image file) and converts it to a string.  We’ll eventually use this function when we want to include images with our tweets. Nothing too complicated here; read a binary file into a byte array, then use VBA’s built-in StrConv function to convert the byte array to a Unicode string.

Later in our code, we’ll need to generate string values that are unique (or, at least have a very high likelihood of being so). The get_GUID function provides an easy way to generate such a string (note we strip out non-alphaniumeric characters, hence the removal of the GUID’s hyphens and outer braces.) Thanks to Oorang via vbaexpress.com for the code.

The strToByteArray function takes a string and converts it to a byte array using VBA’s StrConv function.  When including an image with a tweet, we’ll use this function to prepare the body of the HTTP request.

The UrlEncode function converts non-ASCII characters into a percent-encoded format (e.g., a space becomes ‘%20’).  This function creates an MSScriptControl.ScriptControl object, and sets the language to Jscript (the MS implementation of JavaScript).  We can then execute JavaScript functions, specifically, the built-in encodeURIComponent function, which will properly URLencode text passed to it.

TWITTER FUNCTIONS

With our army of helper functions ready to pitch in, it’s time to tweet!

In order to post a status update (i.e., tweet), we will (broadly) need to:

1. Calculate a special authentication signature
2. Send a properly-constructed http request

Sounds simple enough, but calculating the signature involves a few distinct steps.  Let’s expand our to-do list:

1. Calculate a special authentication signature
   • Create a random text string that is likely to be unique
   • Calculate an epoch timestamp
   • Create a base string with which to calculate our authentication signature
   • Create a special key with which to calculate our authentication signature
   • Create authentication signature
2. Send a properly constructed http request

We’ll take each section in order.

1. Calculate a special authentication signature

To create a random, unique text string (called a nonce), we’ll use the get_GUID helper function.

To calculate an epoch timestamp, we’ll use the get_timestamp helper function.

Next, we need to create the base string which we’ll use to calculate our authentication signature.  To do so, we’ll use a function called get_basestring, which takes our nonce, timestamp, and our tweet text (hereafter called ‘status’) strings as arguments.

The get_basestring function simply constructs a base string in accordance with the OAuth spec (or, at least, Twitter’s implementation of it)

A properly-constructed base string will look something like this:

If you ever have having trouble getting your code to work (and hopefully you won’t), verify that you have generated a properly-formed base string. On your application page in your Twitter developer account,  there’s a ‘OAuth Tool’ tab which will let you manually input the various required parameters and show you what Twitter expects as a base string.  Make sure your base string matches Twitter’s expected base string exactly.

Now that we have the base string, we need the composite key we’ll use in conjunction with the base string to calculate our authentication signature.  The composite key is simply our consumer secret value joined to our access token secret value with the ‘&’ character.

Now that we have our base string and our composite key, we run both through the Base64_HMACSHA1 helper function, then URL encode the result with UrlEncode.

Your signature will look something like: FPMPdE262aH6keADBjV+x8MLkcc=

2. Send a properly constructed http request

Next, we’ll create a WinHttp.WinHttpRequest object to submit our request. We provide the method, destination URL, and set asynchronous mode to False.  For text-only tweets, the Content-Type header should be “application/x-www-form-urlencoded”

Next, we need to send the ‘Authorization’ header; like the base string, it includes various OAuth-related values. Twitter also seems to have very specific rules about the format.  For example, the OAuth spec (per my reading) seems to state that the space between comma-separated name-value pairs is optional (“Parameters are separated by a “,” character (ASCII code 44) and OPTIONAL linear whitespace per RFC2617.”), but in my testing, Twitter won’t process requests which omit this ‘optional’ whiteaapce. To ensure that the header gets constructed/formatted as Twitter expects it, we’ll use the get_header function.

Add the properly-constructed Authorization header to the http request.

Finally, we provide our tweet text as the body of the POST request, sent as a ‘status=’ name-value pair (for purposes of this example, assume our url-encoded tweet text has been previously stored in the variable strStatus).  We then wait for a response from the server before continuing.

Putting the preceding together gives us the full send_tweet function, which takes the tweet text as a string and returns a boolean indicating whether or not the tweet attempt was successful (via a ‘200’ http status response).

Here’s the preceding code in action.

If we want to include an image with our tweet, we need to make a few alterations.  While much of the OAuth preparation is the same, the base string for tweets with images gets crafted a bit differently (namely, the status text is not included). Accordingly, we’ll use a new get_media_basestring function.

The http request also gets constructed a little differently.  While the Authorization header is the same (and can be constructed using the functions previously discussed), we will have to send our request to a different URL (stored in cnsURLMedia), and the Content-Type is set to ‘multipart/form-data’.

Handling this multipart form data is the biggest difference between tweeting plain text and tweeting with an image.  Since we’ll be sending multipart data, we first need to provide a ‘delimiter’ (called a boundary) between the various parts (see this StackOverflow thread for some helpful discussions).  This boundary must basically be a specific pattern of hyphens followed by a string of characters that does not appear anywhere else in our data.  Seems the easiest way to accomplish that is to generate another, random, likely-unique text string using our get_GUID function.

We then need to construct the body of our POST request.  Everything (tweet text, image data, and image metadata) will be included within the request body. To properly construct this portion of the request, we’ll use the get_media_http_body function, which takes our tweet text, boundary ‘delimiter’, and image file path (location and name) as arguments, and outputs a text string containing everything we’ll need to send.

Output will look something like this (the blank line before the status text the and hyphens around the boundary values are significant!):

–DFA78463375D43848B36F76E30508002
Content-Disposition: form-data; name=”status”

Our lovely state flag.
–DFA78463375D43848B36F76E30508002
Content-Type: application/octet-stream
Content-Disposition: form-data; name=”media[]”; filename=”oregon_flag.png”
[binary image data converted to text goes here]
–DFA78463375D43848B36F76E30508002–

Once we have the body request, we convert it to a byte array using strToByteArray, then send it on its way.

Here’s the whole thing put together in the send_tweet_with_media function.

Here’s the preceding code in action.

GETTING TWITTER CONFIGURATION INFO

Twitter’s current system status and various limits (e.g., maximum image size, maximum number of characters that will be used in a t.co link wrapper, etc…) are constantly being updated. A full list of these limits/status indicators can be found here.

Querying some of this info before attempting to post your tweet can be very useful (for example, to make sure your image isn’t too large, or to verify your tweet will be 140 characters or less after Twitter wraps your link).  Retrieving these various values is very similar to the tweeting functions we’ve reviewed already.  There are a few notable differences:

In addition to pointing to using a different URL, the http request to the configuration URL uses GET instead of POST.  The previous base string functions both use a constant (set to “POST”) for the request method, so when retrieving status info, make sure you’re calculating the base string using “GET” as the request method (I use a different constant, cnsAPIMethodG).

The configuration request doesn’t require anything to be sent in the request body. The server response will be in JSON format. There are several VBA JSON parsing modules out there (see ‘Other Resources’), but again, I’m using the capabilities of the built-in MSScriptcontrol.ScriptControl (which, admittedly, are pretty basic and may not suffice for all scenarios). Basically, we read the JSON response from the http request into a ScriptControl object, then use the Eval method to create an object having properties based on the  JSON field names.

(full disclosure: I’m an XML guy, and have only recently started to work with JSON on a somewhat regular basis. There may be infinitely more efficient/robust ways to work with JSON in VBA, but this sufficed for my immediate purposes.  Please feel free to offer suggestions for improvement/enhancement!)

Here’s everything put together in a function called get_max_link_len which returns the current maximum t.co link wrapper length (or, alternatively, -1 in case of an error).

SHORTENING URLS

Finally, to conserve precious tweet characters, you may want to shorten your links before including them in your tweet (Twitter will automatically wrap the link itself using the t.co shortener, but what’s displayed in your tweet (and counts toward your 140 character limit) will be the longer URL you provided.  For example, if I tweet the status “My new favorite site is http://www.chocolatecoveredeverything.com!”, Twitter will represent the link as <a href=”http://t.co/xxxxxx”>http://www.chocolatecoveredeverything.com</a>; the URL that gets accessed when a user clicks on your tweet gets shortened to a t.co address, but the displayed URL text doesn’t (and counts against your 140 character limit).

So, we can run any links through a link shortener before posting. The get_short_link function can be used on any link you include in your tweet. You’ll have to sign up for an API key for  Google’s link shortener service.  Also note that the http request body has to be in JSON format.

OTHER RESOURCES

The following are some resources which I found particularly helpful as I was working on this project.  There are also links to some alternate means of parsing JSON, calculating HMACSHA1 signatures, etc…

• Using Excel as Twitter Client with OAuth – Extremely helpful walkthrough/sample code. This was really the starting point for my efforts; I can’t give Adrianus enough credit. Thanks!
• Twitter API v1.1 documentation – A little spartan at times, but better than nothing!
• Twitter API error codes – If your HTTP request is returning anything other than a ‘200’ response, check here to see what the problem might be.
• OAuth protocol – Useful for figuring out how to configure OAuth requests, but it seems as if Twitter’s implementation differs every so often from the spec. When in doubt, go with Twitter’s implementation!
• If you don’t want to use built-in objects like MSScriptControl, here are some links to code you can import/use instead:
  • The Base64 encoder/decoder by Christian d’Heureuse contains numerous useful functions.
  • Google Code has a VBA JSON parser. Michael Glaser has a refined, VB6 version, which can easily be modified to work in VBA. Glaser’s download also includes/utilizes the handy StringBuilder class by Steve McMahon, which speeds up VB string handling operations.
  • Phil Fresle has a VB SHA-256 class.
  • This thread on Amazon’s AWS Developer Forums provides some examples of things like URL encoding and HMAC signatures using the classes/code mentioned above. Somewhere along the line, I picked up a .bas module which included some of the handy functions from this thread. I don’t have any records of where I got it…my thanks (and apologies!) to the original, unknown compiler.
• (added Jun. 20, 2013) Also see my follow-up post about pulling user timelines with VBA.

Hope this proves helpful to others. Comments/questions/error reports welcome!

ADDENDA

7/4/2013 – In playing around with some other Twitter APIs (for example, users/lookup.json), I discovered that some APIs send responses using gzip compression.  Unfortunately, the WinHttp.WinHttpRequest object won’t automatically decompress gzip responses.  Instead, you’ll want to add a reference to ‘Microsoft XML, v6.0’ in the VBA IDE, and instead of using