Table of Contents
You can use the OPC DataHub to put OPC data into Excel, and to write data back to an OPC server or client.
The following sections explain how to drag and drop live OPC data into Excel, how to configure the OPC DataHub to receive data, and how to use Excel macros for sending and receiving data between Excel and the DataHub.
Before starting, to see any results you will have to ensure that you have some kind of data being fed into the OPC DataHub. If your system isn't set up for this yet, you can create a local data feed by following the steps outlined in Section 1.2, “Test with simulated data”.
There are two ways to get data into Excel from the OPC DataHub: by setting up a DDEAdvise loop to receive data automatically, or by using a DDERequest command from a macro to read data. Deciding which to use depends on your situation. We suggest you become familiar with both. For more information about DDE and these commands, please refer to Section 17.3.3, “DDE Protocol” and Appendix G, DDE Overview.
The easiest way to get data into Excel is to drag and drop point names from the DataHub Data Browser directly into the Excel spreadsheet. This automatically sets up a DDEAdvise loop between Excel and the DataHub. DDEAdvise loops update automatically so you will always see the latest data in your spreadsheet.
You can select multiple points for drag and drop by using Shift-click or Ctrl-click.
If your data displays but does not update, you might need to change your settings in Excel. Please refer to Chapter 22, Troubleshooting for more information.
When you save and close a spreadsheet connected to the OPC DataHub, and then attempt to reopen it, you may get one or more messages, depending on your security settings in Excel, or other circumstances. Here's a summary of each message, and what to do:
Sometimes, you may prefer to manually read data into your spreadsheet, rather than use a DDEAdvise loop to constantly accept new values. It may be that you intend to print reports only a couple of times a day and don't need to see every point change in between. You can have Excel read specific data points from the OPC DataHub at your request by triggering the DDERequest command from within a macro.
Using DDERequest within a macro gives you complete control over when Excel reads new point values, and lets you read several data points at one time. To run the macro, it is convenient to link it to a control button. This is explained in Add a Control Button.
' ' GetInput Macro ' Sub GetInput() mychannel = DDEInitiate("datahub", "default") Application.Worksheets("Sheet1").Activate newval = DDERequest(mychannel, "my_pointname") Sheet1.Cells(2, 3) = newval DDETerminate mychannel End Sub
Use the name of your data point from the OPC DataHub for my_pointname.
We use cell C2 in this example. If you need to use another cell, you will have to replace (2, 3) with the row and column numbers of the cell you wish to use.
Copyright © 1995-2010 by Cogent Real-Time Systems, Inc. All rights reserved.