E.2. Write data from Excel - User initiated (using DDEPoke)

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

These macros are useful for writing data out from Excel on demand. In other words, the user decides when to write the data, and does so by running one of these macros (usually from an assigned button click).

Transmit array data, one array per row, to points in the OPC DataHub. Triggering this macro writes all the data from the table in Excel to the OPC DataHub. The macro writes each row of the table as an array point in the DataHub. All rows of the table get transmitted, one after another.

This macro assumes that the data points are named "array0001", "array0002", etc.

---------------------------------------------------
Sub transmit_arrays()
    Dim chan As Integer
    Dim pname As String

    chan = DDEInitiate("datahub", "default")
    For i = 1 To 100
        pname = Format(i, "0000")
        pname = "array" & pname
        DDEPoke chan, pname, Worksheets("Sheet1").Range(Cells(i, 1), Cells(i, 40))
    Next i
    DDETerminate (chan)
End Sub
---------------------------------------------------

Transmit individual point data, one point per cell, to points in the OPC DataHub. Triggering this macro writes all the data from the Excel table to the DataHub. The macro writes each cell of the table in turn to a single point in the DataHub.

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

---------------------------------------------------
Sub transmit_points()
    Dim chan As Integer
    Dim pname As String

    chan = DDEInitiate("datahub", "default")
    For i = 1 To 100
        For j = 1 To 40
            pname = Format((i - 1) * 40 + j, "0000")
            pname = "point" & pname
            DDEPoke chan, pname, Worksheets("Sheet1").Cells(i, j)
        Next j
    Next i
    DDETerminate (chan)
End Sub
---------------------------------------------------


Appendix E. Excel Macro Library		E.3. Write data from Excel - Automatically on value change (using DDEPoke)