I would like to subscribe to a RabbitMQ message queue from Excel 2013.
The ultimate aim is to allow data contained within a MQ message to be processed within Excel and to also allow Excel to send a formatted message via a RabbitMQ message queue. Is this possible?
The message which is sent down the message queue is comprised of 7 fields, each field is delimited by a ; symbol - however the message is sent as one string over the message queue...
e.g. "text;number;number;number;text,text,timestamp"
I would like to be able to split the raw message as above, into formatted cells in Excel 2013. Can this be done?
I have limited coding experience and I am trying to learn. Can this be done via VBA code or an Excel Add-In?
First, the bad news: there is no easy way to do this.
Now, the good news: if you really want to, you probably can do it, in at least two different ways.
Disclaimer I have not tested anything, cannot guarantee that it will work, and you (probably) will need to level up your coding skills to achieve what you want. These are just ideas that I would explore myself if I had requirements like yours, and should give you some pointers to get started. They're not necessarily good or functional.
There are various approaches to explore in order to achieve what you want:
- Write an Excel addin in .NET to actually consume/publish to the queue. This may actually be the easiest solution, but it requires both Visual Studio and a bit of .NET knowledge. Some good resources to get started:
https://msdn.microsoft.com/en-us/library/bb157876.aspx and the always-so-good https://www.rabbitmq.com/tutorials/tutorial-one-dotnet.html .
- Pros: probably the easiest solution
- Cons: you'll probably end up with an addin tied to your current requirements, and will have to update your addin when specifications change
- Leverage COM Interop in .NET to write a wrapper DLL around RabbitMQ's .NET client library. If you've never done .NET, this approach may not be appropriate, as it will involve marshalling datatypes and function pointers. Some more pointers if you choose this (difficult) path: Easiest way to make .NET DLL visible to COM?. You can then import your COM wrapper into any Excel/Office project by adding the generated DLL to your VBA project.
- Pros: you can create a generic wrapper that exposes RabbitMQ to Excel VBA and reuse it in any other Excel based project
- Cons: the learning curve will be steep.
- Use the STOMP adapter on your RabbitMQ server (if you have that option available, which may or may not be possible depending on your setup/sys admin), and write your own implementation in VBA. This is a bit hardcore, but works without any .NET voodoo, but you will need a special ActiveX control called MSWINSCK.OCX, and some event handling. StackOverflow has a question with decent answers samples here: MSWinsock.Winsock event handling in VisualBasic
- Pros: once again, you can create re-usable code (via a class that you can import in other projects)
- Cons: you have to implement a protocol, and it depends on an ActiveX control. Also requires the use of a RabbitMQ adapter, which you may not be able to use in your context.
I'm sorry that I can't provide actual code showing how you'd go at any of this, but as you've probably guessed by now, all solutions imply a fair amount of work. So at least, you know where to look...