How to read an OPC-UA node value in Excel

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-UA node 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:

Step 2. In “Microsoft Visual Basic for Applications” window, select Tools -> References, check the box next to "OPC Labs EasyOPC-UA Library" (in versions up to 5.40, "OPC Labs EasyOPC-UA Type Library"), and press OK:

Step 3. Select and double-click 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 the code window:

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

   ' Create EasyOPC-UA component
   Dim Client As New EasyUAClient
   
   ' Read node value and display it
   Range("A1").Value =   
       Client.ReadValue("http://localhost:51211/UA/SampleServer", 
       "nsu=http://test.org/UA/Data/;i=10853")

As you can guess from the code, the event handler reads a value of an OPC-UA node 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:

---

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 item value in Excel (OPC Data Access)