Write Your Own Twitter Client using Microsoft Excel

Posted on February 5th, 2009 in excel apps , Featured , Learn Excel - 92 comments

Update: As of Sept 3rd, 2010, this code or approach no longer works. Twitter has taken themselves too seriously to disable a working protocol in lieu of complicated oAuth. I am keeping the post intact for amusement and knowledge of xmlhttp works.

Twitter is fun. You can stay in touch with your friends and followers, 140 characters at a time.

Out of curiosity I opened the twitter api documentation today to see if it is possible to build an excel based desktop client to post messages to twitter.

It turns out that, doing this using twitter api is relatively simple. So I went ahead and built an excel sheet using which you can post messages to your twitter account. Interested? Read on

Create a new MS Excel file and make a data entry form like this

You can insert the button using form controls. Go to menu > view > toolbars  and select “forms”.

Now select the button control and draw it on your sheet.

Excel will show a dailog asking which macro to run when that button is pressed. Enter the name as “tweetThis” or something cool.

Make sure you adjust the text color of password field same as background. That way your colleague (say hello to her from PHD) cannot look over your shoulder and know your twitter password.

PS: if you are using Excel 2007, form controls will be available in “Developer” tab of ribbon. If you don’t see developer tab, you must turn it on from “excel options”. Press office button and select excel options. I guess the option will be in “Advanced” area.

Create named ranges for your data

(this step is not mandatory)

We need just 3 fields of data to post a message to your twitter account. User name, Password and Message . Select each of the 3 cells and create named ranges for them. Name them something meaningful like “tusername”, “tpasswd” and “tmessage”. To create a name for selected cell, just press menu > insert > name > define (press the create name button in excel 2007)

Now, the fun part, writing macro code to post your message to twitter

The actual code is no geek stuff. We will use XMLHTTP object to do our work.

What is xmlhttp? it is the same object your browser uses to dynamically receive and send data from websites. It is the stuff behind all those cool AJAX powered sites.

First, right click on the “tweet” button you have created and select assign macro option. In the window it would show your macroname (tweetThis). Select it and click on edit button. This will open VBA Editor. Don’t freak out. :)

Our code needs to do the following stuff:

  1. Create an xmlhttp object
  2. Use twitter API’s post method and post the message
  3. Get the status and display it in debug window (just so that we would know if something went wrong)
  4. Close the xmlhttp object

I have written the below code, but I am sure you can write your own looking at how simple it is.


Sub tweetThis()

    Dim xml, tUsername, tPassword, tStatus, tResult
    Set xml = CreateObject("MSXML2.XMLHTTP")

    'get the username entered by you in named range tusername
    tUsername = Range("tusername")
    'get the password entered by you in named range tpasswd
    tPassword = Range("tpasswd")
    'get the message entered by you in named range tmessage
    tStatus = Range("tmessage") 

    xml.Open "POST", "http://" & tUsername & ":" & tPassword & "@twitter.com/statuses/update.xml?status="
& tStatus, False
    xml.setRequestHeader "Content-Type", "content=text/html; charset=iso-8859-1"
    xml.Send

    tResult = xml.responsetext 'you can view Twitter’s response in debug window
    Debug.Print tResult

    Set xml = Nothing
End Sub

Finally, enter your user name and password and a test message and test your code

If everything is fine, you should be able to tweet from that spreadsheet. Here is a sample message if you don’t know what to tweet:

Wow, http://chandoo.org/wp just posted a cool new way to make your own twitter client

You can easily extend this code to check someones status message, DM or message someone or analyze twitter stream. For processing twitter data you can use DOM parser objects from VBA.

Download Twitter from Excel Application and Play around with the code

Go ahead and download the twitter from excel app and learn by opening the code etc.

That is all. Do you like this ?

Cool, then please Tweet About This!, add this to your delicious bookmarks, stumble this page.

If you are new to PHD, I encourage you to sign up for our e-mail updates or add this blog to your reading list because we post cool and fun excel and charting stuff almost every weekday.

Check out other cool excel and spreadsheet stuff

Track your investments using Excel

Build an expense tracker using google docs

Make your own Amazon Deal Tracker in Excel

Findout when that iPod is available for cheap. Learn xpath and track almost everything

To Do list in Excel

Play Deal or No Deal using this Excel game

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

