Excel VBA - industrialisation...Part Nine "Enumerating DDE Servers"

A DDE Server is an old technology that is still used by a number of vendors to push real time data into Excel. It has mainly been replaced by RTD but is still in use by many firms for internal applications and for some legacy applications. In some quite particular use cases DDE outperforms RTD and so it will remain in use for some time yet.

The requirement for Excel Industrialisation is to provide an operating paradigm such that a portfolio of Excel spreadsheets can be supported and managed using code rather than people. Over the course of this series we'll build up to show a complete proof-of-concept, although production ready code will not be shipped.

This post covers the case of enumerating all of the DDE servers that are included with an Excel Workbook.

Download the code from github

Create a new Excel workbook with one worksheet.

Save the new workbook as an xlsm format workbook

Enter the VBA editor

Import the module you just downloaded from github

Add a reference to Windows Script Host Object Model within the new workbook

Create a number of DDE formula links within the worksheet in the workbook you just created. If you don't have an DDE server available, download the C++ code to create one from github

Execute the "GetDDEServersUsedByActiveWorkbook" method and look in the debug window

Execute the "GetWorkbookLinksByActiveWorkbook" method and look in the debug window

Note that the C++ on github is extracted from the Microsoft website and the original code can be found here: http://support.microsoft.com/en-us/kb/238133/en-us

We now have shown in code a series of methods in "proof-of-concept" standard code that with Excel Industrialisation we can modify VBA, list all references, list DDE Servers used, list RTD Servers used (and trace using the registry to find the dll or exe server responsible), integrate with git, export VBA to files, interact via MSMQ with a central error management service and catch errors at the level of line-of-code granularity.

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.

Global Electronic Trading

Search This Blog

Excel VBA - industrialisation...Part Nine "Enumerating DDE Servers"

Comments

Post a Comment