9.2. Getting Data out of Excel

OPC DataHub™ : Version 6.4
	Chapter 9. OPC to Excel

There are two ways to get data out of Excel and into the OPC DataHub:

Configure a DDEAdvise loop in the DataHub that instructs Excel to send data automatically to the DataHub any time a value changes.

The data is sent immediately to the DataHub, every time the specified cell or range changes. This does not allow any kind of sanity check or safeguard on the data being sent, but in some cases it may be desirable to have Excel emit data automatically.

Each time data is sent for one point (data item), it is sent for all points. This can tie up your network if you have a large number of points. If you need to send data for a large number of cells, you can reduce this effect and reduce CPU load by sending a range that contains the cells.

Write a macro in Excel that uses the DDEPoke command to 'push' data from Excel to the DataHub. This allows you to define exactly when the data is sent to the DataHub.

9.2.1. Method 1 - Configuring `DDEAdvise` loops in the OPC DataHub

The quickest and easiest method to get data from Excel to the DataHub is to configure one or more DDEAdvise loops in the DataHub to automatically receive data from Excel, which is acting as a DDE server.

Open an Excel spreadsheet.
Choose a cell or range to receive the data. You will need to refer to your cell or range by row and column number, or by a name. For example, the cell B2 can be referred to as R2C2, or by giving it a name.
To name a cell or range, select it and enter a unique name the box just above the first column of the worksheet. Then save the worksheet.
Start the OPC DataHub if it isn't already started, and open the Properties Window (by right-clicking on the DataHub icon in the Windows system tray and selecting Properties).
Click the DDE button.

Make sure the Act as DDE client box is checked.

For best performance, ensure that a DDE server (in this case, Excel) is running when using the DataHub as a DDE client. A DDE client can consume substantial system resources trying to connect if a DDE server is not available.

Click the Add button. This opens the DDE Item Definition window where you can add Excel as a new DDE service.

Type in the following information:

Connection Name: choose a name to identify this connection. It must be unique among all DDE connections.

Service: type in Excel.

Topic: type the name of your worksheet file. This name is always exactly what is shown after the dash in the title-bar of the Excel spreadsheet. So if the title bar reads, "Microsoft Excel - Book1", then your Topic is simply Book1, but if the title bar reads Microsoft Excel - Test.xls then your Topic needs to be Test.xls.

If you want to link to a cell or range which is not on the first sheet in the workbook, you need to put the filename in square brackets, followed by the sheet name. For example, if your worksheet name is Test.xls:

Sheet in workbook	Service	Topic to enter
The first sheet	`Excel`	`Test.xls`
An unnamed sheet (e.g. Sheet2)	`Excel`	`[Test.xls]Sheet2`
A named sheet (e.g. StockData)	`Excel`	`[Test.xls]StockData`

Item Names: type in the row and column numbers or the name you entered as the cell or range name in Excel (in step 2 above).

Click the Add button. The fields DDE Item, Point Name and Data Domain are then added to the list of items associated with this DDEAdvise loop. You can continue to add points for other cells in your spreadsheet or click OK to close the dialog.

The DDE Item is associated with a point in the DataHub. You can change the Point Name and Data Domain to anything you want by double clicking on the name and typing a new name. When you click OK, the new point will be created in the DataHub.

Click OK to close the DDE Item Definition window. The new DDEAdvise loop is added to the list.
Click the Apply button for your changes to take effect. Once you have done this, you should see the DDEAdvise loop connection Status change to Connected.
Open the Data Browser by right clicking the DataHub icon in the system tray and selecting View Data
With the default data domain chosen, scroll down to see the name of the point.
In Excel, type a number into the cell or range you named in step 2, and press Enter. You should see the data update in the Data Browser.

Although this is an easy way to send data from Excel, it is not the most efficient when you have a large number of points to transmit. Whenever Excel transmits a data point using DDEAdvise, it also transmits the current value of every other point associated with any DDEAdvise loop.

Where you have a large number of cells to update, we have found it to be much more efficient to transmit the data as Excel ranges. In your DDEAdvise loop, define a range of cells that contains the data you want to transmit. Using Excel ranges will reduce the load on the computer and make it easier to configure your application.

Another option for reducing the load on the computer when transmitting a large number of points is to write an Excel macro that uses DDEPoke to transmit data on a timed basis, say once a second. Information on how to write a macro in Excel to do this is given below.

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:

