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:
![[GRAPHIC: Download]](/wiki/index.php?title=File:Download.gif){kind=small}
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.
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("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.
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
285339 HOWTO: Create a RealTimeData Server for Excel
277617 XL2002: RTD Formula Evaluates to #N/A
285888 HOWTO: Use an Excel RTD Server with DCOM
289150 HOW TO: Set Up and Use the RTD Function in Excel 2002
277618 XL2002: Calling a Server by Using the RealTimeData (RTD) Function
284872 XL2002: #N/A Displayed in Cell with RTD Function If You Change RTD Topic
284878 PRB: RTD Servers Used with Embedded Excel Workbooks May Be Problematic
284883 RTD Server Does Not Send Update Notifications to Multiple Excel Instances
286258 INFO: Excel RTD Function Cannot Return an Array
286259 INFO: Security Settings and Excel RealTimeData Servers
287118 XL2002: Data Is Not Refreshed When RTD Server Is Started
288616 XL2002: RTD Function Generates Excel 4.0 Macro Warning in Excel 97
Additional query words: rtd VCRTDServer XL2002 XL2003 XL2007
Keywords: kbexpertiseinter kbdownload kbautomation kbfile kbhowto kbsample KB327215
