Excel VBA - industrialisation...Part Eight "Enumerating RTD Servers"

An Excel RTD Server is a hybrid push/pull mechanism that can send data into Excel in something approaching real-time. If you are not familiar with this technology, you can find instructions on how to build one on this blog at:

When engaging in Excel Industrialisation we need to keep track of external dependencies that impact on the functioning of a spreadsheet. We have already looked at Addin references, in this post we cover the case of RTD Servers. 
The Excel RTD Server has a pretty light footprint in terms of the data that is exposed to the Excel Object Model. That is something that has concerned me since 2005 (here and here) .
At a simple level the problem is that RTD is very much on the back end - there's no easy way to list all RTD Servers in a similar manner to the way we can list references.
So - what can the resourceful developer do?
  1. Download the code from github
  2. Create a new Excel workbook with one worksheet.
  3. Save the new workbook as an xlsm format workbook
  4. Enter the VBA editor
  5. Import the module you just downloaded from github
  6. Add a reference to Windows Script Host Object Model within the new workbook
  7. Create a number of RTD formula links within the worksheet in the workbook you just created. If you don't have an RTD server available, download the C# code to create one from github
  8. Execute the "GetRTDServersUsedByActiveWorkbook" method and look in the debug window
The output captured on this PC is

RTD Server Codebase located at:file:///C:/Users/John.Greenan/Code/VS2013/Projects/ExcelRTDSimple/Node2RTDclient_2/ClassLibrary2/bin/x64/Release/AlignmentSystemsRTD.DLL

This can then be used within the Excel Industrialisation process to capture the RTD Server dependencies of the spreadsheet.

As with all of the code published in this series, this is not production standard, it's proof-of-concept to illustrate what can be done and what has been done with other firms.  So there is no error handling and other features that are needed to make the user experience robust and a real value-add are missing.

See also
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"