Great place to check latest deals

My favorite website for deals is Deals 2 Buy
Go get the deals !!!

Reading OPC data Using DataChange Subscription Events

In the previous section we read data synchronously, Also it was done using a timer loop.

In this example we make use of the VBA Feature called Events.

The OPC group subscribes to Data Change Event. Based on the refresh rate if the Data is Changed the DataChange function is invoked which can be used for processing.

The automation interface supports the event notification mechanism that is provided with Visual Basic The Automation server triggers events in response to Async Refresh, Async Read and Async Write Method calls. In addition, Automation server triggers events when data changes according to the client specification. The implementation assumes that the Automation Client is equipped to deal with these events.

DataChange

Description

The DataChange event is fired when a value or the quality of a value for an item within the group has changed. Note the event will not fire faster than the update rate of the group. Therefore, item values will be held by the server and buffered until the current time + update rate is greater than the time of the previous update (event fired). This is also affected by active states for both Group and Items. Only items that are active, and whose group is active will be sent to the client in an event.

Syntax

DataChange (TransactionID As Long, NumItems As Long, ClientHandles() As Long, ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)

Example

Open Excel Tools -> Macro ->Visual Basic Editor

Click Insert Class Module

Paste the following code

Option Explicit

Option Base 1

Const MaxItemCount = 100

Public WithEvents OPCMyserver As OPCServer

Public WithEvents OPCMygroups As OPCGroups

Public WithEvents OPCMygroup As OPCGroup

Dim OPCMyitems As OPCItems

Dim OPCMyitem As OPCItem

Dim ItemCount As Integer

Private Sub Class_Initialize()

MsgBox "Server startup"

Connect "Matrikon.OPC.Simulation.1"

AddGroup "Group1", "Random.Int1"

Subscribe True

End Sub

Private Sub Class_Terminate()

Dim Errors() As Long

On Error Resume Next

OPCMygroup.IsActive = False

OPCMygroups.Remove OPCMygroup.ServerHandle

Set OPCMyitems = Nothing

Set OPCMyitem = Nothing

Set OPCMygroups = Nothing

Set OPCMygroup = Nothing

OPCMyserver.DisConnect

Set OPCMyserver = Nothing

End Sub

Function Connect(ServerName As String)

On Error GoTo ConnectError

Set OPCMyserver = New OPCServer

OPCMyserver.Connect ServerName, ""

Connect = True

Exit Function

ConnectError:

Connect = False

End Function

Function AddGroup(GroupName As String, ItemID As String)

On Error GoTo AddError

Dim i As Integer

Dim ItemServerHandles() As Long

Dim ClientHandles(1) As Long

Dim OPCItemIDs(1) As String

Dim Errors() As Long

Set OPCMygroups = OPCMyserver.OPCGroups

Set OPCMygroup = OPCMygroups.Add(GroupName)

OPCMygroup.UpdateRate = 1000

Set OPCMyitems = OPCMygroup.OPCItems

ItemCount = 1

For i = 1 To ItemCount

ClientHandles(1) = i

OPCItemIDs(1) = ItemID

OPCMyitems.AddItems 1, OPCItemIDs, ClientHandles, _

