RabbitMQ is one of the most popular messaging queue systems out there. If you’re a programmer there are client packages available for almost of the programming languages. However if you’re not a programmer your choices are limited in trying to interact with RabbitMQ.

In this blog I wanted to document some of my work in using Excel to read and write messages from RabbitMQ.

Getting Started

There are some great RabbitMQ guides. To get Excel talking to RabbitMQ I used the REST API that will be need to be installed.

For my Excel work I used Visual Basic for Applications (VBA) that is launched by Alt-F11. Next I created a Module (this is a container for VBA code), and I created two functions:

• RMQ_getqueue – to get the first value in a queue
• RMQ_exchangeWrite – to write a value to an exchange  (note: if an exchange has a binding to a queue. Writing to an exchange will be also be writing to a queue).

Read Message Queues

For my example I focused on getting the first item in a queue. Message in a queue can be cleared as they are read if the option requeue = False.

 Function RMQ_getqueue(RabbitMQ_Server As String, QueueName As String, Optional rmq_user As String, Optional rmq_pswd As String, Optional requeue As Boolean)  
 ' Create a REST API get request to a Rabbitmq server and get the first item in a queue  
   Dim theJSON As Object, http As Object, scriptControl As Object  
   Dim theurl As String, theparams As String, rqmsg As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
 ' Use a Jscript eval command to evaluate the JSON, (so you don't have to add a JSON component  
   Set scriptControl = CreateObject("MSScriptControl.ScriptControl")  
   scriptControl.Language = "JScript"  
     
   theurl = "http://" & RabbitMQ_Server & "/api/queues/%2f/" & QueueName + "/get"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   If IsMissing(requeue) Then requeue = True  
   theparams = "{""count"":1,""requeue"":""" & LCase(requeue) & """,""encoding"":""auto"",""truncate"":50000}"  
   'Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
 ' create a simple JSON string  
   thestr = Mid(http.responseText, 2, Len(http.responseText) - 2)  
   Set theJSON = scriptControl.Eval("(" & thestr & ")")  
   'Debug.Print http.responseText  
   
   RMQ_getqueue = theJSON.payload  
 End Function  

The RMQ_getqueue function creates a URL string that is passed to the http request object. The RabbitMQ response text is JSON formatted. I didn’t want to load external JSON addins so I used the ScriptControl with a Javascript eval function to parse the JSON. The payload item of the response is the first message in the queue.

To use this new function in Excel select the category “User Defined”.

Then fill in the parameters as per your requirement.

Write a Message to an Exchange

As mentioned earlier you can’t write directly to a queue with the REST API instead you write to an exchange. The exchange has a routing key that “binds” a messages to a queue.

Like the get queue function the RMQ_exchangeWrite function formats a URL with all the required parameters.

 Function RMQ_exchangeWrite(RabbitMQ_Server As String, ExchangeName As String, RouteKey As String, theMessage As String, Optional rmq_user As String, Optional rmq_pswd As String)  
 ' Create a REST API post a new message to a Rabbitmq Exchange with a Routing Key  
   Dim http As Object  
   Dim theurl As String, theparams As String  
   Set http = CreateObject("MSXML2.XMLHTTP")  
     
   theurl = "http://" & RabbitMQ_Server & "/api/exchanges/%2f/" & ExchangeName + "/publish"  
    
 ' define a parameter list this must be JSON and the synax is pickie  
   theparams = "{""properties"":{},""routing_key"":""" & RouteKey & """,""payload"":""" & theMessage & """,""payload_encoding"":""string""}"  
     
   Debug.Print theparams  
     
   http.Open "POST", theurl, False, rmq_user, rmq_pswd  
   http.setRequestHeader "Content-type", "application/xml"  
   http.send (theparams)  
   
   'Debug.Print http.responseText  
   
   RMQ_exchangeWrite = http.statusText & " " & http.responseText  
 End Function  

Like the earlier function, fill in all the parameter as per your system.

Final Comments

This was an introductory project, and there are still a lot of features and error checking that should be included.

If I had more time I would put some dialogs in to dynamically show queues, exchanges etc.