The implementation here is one that uses Microsoft Message Queuing to report to a central hub (not shown here). The beauty of MSMQ is that the error message can be captured even when the computer is not connected to a network, so there is no limp implementation that only works when you are connected to a network.
So, let's look at how this works. The code is, as for all part of this series, on github
- Download the code from github
- Create a new Excel workbook.
- Save the new workbook as an xlsm format workbook
- Enter the VBA editor
- Add a reference to Microsoft Message Queue 3.0 Object Library (see screenshot 1 below)
- Add a reference to Windows Script Host Object Model (see screenshot 1 below)
- Import the modules you just downloaded from github
- Check that Microsoft Message Queue is running on the computer (see screenshot 2 below)
- In the Computer Management snap-in (see screenshot 3 below) in "Private Queues" create a queue called "ExcelIndErrorRep"
- In numberStationMQ execute "ExcelIndustrialisationErrorReportingToMSMQ"
- Have a look again at "Private Queues" in the Computer Management snap-in. You should see that there is a new message in the queue (see screenshot 4 and 5 below)
|(1) MSMQ Reference in Excel VBA IDE|
|(2) MSMQ Executable shown in Task Manager|
|(3) Computer Management - Private Queues|
|(4) New Message Added to MSMQ|
|(5) New Message Added to MSMQ|
Please note that this is not a full implementation, it's a way of showing an integration. So there is not the level of error handling that is needed in a true production setup. Also, the security level and the MSMQ implementation are not production ready. It's cleaner to use an XML format for the message sent by MSMQ rather than the concatenation of data as shown here.
Excel industrialisation...Part One "Code to write code"
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"
Excel industrialisation...Part Ten "Enumerating COMAddins"