ItemServerHandles, Errors ''', RequestedDataTypes, AccessPaths

Cells(i, 1) = OPCItemIDs(1)

If Errors(1) <> 0 Then

Cells(i, 2) = "#N/A"

End If

Next i

AddGroup = True

Exit Function

AddError:

AddGroup = False

End Function

Sub Subscribe(bSubscribe As Boolean)

On Error Resume Next

OPCMygroup.IsActive = bSubscribe

OPCMygroup.IsSubscribed = bSubscribe

If bSubscribe = True Then

Dim anItem As OPCItem

For Each anItem In OPCMygroup.OPCItems

anItem.Read OPCCache ', value, qual, time

Cells(anItem.ClientHandle, 2) = anItem.Value

Cells(anItem.ClientHandle, 3) = anItem.TimeStamp

Cells(anItem.ClientHandle, 4) = anItem.Quality

Set anItem = Nothing

Next anItem

End If

End Sub

Sub UnSubscribe()

OPCMygroup.IsSubscribed = False

End Sub

'Subscribed to OPC group Data Change Event

Private Sub OPCMygroup_DataChange(ByVal TransactionID As Long, _

ByVal NumItems As Long, ClientHandles() As Long, _

ItemValues() As Variant, Qualities() As Long, TimeStamps() As Date)

Dim i As Integer

For i = 1 To NumItems

Cells(1, 2) = ItemValues(i)

Cells(1, 3) = TimeStamps(i)

Cells(1, 4) = Qualities(i)

Next i

End Sub

'Subscribed to Server shutdown event

Private Sub OPCMyserver_ServerShutDown(ByVal Reason As String)

MsgBox "Server shutdown"

End Sub

You will need to instantiate the class so we will do it when the Workbook opens

Note the WithEvents declaration makes sure the events for the objects are turned on.

Dim instance As Class1

Public WithEvents OPCMyserver As OPCServer

Public WithEvents OPCMygroups As OPCGroups

Public WithEvents OPCMygroup As OPCGroup

Private Sub Workbook_Open()

Set instance = New Class1

Set OPCMygroup = instance.OPCMygroup

Set OPCMyserver = instance.OPCMyserver

Set OPCMygroups = instance.OPCMygroups

End Sub

Programming OPC using Excel

Basics of OPC

Object-Linking and Embedding (OLE) for Process Control, is the original name for a standards specification developed in 1996 by an industrial automation industry task force. The standard specifies the communication of real-time plant data between control devices from different manufacturers.
The standards are driven my OPC Foundation.
Unfortunately most of the standards specifications are not accessible for free u need to be a member. Although some of these can be googled.
A good Multimedia Tutorial From Matrikon Explains the basics of OPC.

OPC Client
You may choose to program OPC clients by the following ways
a ) invoking COM interfaces exposed by OPC Server.
b) Use Web services api OPC XML-DA standards
c) Use OLE Automation Api which allows you to program using Macros within Microsoft products such as Excel and Powerpoint or even windows shell scripts using Windows Scripting Host.

Simple example to Read Realtime data from OPC Server using excel
For doing this first thing install a OPC server . If you have one gr8. ignore this step.
Download a OPC Server . I will use Matrikon OPC Simulation Server
to demonstrate the example. You could use any other OPC server which provides OLE Automation library.

OPC server acts as a COM Server and exposes Interfaces which can be used by Clients. OLE Automation was designed to make scripting life easier for VB and other scripter. OLE Automation Object is basically a COM object implementing the IDispatch interface .Automation objects are referred to as ActiveX objects, while an application that manipulates an ActiveX object is referred to as an ActiveX Client.

When we install the Matrikon OPC Simulation server It registers COM Server and Automation Library DLL

OPCAuto.dll à Provides the OPC Automation Library

OPCDAuto.dll -> OPC DA Automation Wrapper DLL

OPCHDAAuto.dll ->OPC HDA Automation Wrapper DLL

Lets Start Some coding

Open Excel

Open Tools->Macros-> Give a name OPC->create

Add reference to Matrikon OPC Automation (This will show up only after u install the simulation server) and OLE Automation

After this you are good to code.

OPC Automation Server Object Model

Some of the key objects the OPC Automation library exposes

Object	Description
OPCServer	An instance of an OPC Server. You must create an OPCServer object before you can get references to other objects. It contains the OPCGroups Collection and creates OPCBrowser objects.
OPCGroups	An Automation collection containing all of the OPCGroup objects this client has created within the scope of the OPCServer that the Automation Application has connected to via the OPCServer.Connect()
OPCGroup	An instance of an OPCGroup object. The purpose of this object is to maintain state information and provide the mechanism to provide data acquisition services for the OPCItem Collection object that the OPCGroup object references.
OPCItems	An Automation collection containing all of the OPCItem objects this client has created within the scope of the OPCServer, and corresponding OPCGroup object that the Automation Application has created.
OPCItem	An automation object that maintains the item’s definition, current value, status information, last update time. Note the Custom Interface does not provide a separate Item Object.
OPCBrowser	An object that browses item names in the server’s configuration. There exists only one instance of an OPCBrowser object per instance of an OPC Server object.

The example below reads a predefined tag called "Random.Int1" every 5 seconds and displays it in A1 Cell of excel. The simulation servers keeps changing the Random.Int1 tag value which will be reflected in the worksheet.

Steps are

Connect to the OPC Server

Add a group

Add the items (tags) which you want to read

Read the current value of the tag

Sub opc()

Dim groups As OPCGroups

Dim handle As Long

Dim opcServer As New opcServer

Dim AnOPCItem As opcItem

Dim grp As OPCGroup

Dim sheet1 As Worksheet

Set sheet1 = ThisWorkbook.Worksheets("Sheet1")

opcServer.Connect ("Matrikon.OPC.Simulation.1")

Set groups = opcServer.OPCGroups

groups.Add "NewGroup"

Set grp = groups.GetOPCGroup("NewGroup")

Set AnOPCItemCollection = grp.OPCItems

AnOPCItemCollection.AddItem "Random.Int1", handle

Set AnOPCItem = AnOPCItemCollection.Item(1)

AnOPCItem.Read (1)

sheet1.Range("A1") = AnOPCItem.Value

Application.OnTime Now + TimeValue("00:00:05"), "opc"

End Sub
You should see values getting changed every minute