(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_basestringget_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:


POST&https%3A%2F%2Fapi.twitter.com%2F1.1%2Fstatuses%2Fupdate.json&oauth_consumer_key%3Dyourtwitterconsumerkey%26oauth_nonce%3D47f42578fa5fc14027e96eff3b093c04%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1370320828%26oauth_token%3Dyourtwitteraccesstoken%26oauth_version%3D1.0%26status%3DHello%252C%2520world%2521

 

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

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

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:
  • (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


you’ll want to use this instead

The XMLHttp object’s methods and properties are largely the same as those of WinHttp.WinHttpRequest.  You will, however, have to remove the objRest.waitForResponse  statement, as XMLHttp doesn’t have that method.  Instead, you could do something like

 

 

26 Thoughts on “Tweeting with VBA

  1. Markus on June 19, 2013 at 2:35 am said:

    Hello Greg,
    is it also possible to receive tweets into Excel using a variation of your code?
    I was using the exceltweets.com service for sending and receiving stuff, but since the
    guy running the site obviously hasn’t had time to update his code to API 1.1 yet, I need another solution.

    Thanks,
    Markus

    • greg greg on June 19, 2013 at 8:56 am said:

      Hi Markus,

      It should be relatively easy to do. Looking at Twitter’s documentation, it seems the main things to keep in mind would be:

      1) Request goes to https://api.twitter.com/1.1/statuses/user_timeline.json (this will get most recent tweets on a user’s timeline)
      2) Request uses GET method
      3) The request will have to include some parameters to identify the user, optionally limit by date range, etc…
      4) Output will be a JSON response, which would need to be parsed.

      This seems to be the relevant documentation page. Give me a day or two, and I’ll put together a sample.

  2. Markus on June 20, 2013 at 6:48 am said:

    Excellent. Thank you very much!

  3. devang on June 20, 2013 at 9:28 am said:

    Hi there indeed very helpful details your mentioned. Just wish to know i ther any way we can scadule tweets through VBA.

    • greg greg on June 20, 2013 at 9:57 pm said:

      If you’re just running off of desktop machine, I might try something like:

      Create an Access database to hold all the VBA code and a table with the tweets you want to send out on a regular basis.
      Create a VBscript that constantly runs in the background (or set up a Windows scheduled task), and every XX minutes do the following:

      • Query your table for the next ‘unsent’ tweet (use ADO from VBscript)
      • Execute the applicable VBA function (use createobject(“access.application”))
      • Update the record to indicate it’s been tweeted (again, using ADO)

      That’s just off the top of my head…others may have better approaches.

  4. Carlos Betancur on June 25, 2013 at 9:32 am said:

    Hello, tks for the interesting information. Can you give me a link with the project in Excel done? Kind regards.

    Carlos

  5. Markus on July 2, 2013 at 1:27 pm said:

    Greg,
    This worked for a while, but now I always get a 401 error when posting or receiving tweets.
    Could this have anyhtin to do with this:
    https://dev.twitter.com/discussions/16443?page=1

    • Greg Greg on July 2, 2013 at 2:13 pm said:

      Thanks for the heads up! I’ll check into it.

    • Greg Greg on July 2, 2013 at 2:54 pm said:

      Everything seems to be working on my end; I’m still able to post tweets and pull tweets from timelines.

      Do you have a ‘pre-registered’ app with Twitter (as I do in this example), where your access and secret tokens are pre-generated? If so, I don’t think the link you posted above is the cause of your 401 error; with a ‘pre-registered’ app, we’re not requesting any tokens from Twitter during the requests, so I don’t think oauth_callback and oauth_verifier are needed (I’ll do a little more research to confirm).

      Did you happen to change the order of the parameters in the base string and/or authorization header? In my testing, I found that this could cause access problems. If Twitter is expecting something like “value1=aaaa&value2=bbbb”, sending “value2=bbbb&value1=aaaa” instead seems to cause problems (even though you would think they are equivalent).

      If you like, feel free to email me the VBA code you’re using, and I’ll take a look (please delete any token/account info…I’ll substitute my own for testing). Just click on the envelope n the ‘Connect’ section at the top of the page.

  6. Dear friend,

    First thank you for the code and the patient to explain it. All we have to thank you. I have a question regarding the properly-constructed base string. In your code the message (status) appears at the end, but with the oauth tool in Twitter the message goes near the start. Why this difference?

    Regards,

    Carlos

    • Greg Greg on July 10, 2013 at 9:22 pm said:

      Great question! The only thing I can think of is that Twitter changed the parameter order between my original post and now; the ‘sample’ base string output I posted was copied/pasted directly from the Twitter OAuth tool output (which is what I based my original string order on).

      I’m going to look a little bit more into this. In the meantime, I think best practice is still to make sure your base string parameters occur in the same order as Twitter expects them; in my experience, if/when if your code throws “401” errors, parameter order mismatch is a likely reason.

  7. Fantastic work here Greg, really very helpful and well explained.

    This is the first time I’ve had to work with the Twitter API for posting tweets, and I did have one problem.

    I got a little stuck when I changed my Twitter application settings from ‘Read’ to ‘Read and write’, I wasn’t aware that I had to recreate the access token and was getting 401’s.

    I couldn’t work out what was going on until I added objRest.responseText to the debug code which then gave me a bit more information that told me the application was read only, even though I’d set it to read and write. Recreated the access token and it all worked fine.

    Posted the above in case it helps another oAuth / REST v1.1 newbie like me :)

    Thanks Greg!

  8. Hi Greg-
    I was able to get the code working to post status updates, but I keep receiving errors when I am trying to upload media. As far as I can tell, everything in my code looks correct. Are there any issues you are aware of that I should know about? Have you received any errors trying to upload media with the code in the post?
    Thanks

    • Hi John,

      I’m not aware of any errors, but if you’d like to send me your code (just use the email link in the ‘Connect’ section), I’ll be happy to take a quick look and see if I can spot anything.

  9. Well, I wished that I’d found this post about a month ago. I worked my way through the TwoBlogs post. Even wrote my own VBA HMAC-SHA1 code as a work around versus referring a DLL. Everything matches the twitter example, but the twitter API V1.1 drop XML. About to toss in the towel on this DYI project. Now, I have to learn JSON, argh!! Any suggestions for a beginner on references for learn about JSON and using it with VBA?

  10. Great post, much appreciated.

    Is this process still working for people? I seem to get a timeout error.

  11. Hi Greg – an excellent post. I got this going last month with no problems, but today after a break of 10 days I tried to send a tweet but it no longer works (401 – not authorized) – but nothing has changed during that time. Just wondering if your copy still works or has something happened at Twitter? Thanks.

  12. Hi Greg – An update. There have been no changes made, however today, 6 tweets have been sent over about a 4 hour period – 2 have been successful and 4 have failed. For the Period 9th Dec to 11 Dec, 22 Tweets sent and all failed.
    Interesting!

  13. Greg – a further update. I have not made any changes but everything is now working fine. I sent our a further 8 tweets over the past 2 hours and they were all successful. Thanks. Fred

  14. Stumbled upon this solution when SuperTweet API became blacklisted by twitter.

    I was a little concerned by the recent comments, but I got this to work perfectly on the first try.

    Thanks for explaining/sharing Greg!

  15. CONSTANTS line 13 won’t work without an = sign :)

Leave a Reply

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

Post Navigation