Here is an example of a worksheet that sends data points to the OPC DataHub both ways: to the DataHub acting as a DDE client (using an advise loop), and by using macros (with DDEPoke and DDERequest). The worksheet also receives the same data back from the DataHub in both ways. For more information about DDE and these commands, please refer to Appendix G, DDE Overview.
This worksheet, Excel-DataHubTest.xls, is included in your OPC DataHub distribution.
Explanation of the pertinent manual data entry cells:
B3 The cell is named: Ctest1. When you use named cells to send data to the OPC DataHub that is acting as a DDE client, each time you update any cell, all the other cells set up this way also send their data to the OPC DataHub.
C4 Has a macro associated with it, shown below. When you use a macro to send data to the OPC DataHub, each time you send updated data, only that cell's data gets sent to the OPC DataHub.
The macro is:
Sub SendOutput() mychannel = DDEInitiate("datahub", "default") Application.Worksheets("Sheet1").Activate Call DDEPoke(mychannel, "Ctest2", Cells(4, 3)) DDETerminate mychannel End Sub
C5 The button activates the macro associated with cell C4. The formula is: Excel-DataHubTest.xls!SendOutput
B6 The cell formula is: =datahub|default!Ctest1
C6 The cell formula is: =datahub|default!Ctest2
B7 and C7 Have a macro associated with them, shown below. When you use a macro to receive data from the OPC DataHub, the data is only updated once.
The macro is:
Sub GetInput() mychannel = DDEInitiate("datahub", "default") Application.Worksheets("Sheet1").Activate newval1 = DDERequest(mychannel, "Ctest1") newval2 = DDERequest(mychannel, "Ctest2") Sheet1.Cells(7, 2) = newval1 Sheet1.Cells(7, 3) = newval2 DDETerminate mychannel End Sub
B8 and C8 The button activates the macro associated with cells B7 and C7. The formula is: Excel-DataHubTest.xls!GetInput.
The Data from DataSim part of the spreadsheet receives the data from the four DataSim points (SIN, SQR, TRI, and RMP), and displays their values in a chart.
The OPC DataHub DDE properties for this example are set as follows:
Act as DDE client to these services and topics: Checked.
Connection Name: ExcelTest
DDE Item: Ctest1
Point Name: Ctest1
Data Domain: default
Act as DDE server listening on these services: Checked.
DDE Service Name: datahub
Copyright © 1995-2010 by Cogent Real-Time Systems, Inc. All rights reserved.