Excel OPC ClientDescription| 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.
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 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:
- 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
- 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
Psychedelic Mandelbrot Set