How to read an OPC item value in Excel

From OPC Labs Knowledge Base
Jump to navigation Jump to search
Warning-icon.png

Warning: Effective from QuickOPC 2017.1, the method described here is obsolete for most purposes. It is recommended that you use the Excel Real Time Data (RTD Server in Excel Connector) instead.

This is the simplest Excel VBA example. It reads an OPC item value and stores it in a cell of a worksheet.

Step 1: Create a new Excel worksheet, and select Developer -> Visual Basic from the ribbon:

Howtoreadanopcitemvalueinexcel-1.png

Step 2. Select Tools -> References, check the box next to "OPC Labs EasyOPC "Classic" Library" (in versions up to 5.41, "OPC Labs EasyOPC Type Library"), and press OK:

Howtoreadanopcitemvalueinexcel-2.png

Step 3. Select This Workbook in the project tree, and then add a handler for opening the workbook by selecting Workbook and Open from the drop-downs on the top of he code window:

Howtoreadanopcitemvalueinexcel-3.png

Step 4. Enter the code for the handler as follows:

   Private Sub Workbook_Open()
       ' Create EasyOPC-DA component
       Dim EasyDAClient As New EasyDAClient
 
       ' Read item value and display it
       Range("A1").Value = EasyDAClient.ReadItemValue("", "OPCLabs.KitServer.2", "Demo.Single")
   End Sub

As you can guess from the code, the event handler reads a value of an OPC Item (Demo.Single) and stores it into cell A1 of the worksheet.

Step 5. Save the file as "Excel Macro-Enabled Worksheet", close Excel, and then open the file again. The worksheet will read the OPC item and put it into cell A1, so you will see something like this:

Howtoreadanopcitemvalueinexcel-4.png



This concept can obviously be enhanced in many different ways. Specifically, you may want to tie the OPC actions to different events, such as pressing a button on the sheet, or a periodic timer. This example project is included with the product. Please use the example from the product itself for the most up-to-date code of the example.

See Also

How to read an OPC-UA node value in Excel