Getting Real Time Data Into Excel

Devices like Electrical Drive Systems and Programmable Logic Controllers (PLCs) are able to provide real time data interfaces for 3rd party clients. Excel can often be that client. Getting real time data into Excel allows the use of the powerful analysis features of Excel to be used in real time.

There are a few options for getting data into Excel. In the past, serial ports were the most common way of connecting a computer to a device for data analysis. Many devices required their own drivers for the data to be pulled into Windows applications. While custom applications for pulling out data still exist, open standards are now more commonly used for accessing real time data from devices.

Two common ways of accessing data these days are NetDDE and OPC. NetDDE or Network Dynamic Data Exchange is used to transfer data across networks. An Excel can function as a NetDDE client and connect to a NetDDE server. Keying in the formula

=’\\DeviceName\NDDE$’|sharename!itemName

will establish a NetDDE connection and bring in data for the specified item.

NetDDE is often flaky and tends to hang. Many devices now act as OPC servers and allow OPC clients to connect to them. Excel can be used as an OPC client. Installing the OPC automation DLL, typically provided by the device manufacturer, enables VBA scripts to be written in Excel to access data using OPC in real time. While programming from scratch takes a fair amount of expertise, examples of VBA code for OPC connections, reads and writes can be readily found by a searching the net.