OPC DataHub™ : Version 6.4
	Appendix E. Excel Macro Library

Appendix E. Excel Macro Library

Table of Contents

E.1. Configure Excel to receive data from the OPC DataHub (using DDEAdvise)
E.2. Write data from Excel - User initiated (using DDEPoke)
E.3. Write data from Excel - Automatically on value change (using DDEPoke)
E.4. Other Useful Macros

This set of Excel macros each work on a 100 row x 40 column table of data in Sheet1 of the worksheet, starting at cell position A1. We have tried to make these macros generic so you can easily modify them to suit your needs.

E.1. Configure Excel to receive data from the OPC DataHub (using DDEAdvise)

These macros normally need to be run only once, when first setting up a spreadsheet to receive data.

Attach array data in the OPC DataHub, one array per row, to a table of values in Excel. It is often more convenient to transmit large sets of Excel data as an array because this significantly reduces the bandwidth requirements and increases the speed of transmission. This macro sets up DDEAdvise loops from the DataHub to Excel, so that each row of the table is linked to an array point in the DataHub.

Each data point represents a row of data. This macro assumes the names are "array0001", "array0002", etc.

---------------------------------------------------
Sub register_arrays()
    Dim pname As String
    
    For i = 1 To 100
        pname = Format(i, "0000")
        pname = "=datahub|default!array" & pname
        Worksheets("Sheet1").Range(Cells(i, 1), Cells(i, 40)).FormulaArray = pname
    Next i
End Sub
---------------------------------------------------

Attach individual point data in the OPC DataHub, one point per cell, to a table of values. This macro sets up DDEAdvise loops from the DataHub to Excel, so that each cell in the table is linked to a point in the OPC DataHub.

This macro assumes that the data points are named "point0001", "point0002", etc.

---------------------------------------------------
Sub register_points()
    Dim pname As String
    
    For i = 1 To 100
        For j = 1 To 40
            pname = Format((i - 1) * 40 + j, "0000")
            pname = "=datahub|default!point" & pname
            Worksheets("Sheet1").Cells(i, j).Formula = pname
        Next j
    Next i
End Sub
---------------------------------------------------


Appendix D. Performing a Silent (Unattended) Install of the DataHub		E.2. Write data from Excel - User initiated (using DDEPoke)