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