Difference between revisions of "How to read an OPC-UA node value in Excel"
(Created page with "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 -> Vi...") |
|||
| Line 2: | Line 2: | ||
Step 1: Create a new Excel worksheet, and select Developer -> Visual Basic from the ribbon: | Step 1: Create a new Excel worksheet, and select Developer -> Visual Basic from the ribbon: | ||
| − | + | [[File:howtoreadanopcuanodevalueinexcel-1.png|center]] | |
Step 2. In “Microsoft Visual Basic for Applications” window, select Tools -> References, check the box next to "OPC Labs EasyOPC-UA Type Library", and press OK: | Step 2. In “Microsoft Visual Basic for Applications” window, select Tools -> References, check the box next to "OPC Labs EasyOPC-UA Type Library", and press OK: | ||
| − | + | [[File:howtoreadanopcuanodevalueinexcel-2.png|center]] | |
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 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: | ||
| − | + | [[File:howtoreadanopcuanodevalueinexcel-3.png|center]] | |
Step 4. Enter the code for the handler as follows: | Step 4. Enter the code for the handler as follows: | ||
| Line 23: | Line 23: | ||
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: | 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: | ||
| + | [[File:howtoreadanopcuanodevalueinexcel-4.png|center]] | ||
| − | |||
Revision as of 15:22, 31 July 2016
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 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.