92 Responses to “Write Your Own Twitter Client using Microsoft Excel”

  1. JP says:

    How about a UI version?

  2. Gordon says:

    Although I signed up for Twitter a while ago and linked my Twitter account to Facebook so that my status would be in sync, I’ve never found a way of sending tweets that is convenient enough for me to do more than just the occasional update.

    Thanks to your post, I now have a commandbar button with a neat Twitter logo that loads a userform that allows me to tweet from the application I spend about 9 hours a day staring at – no visiting a site specifically to tweet, running a separate app or dealing with an annoying mobile interface.

    If this doesn’t allow me to get more into Twitter then I guess nothing will!

  3. Kunal Gautam says:

    Awesome, Will check it :) Thanks for sharing

  4. Good one dude.
    Will try.
    -Nikhil

  5. Gordon says:

    Bit obvious this really, but I found you have to do a replace on any character in your tweet that can get mixed up in the XML, e.g. “&”.

    Replacing these characters with their hex encoded equivalents allows the tweet to go through.

  6. Chandoo says:

    @JP.. you mean an add-in.. well, I wanted to explore xmlhttp and this is my first time. So I tried to keep it simple, but adding a ui or userform is straight forward and simple

    @Gordon: neat stuff… may be you can share this with us so that JP can download and play with it :), btw, you could use some version of urlencode () to achieve clean up the message content. I didn’t explore all that, but a very good suggestion.

    @Kunal: welcome to PHD and thanks.

    @Nikhil: thanks dude…

  7. […] Self styled Excel Guru, Chandoo tries to build an Excel based desktop client for twitter.Voila, it works. […]

  8. @curtisincalgary says:

    This was awesome. Thanks.

  9. JP says:

    One more thing, I’m not a fan of Twitter at all, but isn’t there some kind of limit to the number of words or characters you can post at one time? If so, you might want to add a check that makes sure Range(“tmessage”) isn’t too long.

    HTH

  10. Chandoo says:

    @curtisincalgary: thank you :)

    JP: the download has a character count to keep the message to 140 chars. Also twitter api is smart enough to take just 140 chars if you are sending messages. so there is little to worry.

  11. JP says:

    Here it is Chandoo, the UI version of your Twitter code I’ve been working on:

    http://www.codeforexcelandoutlook.com/blog/2009/02/excel-to-twitter/

    Take care,
    JP

  12. Sebastian says:

    Wauw, tnx for sharing. I am going to try this one! :p

  13. […] at Code for outlook and excel blog has taken one look at my excel from twitter workbook and immediately felt that, an add-in is a cool way to extend it. Being the compulsive VBA […]

  14. Brijesh says:

    Question: Can we tweak this app so that it can archive the tweets??

    • Chandoo says:

      @Brijesh: yes, yes we can. Twitter sends the response back in xml format. use the DOM object to chomp the xml to what you want and you have a tweet-archiver-spreadsheet ready.

  15. Brijesh says:

    Thanks. I guess, that is something for weekend.

    Taking this a step further: How can I archive the tweets of few people whose ideas I really look forward to? Can this be achieved using DOM?

    • Chandoo says:

      @Brijesh: yeah, DOM is just a framework to parse the XML (or other data that follows the document object model, like web pages etc). If you are looking for a tweet archiver for your fav people (or #topics), here is how you can do it,

      First follow the fav people
      now ask excel to go to your following page and capture all updates
      parse the updates using dom and place them in a table on the spreadsheet
      repeat often

  16. […] was inspired by Chandoo’s post Write Your Own Twitter Client using Microsoft Excel to take his code and create a userform version. Actually, I was waiting for someone else to do it, […]

  17. JP says:

    One of my planned updates for Post To Twitter is to allow you to read others’ tweets. I say “planned” because I’m trying to figure out how to read the XML response, turn it into an XML document, and then parse that. That, and a lot of candle lighting and praying.

    • Chandoo says:

      @JP: and coffee too :)

      on a serious note, I found twitter api to be much more easier to work with than some other stuff out there. Also, one more way to work around the parsing problem could be to pass the xml through some services that spit out csv / txt (like yahoo pipes, which incidentally has the ability to take dynamic xml / rss urls as input and runs them on the pipe to return the output in the format your prefer, like JSON)

  18. […] Tweet from Excel – Write your own twitter client using VBA and Twitter API [17 […]

  19. […] How to write your own Twitter client in Excel (Userform version is here) […]

  20. Brijesh says:

    Hi,

    You know what I would really really like (apart from archiving) is to Schedule the tweets. That I guess, is relatively easier and would be fabulous.

    @brijeshj

  21. Paul says:

    Yeah – this would be awesome if someone can figure out a way to schedule the Tweets based on data already in an excel spreadsheet.

    I work for a cable network and we are just starting to use Twitter to give program updates to our fans. Every week I get the program guide with show name, time, episode description and I would add a shortened link field to this for the show page. If I could have tweets scheduled 30 minutes before the show airs that would be epic.

    If anyone wants to experiment with trying this please e-mail me: mr dot escandon at gmail dot com

  22. Gordon says:

    @Paul

    Getting a list of users to send a message to from a range in Excel is not a problem, the issue is that Twitter has a limits on both the minimum time between tweets (5 minutes I think) and maximum tweets per hour (140?), making Twitter all but unusable for a notification service like you are proposing.

  23. Miguel says:

    Hi Chandoo,
    I’m also trying to develop a vba form where getting some values to enter into the spreadsheet. The point is I’ve got to recover a value from an entryfield ( with no format. I don’t know how formatting textfilelds in vba forms ) and set the value of the cell in the spreadsheet.
    The decimal character is ‘,’. When I do not enter decimals, the value is captured OK, but when I enter decimals, the cell loses its format.

    Do you know how can I format the value of the entryfield ?

    Thanks for your excellent blog.

  24. tudza says:

    I downloaded the .xls file and tried it. I get this mashed up Windows Live content stuff. What does that mean?

  25. Chandoo says:

    @Tudza: I think you have downloaded the file by right clicking on the url. You need to actually visit the url and download the excel from there. Let me know if you face some difficulty….

  26. Alex Fung says:

    Similarly, I developed a full-featured twitter client on Excel called “TweetSheet”. You can take a look!

    http://iphone-chieftain.blogspot.com/2009/04/tweetsheet-10-released.html

  27. thats great that you are talking about the twitter api,a good example of searching with the twitter api is on twiogle.com because you can search on twitter and google at the same time.

  28. Eddy says:

    I have tried sending messages through Excel and it works great. I also wrote some code to schedule messages and all works like a charm. It seems Twitter has problems with %-sign. If I send a message with the %, ir goe swrong and doesnt want to go. Do you know how to solve this?
    Thanks

  29. Alex Fung says:

    Make sure that you have set the correct content-type as below:
    xml.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”

  30. Chandoo says:

    @Eddy: it could be because % is a special character in webforms. As suggested by Alex, you can try changing the encoding type.

  31. Excel-lent post! Thanks.

  32. Eddy says:

    I figured out the % problem, which has to do with the way XML handles the message. If you add 25, the message will be correct in Twitter. So %25 on the Excel sheet looks like % in Twitter.

  33. Eddy says:

    I figured out the % problem, which is caused by the way the XML message is handled. If you use %25 in your Excel message, it will look like % in Twitter.

  34. Craig says:

    Hi! I downloaded tweetsheet and am getting a runtime error 800c0005 (after press tweet button, of course) while using excel 2007 on vista… any ideas? Thanks in advance!

  35. cinocefalo says:

    What is Twitter and How Can I Use It?

  36. Chandoo says:

    @Craig… sorry, I didnt see your comment earlier. Is the problem resolved? May be the firewall or proxy server is preventing you to access twitter… ? This error occurs when firewalls block the msxml object which we are using in this excel file.

    @Cinocefalo… Twitter is next god.. :P, I am kidding, it is just another social media tool that you can use to say what you are upto in less than 140 chars at a time… try it, you might find a cool use for it.

  37. Lanier says:

    Your wonderful idea but running into some problems. The update fails and I get a debug at xml.send

    xml.Open “POST”, “http://” & tUsername & “:” & tPassword & “@twitter.com/statuses/update.xml?status=” & tStatus, False
    xml.setRequestHeader “Content-Type”, “content=text/html; charset=iso-8859-1″
    >>>>xml.Send

  38. Chandoo says:

    @Lanier… the most likely scenario for this error is when your firewall blocks outgoing communications from xmlhttp object. I had the same problem when I tried this from office comp (where twitter is blocked). But when I try it from home, it worked perfectly. Can you try this in some other comp and let me know if the error persists.

  39. Lanier says:

    Chandoo,

    Still the same problem or home network or company. I download another excel that works fine for home and company network. TweetSheet2.0.1.xls is the name of the excel sheet that works. But is much more complicated

  40. Alex Fung says:

    I use the following module to send tweets in Excel in TweetSheet.

    Sub tweetMessage(sStatus As String, sReplyId As String, sTwitterID As String, sTwitterPwd As String)

    Dim xml As Msxml2.XMLHTTP
    Dim sXMLMsg As String

    Set xml = New Msxml2.XMLHTTP

    If Len(sReplyId) > 0 Then
    sXMLMsg = “in_reply_to_status_id=” & Trim(sReplyId) & “&status=” & sStatus
    Else
    sXMLMsg = “status=” & sStatus
    End If

    xml.Open “POST”, “http://@twitter.com/statuses/update.xml”, False, sTwitterID, sTwitterPwd
    xml.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
    Call xml.send(sXMLMsg)

    Set xml = Nothing

    End Sub

  41. Steven Gill says:

    When I hit the tweet button I get an error message. “Compile error: Syntax error” The macro formula is brought up behind the error and debug program is behind that. How do I fix this Please help.

  42. Steven Gill says:

    I tried pasting the macro above, now I get an error msg stating it may not be available. Now the tweetthis is gne and I cantget it back.

  43. Lanier says:

    Alex ,

    I am getting arugment not optional with your code. Sorry for being dense. But how do you program the excel sheet to accecpt your vaules.

    Chandoo,
    Still no luck with you code.

    Lanier

  44. Chandoo says:

    @Steven: You might have pasted same sub twice. Can you close the workbook and try to work with the downloadable workbook. The link is in the post.

    @Lanier: Over the last few days, twitter has been going through tremendous amounts of downtime. May be that is why the code is bouncing…. ?

  45. Alex Fung says:

    Just pass the empty string “” to the optional argument, such as sReplyId.

  46. Lanier says:

    Chandoo,

    No luck. Still the same. Crashes at xml.send

  47. Saemon says:

    Anyone know if this can be done using an oauth token instead of relying on inputting a password?

  48. Rolan says:

    Is it possible to make this work using Excel 2003? Thanks!

  49. Rolan says:

    I haven’t made it worked in Excel 2003 yet, but I was able to make a prototype in MS Access 2007 which opens the possibility of creating a database of tweets to be posted according to a pre-determined time.

  50. Rolan says:

    I like your articles Chandoo. I just wana asked if its ok for you to provide a link to my site http://www.mymsoffice.net and I will also link to your site. Thanks!

  51. Chandoo says:

    @Rolan.. that is a cool idea, using db and timing the tweets.

    btw, coming to your request, I do not link to other sites on my blog just like that. I provide links to useful excel, charting and story-telling articles from time to time on posts. I like the content on mymsoffice.net and have subscribed to it on my reader. I will share interesting articles to my readers through that and may be in an upcoming “excel links” post.

  52. sean says:

    That was so cool. You know how it says “less than 5 seconds ago from API”? Is there a way to make the “API” part read “Excel”?

  53. Gordon says:

    @sean

    No, you need to get a developer API key to have your application’s name appears here. This is a process that requires you to submit your application for testing by Twitter.

  54. This is very inusual, something i didn’t expected from excel and so cool. I’ve been vb programer for a year and i didn’t know that u can make XMLHTTPRequest objects from it. I’m gonna try when i have the chance. This makes me think that my web page is not that cool anymore thanks to you

    chepe263
    Quitter Admin.

  55. Ekendra says:

    Wow, simply the best way to tweet! Just downloaded your file and learning to modify the codes to use privately….
    if you have developed variations please let us know,
    thanks

  56. […]     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. […]

  57. […] van de honderden alternatieve websites, programma’s, mobiele telefoon applicaties en zelfs Excel. Deze kunnen bestaan, omdat Twitter het heel makkelijk heeft gemaakt voor anderen om met hun […]

  58. […] How To Write Your Own Twitter Client using Microsoft Excel […]

  59. Darren says:

    Was quite excited about this but this seems to suggest I won’t have much luck from work…

    407 Proxy Authentication Required

    Proxy Authentication Required

    Unable to complete request:
    Access denied due to authentication failure.

  60. […] How To Write Your Own Twitter Client using Microsoft Excel […]

  61. […] How To Write Your Own Twitter Client using Microsoft Excel […]

  62. mt says:

    To complement this cool macro, I wrote a function for fetching tweets, so now you don’t need to visit Twitter at all :)
    http://mikaelspage.blogspot.com/2010/05/fetching-tweets-to-excelwordppt.html

  63. […] ????? ?????? ?? ?? ??? ?????? ?? ??????? ???? ??? ??????? ? ??? ????? ?? ??? ????? ??? ??? ??  ????? ???? : ???? […]

  64. Until it has OAuth support, for those wanting to use this code after June 2010, it should be easy to modifiy it to use http://www.supertweet.net/api

  65. Flavio says:

    Fantastic !! Awesome !! Thank you very much for sharing.

  66. […] How To Write Your Own Twitter Client using Microsoft Excel […]

  67. LK says:

    Does anyone have updated code with use of OAuth?

  68. Chandoo says:

    @LK… nah.. But twitter is still supporting REST API. I tried to look at the oAuth API, but it seemed like too much hassle for a fun project like this. Nevertheless, I would be curious to know if anyone implemented it in vba.

  69. […] How To Write Your Own Twitter Client using Microsoft Excel […]

  70. Kalyan Verma says:

    @Chandoo No more basic authentication for Twitter. You have no choice but use OAuth. I think the hassle is inevitable now. Any ideas?

  71. Jay Sperl says:

    Chandoo:

    I found your code a year ago and used for real-time football game updates all fall while keeping stats for a high school football team. It worked really well. I am preparing for another football season, first game tomorrow, and testing my setup. Last Monday the tweetThis code worked just fine. Today it does not. Did Twitter change something that I need to adjust the code for?

    Thanks, JLS

  72. Flavio says:

    The macro was working fine until yestarday. But today it seems twitter.com has changed something and this macro is NOT working anymore…. Which is too sad. How can we work around?

  73. Chandoo says:

    @all.. I am sorry, but the code no longer works. Twitter has taken a bold step of disabling RESTful API and chosen to use oAuth. Don’t ask me why. I guess they didnt want to care about all those developers who wrote code using earlier authorization mechanism.

    Please note that I am learning about oAuth and I may post an updated macro sometime in future. Meanwhile, if you cracked the oauth nut, please share your insights / code using comments.

  74. Jon H. says:

    You should try using an OAuth gateway, like Simple Auth Twitter (http://simpleauthtwitter.heroku.com/) or SuperTweet (http://www.supertweet.net/).

    Personally, my client of choice until the change was TweetSheet (http://iphone-chieftain.blogspot.com), but it seems like the macros in there are password-protected :-(

  75. Go to http://simpleauthtwitter.heroku.com and request a authorization key and insert it on the following code:

    Sub tweetThis()

    Dim xml, tStatus, tResult
    Set xml = CreateObject(“MSXML2.XMLHTTP”)

    ‘get the message entered by you in named range tmessage
    tStatus = Range(“tmessage”)

    xml.Open “POST”, “http://simpleauthtwitter.heroku.com/tweet?api_key=YOURKEY&status=your-messageSimple”
    & tStatus, False
    xml.setRequestHeader “Content-Type”, “content=text/html; charset=iso-8859-1″
    xml.Send

    tResult = xml.responsetext ‘you can view Twitter’s response in debug window
    Debug.Print tResult

     Set xml = Nothing
    End Sub

    Best regards,
    Marcelo Araujo

  76. Flavio says:

    Congratulations and thank you, Marcelo.
    It is working fine. And thank you for sharing.

  77. […] Access through a web query. It looks like I was a little late for that — Chandoo documented how to use Excel as a Twitter client, but then reportd that Twitter made a change that means that approach no longer works as of […]

  78. Mike says:

    Yea – no to simpleauthtwitter. I do not pass my userid/password/api keys, etc through any gateway. If it cant be written in pure code that stays on my box then I wont use it. Has anyone found a way to write pure code, without the use of someone else’s library (a black box that I do not trust) where the control of the code is in the hands of us, the developers?

  79. Dustin says:

    I tried your code Marcelo, and I don’t get anything to show up in my twitter feed. any ideas? I am using 2003 btw.

  80. Richard says:

    Wow this is cool dude. Hey I have a tough task of having to visit twitter daily and import daily trends into excel and track them for my boss. Is this something that you can do for me like the excel tool that automatically gets the daily, weekly or monthly trends into excel. So i dont have to manually do the work.

  81. Bruce says:

    Heres a vba.rest library, including a twitter entry.

    http://ramblings.mcpher.com/Home/excelquirks/json/rest

    Direct import to excel of the fields you need with no coding required.
    Bruce.

  82. […] 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 […]

  83. […] GetShortURL(url As String, index As Integer) As String ' based on http://chandoo.org/wp/2009/02/05/twitter-from-excel/ ' tinyurl API creation link from: ' […]

  84. Julian says:

    Hi, is this code compatible with the new API 1.1? I am under the impression that now it is required to authenticate using oAuth method.
    The method of using an oAuth proxy sounds good if you need to share your Excel spreadsheet with other users.

  85. Prince Thomas says:

    I tried this file, but getting an error message
    “Run-time error ‘-2147012891 (80072ee5)':
    Method ‘open’ of object ‘IServerXMLHTTPRequest2′ failed

    please help me how to resolve it.

  86. […] are tons of resources including an Excel Twitter client. So if you are the Excel guru or want to be check out this resource. For a complete list of […]

Leave a Reply