5

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Mark Smith
  • 757
  • 2
  • 16
  • 27
  • 1
    Just so I understand, you want the Excel Spreadsheet to consume messages from RabbitMQ? – Phill Pafford Mar 31 '15 at 13:15
  • Hi Phill, that's right, ideally I'd like to be able to receive a 'string' via a MQ queue and split it into 'fields' so that I can then work with the data formatted in excel.... I'd also like to be able to do the reverse, and send the content of a number of excel cells as a string split by ; symbols, for example. Is this possible and can you give any pointers if so? Many thanks... – Mark Smith Apr 01 '15 at 20:14
  • 1
    I don't think there is anything out of the box, I would point you in the direction of using an external data source with excel https://support.office.com/en-ca/article/Create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46 possibly something that might give you some more insight https://kzhendev.wordpress.com/?s=rabbitmq – Phill Pafford Apr 02 '15 at 13:32
  • Thank you Phill, Just checked out the links, some very useful info indeed! – Mark Smith Apr 02 '15 at 19:05

1 Answers1

3

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

Community
  • 1
  • 1
T. Fabre
  • 1,517
  • 14
  • 20
  • Thank you T. I really appreciate the time and energy you have taken with your answer. I'm in the position where I'm a technical IT person, so I do actually understand what you have written (which I'm sure you'll be glad to hear given the time you have taken!) – Mark Smith Apr 02 '15 at 18:51
  • ...but I don't yet have enough practical experience coding to be confidently competent. The information you have provided is excellent and will really help me to make some decisions in terms of if and where to start. This is a personal project rather than anything related to my work so it seems it will also be a good opportunity for learning. Many thanks! (ran into the 5 minute editing lockout rule with first reply :-) – Mark Smith Apr 02 '15 at 19:01