Microsoft KB Archive/327215

= Create a RealTimeData Server for Excel 2002 or later versions of Excel with Visual C++ =

Article ID: 327215

Article Last Modified on 3/28/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Visual C++ 6.1

-



This article was previously published under Q327215



SUMMARY
Microsoft Excel 2002 and later versions of Excel provides a worksheet function that is named RealTimeData (RTD). The RealTimeData worksheet function permits you to call a Component Object Model (COM) Automation server for the purpose of retrieving data in real time. This article describes how you can use Microsoft Visual C++ to create a RealTimeData Server that you can use with the RTD function of Excel.

The VCRTDServer.exe sample contains a demonstration of a working RTD Server that is written in Visual C++. The sample runs on Microsoft Windows 98, Microsoft Windows Millennium Edition (Me), Microsoft Windows NT, Microsoft Windows 2000, Microsoft Windows XP, and on Intel platforms.



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

Download VCRTDServer.exe now

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:

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 &quot;topic&quot;, 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(&quot;VCRTDServer.RTDFunctions&quot;,,&quot;AAA&quot;, &quot;10&quot;)

=RTD(&quot;VCRTDServer.RTDFunctions&quot;,,&quot;AAA&quot;, &quot;5&quot;)

=RTD(&quot;VCRTDServer.RTDFunctions&quot;,,&quot;aaa&quot;, &quot;5&quot;)

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 &quot;0&quot; 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 &quot;0&quot; 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 &quot;RTDServerSample.xls&quot; 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.

Note When you use Microsoft Office Excel 2003 or Microsoft Excel 2002, make sure that the 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(&quot;VCRTDServer.RTDFunctions&quot;,,&quot;Hello&quot;) 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(&quot;VCRTDServer.RTDFunctions&quot;,,&quot;Hello&quot;) The initial return value matches the contents of cell A1 because this is the same &quot;topic&quot; that is used in A1.

  In cell A3, enter the following formula, and then press ENTER: =RTD(&quot;VCRTDServer.RTDFunctions&quot;,,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.

