Engineers-Excel.com

Excel OPC Client

Description| How it works



This powerful app gets real time data from control systems into Excel using OPC.

Download

.xlsm file (30 KB)

How to Use

Macros must be enabled for this app to work. In addition, a reference should be made to the OPC automation dynamic link library (DLL). While the specification for programming using this DLL is provided by the OPC foundation, the DLL itself is not provided for non-members. This DLL is, however, provided by the vendors selling OPC servers.

In this app, we use the Matrikon OPC automation DLL. To include a reference to a DLL, go to the Visual Basic editor by pressing Alt + F11 in Excel, then to to Tools and then References to choose the required DLL.

OPC Reference

Once that is done, enter the OPC Server Name and ProgID as shown below. Enter the tags to be read under the "Tags" heading. Press the "Connect & Read" button to connect to the OPC Server and read real time data from the OPC Server. The following screenshots show the entries for connection to (free) Matrikon OPC Simulation Server and data being read data from it. The tags are validated before being read.

OPC Server

OPC Read

OPC Connection Error

A common error that one gets from VBA while trying to make an OPC connection is:

"Method 'Connect' of 'IOPCAutoServer' Failed"

This happens when the OPC server refuses the connection request from the client. There can be different reasons for this, but the 2 most common ones are:

  1. The client and servers are on 2 different IP segments and network security does not allow the connection. Also, the connection may be blocked by firewalls on the client PC or the network.
  2. The DCOM settings on either the client or the server is not setup correctly. This page has more details and troubleshooting information.

OPC Data Logger

If you need to log the data from the client, you may wish to purchase the OPC Data Logger from the sister site www.xlncontrol.com. It logs the data from the OPC server into an Excel sheet.

Also See:

PID Loop Simulator 

Loop Drawings Using Excel

Animated Charts

Psychedelic Mandelbrot Set