Create a RealTimeData Server for Excel 2002 or later versions with Visual C++ (327215)

MORE INFORMATION

The following file is available for download from the Microsoft Download Center:

Release Date: October 21, 2002

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The VCRTDServer.exe file contains the following files:

File name	Size
RTDServer.dsw	541 bytes
RTDServer.dsp	4909 bytes
RTDServerDLL.cpp	11168 bytes
RTDServerDLL.h	1265 bytes
RTDServerImpl.cpp	19560 bytes
RTDServerImpl.h	2701 bytes
RTDDataThread.cpp	3051 bytes
RTDDataThread.h	742 bytes
TList.h	6880 bytes
RTDServer.def	318 bytes
IRTDServer.idl	1937 bytes
Resource.h	455 bytes
Script.rc	1616 bytes
RTDServerSample.xls	30208 bytes

Structure of a RealTimeData Server

The RTD worksheet function has the following syntax:

=RTD(ProgID,Server,String1,[String2],...)

The first argument, ProgID, represents the Programmatic Identifier (ProgID) of the RealTimeData server. The Server argument indicates the name of the computer on which the RealTimeData server is run. This argument can be a null string or can be omitted if the RealTimeData server is run locally. The remaining arguments represent parameters to send to the RealTimeData server. Each unique combination of these parameters represents one "topic", to which Excel assigns a unique TopicID. Parameters are case-sensitive. The following examples illustrate calls to the RTD server that result in three separate TopicIDs:

=RTD("VCRTDServer.RTDFunctions",,"AAA", "10")

=RTD("VCRTDServer.RTDFunctions",,"AAA", "5")

=RTD("VCRTDServer.RTDFunctions",,"aaa", "5")

For a COM Automation Server to be a RealTimeData Server that you can use with the RTD function of Excel, the COM Automation Server must implement the IRTDServer interface. The server must implement all the methods of IRTDServer:

ServerStart

STDMETHODIMP ServerStart(IRTDUpdateEvent *CallbackObject, long *pfRes)

The ServerStart method is called immediately after a real-time data server is instantiated. The first parameter is an IRTDUpdateEvent interface pointer that the RTDServer calls to indicate that new data is available. The second parameter is a pointer that must be set to a positive value to indicate success. A "0" or negative value indicates failure.

ServerTerminate

STDMETHODIMP ServerTerminate(void)

ServerTerminate ends the connection to the real-time data server.

ConnectData

STDMETHODIMP ConnectData(long TopicID, SAFEARRAY **Strings, VARIANT_BOOL *GetNewValues, VARIANT *pvarOut)

ConnectData adds new topics from a real-time data server. The ConnectData method is called when an Excel file is opened that contains real-time data functions or when a user types in a new formula that contains the RTD function.

The TopicID parameter contains the value that is assigned by Excel to identify the topic. The Strings is a safe array that contains the strings in the RTD formula that identifies the data that is to be served. The GetNewValues parameter is a BOOLEAN that indicates whether to retrieve the new values or not. Finally, the pvarOut must be set to the initial value of the topic.

DisconnectData

STDMETHODIMP DisconnectData( long TopicID)

DisconnectData notifies the RTD server application that a topic is no longer in use. The TopicID identifies the topic that is no longer in use.

HeartBeat

STDMETHODIMP Heartbeat(long *pfRes)

HeartBeat is called by Excel if a particular interval has elapsed since the last time Excel was notified of updates from the RealTimeData server. HeartBeat permits Excel to determine if the real-time data server is still active. The pfRes parameter must be filled with "0" or a negative number to indicate failure. A positive number indicates success.

RefreshData

STDMETHODIMP RefreshData( long *TopicCount, SAFEARRAY **parrayOut)

This method is called by Microsoft Excel to get new data. This method call only occurs after the method is notified by the real-time data server that there is new data.

The TopicCount must be filled with the count of topics in the safearray. The parrayOut parameter must be filled with a two-dimensional safearray. The first dimension contains the list of topic IDs. The second dimension contains the values of those topics.

About the Sample RTD Server

The sample RTD server is a simple example of how an RTD Server that is written in Visual C++ may work. RTDServerDLL.cpp/.h contains the Class Factory and self-registration code for the RTD Server. RTDServerImpl.cpp/.h contains the implementation of the actual RTD Server. To make the IDispatch interface implementation easier, the RTD server uses the type information defined in IRTDServer.idl to delegate calls. This type information is a copy of the IRTDServer interfaces that are defined in the type library of ExcelXP. Because, in many cases, data for an RTD Server arrives asynchronously, RTDDataThread.cpp/.h defines a second thread that notifies Excel when new data is available.

Use the RTD Server in Excel

The "RTDServerSample.xls" Excel workbook that is included in the download contains RTD formulas that use the sample server. To use the sample workbook, you can rebuild and register the sample RTD server (by using Regsvr32.exe), and then open the Excel workbook. Make sure that Macro security is set to Medium or to Low, or the RTD server cannot run.

If you prefer to create your own Excel workbook that uses the sample RTD server, follow these steps:

Start a new workbook in Microsoft Excel.
In cell A1, enter the following formula, and then press ENTER:
```
=RTD("VCRTDServer.RTDFunctions",,"Hello")
					
```
The return value is made up of the TopicID that is assigned by Excel and the current System Time.
In cell A2, enter the following formula, and then press ENTER:
```
=RTD("VCRTDServer.RTDFunctions",,"Hello")
					
```
The initial return value matches the contents of cell A1 because this is the same "topic" that is used in A1.
In cell A3, enter the following formula, and then press ENTER:
```
=RTD("VCRTDServer.RTDFunctions",,ADDRESS(ROW(), COLUMN()))
					
```
This formula uses the address of the cell to construct a unique topic string. You can copy and then paste this formula to as many cells as you want to see the unique TopicIDs assigned by Excel.

NOTE: You must know the following information about the DisconnectData Event:

While Excel is a subscriber to your RTD server, Excel triggers the DisconnectData event when it no longer needs a topic (for example, when you delete or you clear an RTD formula in a cell). However, Excel does not call DisconnectData on each topic for the RTD server when the workbook is closed or when Excel quits. Instead, Excel calls only ServerTerminate. When you are creating an RTD server, you must code for any clean-up of topics or other objects when the ServerTerminate event fires.