This document contains macros. Enable them?: Click Enable Macros.
This workbook contains links. Update them?: Click Update. If the DataHub is already running, all the links should then update automatically. If the DataHub is not running, you will get a #REF! entry in each cell that has an advise loop established with the DataHub, and the next message (see below) will probably appear.
Remote data not accessible. Start DataHub?: Click No. At this point the best thing to do is close the worksheet, start the DataHub manually, and then reopen the worksheet. When you update the spreadsheet (see above) this time you won't get any #REF! entries. If, instead of No you click Yes at this point, the DataHub will not start, but instead generate an error message, and Excel may even crash later on.

9.2.2. Method 2 - Writing Excel macros that use the `DDEPoke` command

Writing an Excel macro is perhaps the most flexible and efficient way to send data from Excel to the OPC DataHub. By using the DDEPoke command in an Excel macro you have complete control over exactly when the data is transmitted. We will also explain how you can write an Excel macro to transmit multiple points at the same time (see Additional Pointers for more details).

In our example, we have chosen to 'add a control button' to run the macro, but you could also run your macro on a timed interval to produce an automatic update on a cycle that you control.

9.2.2.1. Create a macro

Open a spreadsheet.
From the Tools menu, select Macro, and then Macros....
In the Macro Name: field of the Macro dialog box, type the name SendOutput, and press the Create button.

In the Visual Basic text entry window that comes up, edit the macro to read as follows:

'
' SendOutput Macro
'
Sub SendOutput()
    mychannel = DDEInitiate("datahub", "default")
    Application.Worksheets("Sheet1").Activate
    Call DDEPoke(mychannel, "my_pointname", Cells(4, 3))
    DDETerminate mychannel
End Sub

Use the name of your data point from the OPC DataHub for my_pointname.

We use cell C4 in this example. If you need to use another cell, you will have to replace (4, 3) with the row and column numbers of the cell you wish to use. You can also name a range to send multiple values as an array.

Save and close the Visual Basic text entry window.

9.2.2.2. Add a Control Button

This explanation is illustrated in Section 9.1.2.2, “Add a Control Button”. We repeat the text briefly here.

Activate the Forms toolbar by clicking on the View menu and selecting Toolbars, and then Forms.
Click on the button icon, and then click in cell D4. (You can choose another cell if you'd like.) An Assign Macro window should appear.
Select SendOutput and click OK.
Change the label on the button to "Send".
Save the spreadsheet.

9.2.2.3. Send the data

Now you're ready to send the data. Open the OPC DataHub Data Browser if it is not already open, go to the default data domain, and find the name of the point.
In Excel, type a number in cell C4 (or the cell or range you assigned the macro to) and press Enter.
Click the Send button.
You should see the data update.

9.2.2.4. Additional Pointers

To reduce CPU for large amounts of data, send arrays of data using ranges instead of sending the data for each cell as a separate point.

If you are using Unicode characters in strings for DDEPoke commands, you should check the Accept non-English characters in Excel strings (slower) button in the DDE option of the Properties window.

This will cause Excel to send your strings of Unicode characters correctly, although slower than numerical data. For more information, please refer to Non-English Characters in Excel in Section 19.5, “DDE”.

The DDEInitiate and DDETerminate commands that are used to open and close DDE links between applications are also very CPU expensive. When sending variables at frequent intervals it is more efficient to open a DDE channel at the beginning of the session and close it when you are finished. Here are two suggestions:

Send multiple points within a single set of DDEInitiate and DDETerminate commands. For example:

'
' Cascade Multiple Writeback macro
'
Sub Cascade_Writeback_Many()
      mychannel = DDEInitiate("datahub", "default")
      Application.Worksheets("variables").Activate
      DDEPoke(mychannel, "pointname1", Cells(1,2))
      DDEPoke(mychannel, "pointname2", Cells(2,2))
      DDEPoke(mychannel, "pointname3", Cells(3,2))
      DDEPoke(mychannel, "pointname4", Cells(4,2))
      DDEPoke(mychannel, "pointname5", Cells(5,2))
      DDEPoke(mychannel, "pointname6", Cells(6,2))
      DDETerminate mychannel
End Sub

In this example the worksheet named variables contains six variables (pointname1 through pointname6) that we wish to send to the OPC DataHub. The DDEInitiate command opens the channel, then all six variables are sent to the DataHub before the link is closed.

Create a separate 'open' and 'close' macro for the worksheet, and place the DDEInitiate and DDETerminate commands in those macros. This will keep communication to the DataHub open for the whole time the worksheet is open. The only drawback is that your data transmission could get interrupted (see below).

If you need to send data continually from Excel to the OPC DataHub you may run into problems using DDEInitiate and DDEPoke. When you open a DDE channel using the DDEInitiate statement, and follow it with several DDEPoke statements, there is a chance that the DDE channel may fail after some time. For this reason, if you need to keep a DDE channel open for an extended period of time, we suggest that you attempt to deal with DDE errors within the macro.