Twitter and Excel
19.04.2009
Twitter.com is a microblogging service. The allowed maximal message length is about 140 characters. Generally you send a message via your internet browser. Ok, you’ll find many specialized tools on the net; however our most used software is Microsoft Excel. This was the beginning of our Excel Add-In.
In this article we would like to explain the base techniques we used for sending messages to Twitter. You’ll find also a sample open source project file, which can be downloaded. The sample uses parts of our code from the Add-In. At the end of our article we’ll shortly present the Excel Add-In.
Twitter interface
First, we had to check if an API - Application Programing Interface - is available for Twitter. A good documentation of the Twitter API can be found on their website.
Twitter attempts to conform to the design principles of REST - Representational State Transfer. Each resource can the be addressed with its own URI. Twitters uses the HTTP transfer protocol. So, if we would like to send some data to Twitter, we have to use the POST method, append the data within parameters to the URL and choose a format, like XML. It works similar to web form.
According to the Twitter API, we should use the function statuses/update and the variable status to send a message. This looks like:
http://twitter.com/statuses/update.xml?status=MyMessage
However, when sending this request above, the user is not authenticated yet. We have to modify our request by prepending the username and the password: :
http://username:password@twitter.com/statuses/update.xml?status=MyMessage
Also, according to the API, we have to encode some special characters like < and > before sending the message.
Send a message to Twitter with Excel
Well, how can we send a message with Excel? Microsoft Windows contains an ActiveX Library (since Internet Explorer 5.0) which provides the functionality to send HTTP-
Requests to a server. This library is also used by Ajax applications. In Excel, we can create an object instance of this library by calling CreateObject(). Here’s the code:
Public Sub mlfpTwitter()
Dim n As String
Dim p As String
Dim m As String
Dim r As String
Dim x As Object
' Bypass...
On Error Resume Next
' Read...
n = ThisWorkbook.ActiveSheet.Cells(3, 4).Value
p = ThisWorkbook.ActiveSheet.Cells(5, 4).Value
m = ThisWorkbook.ActiveSheet.Cells(7, 4).Value
' Reset...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = ""
' Check...
If Len(n) < 1 Then
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"No username."
' Exit...
Exit Sub
End If
' Check...
If Len(p) < 1 Then
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"No password."
' Exit...
Exit Sub
End If
' Check...
If Len(m) < 1 Then
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"No message."
' Exit...
Exit Sub
Else
If Len(m) > 140 Then
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"Message too long."
' Exit...
Exit Sub
End If
End If
' Encode...
m = mlfpEncode(m)
' Cursor...
Application.Cursor = xlWait
' Create...
Set x = CreateObject("Msxml2.XMLHTTP")
' Check...
If Not x Is Nothing Then
' Open...
x.Open "POST", "http://" & n & ":" & p & _
"@twitter.com/statuses/update.xml?" & _
"status=" & m, False
' Header...
x.setRequestHeader "Content-Type", _
"content=text/html; charset=UTF-8"
' Send...
x.send
' Result...
If InStr(Trim(LCase(x.responseText)), "error") > 0 Then
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"Error."
Else
' Message...
ThisWorkbook.ActiveSheet.Cells(11, 4).Value = _
"Success."
End If
End If
' Clear...
Set x = Nothing
' Cursor...
Application.Cursor = xlDefault
End Sub
We assign to the variables n, p and m the contents of the corresponding cells in the active sheet. Then we validate them first, for example we check if their length is not smaller than 1. In case of a successful check, we encode the message in UTF8. A call to mlfpEncode() encodes the message. Later more about this feature.Set x = CreateObject("Msxml2.XMLHTTP") creates an XMLHTTP object instance. If the call succeeds, we can open a connection using x.Open() and send the message. We inform the server about our content type using x.setRequestHeader(). Finally we obtain a response from the server which can be parsed for errors.
Now, some word about our encoding function mlfpMap(). This function reads a mapping table from an Excel sheet into a Collection and uses the ASCII Codes as primary key for the collection items.
Public Function mlfpMap() As Long
Dim c As Long
Dim n As Long
Dim f As String
Dim q As String
' Bypass...
On Error Resume Next
' Create...
Set mlvpMap = New Collection
' With...
With ThisWorkbook.Worksheets(3)
' Loop...
For n = 1 To 98
mlvpMap.Add "%" & _
Replace(CStr(.Cells(n + 1, 4).Value), ",", "%"), "K" & _
Asc(CStr(.Cells(n + 1, 3).Value))
Next n
End With
' Return...
mlfpMap = mlvpMap.Count
End Function
The function mlfpEncode() checks if the collection has already be created, if not it calls mlfpMap(). Then any character in the message is check for existence in the collection and eventually replaced by the encoded character.
Public Function mlfpEncode(Message As String) As String
Dim n As Long
Dim s As String
Dim t As String
' Bypass...
On Error Resume Next
' Check...
If mlvpMap Is Nothing Then
mlfpMap
End If
' Loop...
For n = 1 To Len(Message)
' Try...
t = ""
t = mlvpMap("K" & Asc(Mid(Message, n, 1)))
' Check...
If Len(t) > 0 Then
s = s & t
Else
s = s & Mid(Message, n, 1)
End If
Next n
' Return...
mlfpEncode = s
End Function
You can download the sample project from our download area.Twitter4Excel
This Excel Add-In allows you to send messages to Twitter via Excel. Your messages are stored in an Access database. The software is available in English, German and French. Please note that we primarly have developed and testet the application in Microsoft Excel 2003. Here a screenshot from the main window:
Links
» Our download area
» Maninweb.de on Twitter
» Twitter API
» Information about REST on Wikipedia
Blog
News
