There are two methods of transmitting a range, or array data, from Excel to the OPC DataHub. These exactly match the mechanisms used for individual point data: DDEPoke and DDEAdvise.

Using DDEPoke with a Macro

A DDEPoke command can be issued by Excel to send data to the OPC DataHub based on a trigger within Excel. For this to work, the OPC DataHub needs to be configured to act as a DDE server and have registered at least one service name. An Excel macro can then issue a DDEPoke to that service, along with a OPC DataHub data domain name (the DDE topic), a point name (the DDE item) and a value. If the value is of type Range then Excel will automatically format the value as a tab-and-newline separated string.

Example: See the definition of the PutData function in the Excel macro coding examples below.

Using a DDE Advise Loop

When sending data from Excel to the OPC DataHub using a DDE advise loop, Excel acts as the DDE server and the DataHub acts as the client. To create the advise loop:

There are two ways to drag and drop data into Excel to create a range, using DDE advise loops. Or you can use DDE Request and macros.

Drag and drop a group of points into Excel

Here is how you can collect a group of points in the DataHub and drag them all into Excel, where the data for each point occupies a unique cell.

You can drag and drop point names, timestamps, and other attributes of a point using the Property dropdown list. Please refer to Drag and Drop Style and Property in the Data Browser section for more details.

Drag and drop an array into Excel

Here is how you can take a single point in the DataHub whose value is an array, and have each value in the array occupy a unique cell in Excel.

To demonstrate this, we are going to first combine the two procedures shown above to create an array in the DataHub

Make an array

Drag and drop the array

For simplicity's sake we are going to just put the same array back into Excel.

Using DDE Request in Excel

If you are creating macros in Excel to read data from the OPC DataHub, you can use the DDERequest function call. This will return an array type value that can be written directly into any range in the spreadsheet. If the array data is larger in any dimension than the range into which it is written, then extra data in the array is discarded. If the array data is smaller than the target range then extra cells in the range are filled by repeating the data in the array. See below for an Excel macro that dynamically determines the target range to ensure that all array data is entered into the spreadsheet with no duplication.

The following macros represent the entire macro set for a simple test spreadsheet that reads and writes a single array point in the OPC DataHub. The two functions GetData and PutData can be attached to buttons on a spreadsheet for easy testing. The PutData subroutine contains two alternative representations of the source range, one of which is commented out in the macro.

Sub GetDataArray(Channel As Integer, SheetName As String, DataPoint As String, _
                 StartRow As Integer, StartCol As Integer)
    Dim NRows As Integer, NCols As Integer
    
    ' This sub performs a DDERequest for DataPoint in the DDE Channel and reads in a tab
    ' delimited array with carriage returns at the end of each line. It then fills a range 
    ' of cells with the data. The native format for Excel data is tab delimited text with a
    ' carriage return at the end of each row of data. If we assign this type of data to a
    ' range of cells using the FormulaArray function, Excel automatically parses the data
    ' and fills it into the specified range.  The real trick here is to ensure that the
    ' range is the same size as the incoming data, so we do not have to know the size
    ' a priori.
    
    DataArray = DDERequest(chan, DataPoint)   ' request DataPoint from Channel
    
    ' find the upper row and column bounds for the variant array
    
    If StartCol = 0 Then StartCol = 1   ' Starting column where data will go in our sheet
    If StartRow = 0 Then StartRow = 1   ' set the starting row
    NCols = 1                           ' set default number or columns to 1
    On Error Resume Next                ' ignore errors (error occurs if array has
                                        '                one dimension)
    
    ' get upper bound of the array columns
    ' the following line will generate an error if the array is only a one dimensional array
    ' We just skip this, and use the default 1
    NCols = UBound(DataArray, 2)
    
    On Error GoTo 0                     ' allow errors
    NRows = UBound(DataArray, 1)        ' get upper bound of array y dimension
    
    NRows = NRows + StartRow - 1        ' add offset from StartRow - this is the ending row
    NCols = NCols + StartCol - 1        ' add offset from StartCol - this is the ending col
    
    ' the following line fills up the cells in the range starting in "StartCol:StartRow" to
    ' "Nrows:Ncols" with the data from the variant array
    Sheets(SheetName).Range(Cells(StartRow, StartCol), Cells(NRows, NCols)) = DataArray
End Sub

Sub PutDataArray(Channel As Integer, SheetName As String, DataPoint As String, _
                 StartRow As Integer, StartCol As Integer, NRows As Integer, _
                 NCols As Integer)
    DDEPoke Channel, DataPoint, Sheets(SheetName).Range(Cells(StartRow, StartCol), _
            Cells(StartRow + NRows - 1, StartCol + NCols - 1))
End Sub

Sub PutDataRange(Channel As Integer, DataPoint As String, DataRange As Range)
    DDEPoke Channel, DataPoint, DataRange
End Sub

Sub GetData()
'
'   This is a test function assigned to a button.  It reads a test point into
'   an arbitrarily sized matrix starting at A10
'
    Dim chan As Integer
    chan = DDEInitiate("datahub", "default")
    GetDataArray chan, "Sheet1", "TestArray", 10, 1
    DDETerminate (chan)
End Sub

Sub PutData()
'
'   This is a test function assigned to a button.  It writes a 3 row x 5 column
'   area of Sheet1 into a single data point in the DataHub.  You can use either
'   PutDataArray or PutDataRange, depending on how you wish to specify the range.
'
    Dim chan As Integer
    chan = DDEInitiate("datahub", "default")
    'PutDataArray chan, "Sheet1", "TestArray", 1, 1, 3, 5
    PutDataRange chan, "TestArray", Sheets("Sheet1").Range("A1:E3")
    DDETerminate (chan)
End Sub