OPC Expert enables users to access 1-dimension and 2-dimension arrays. Documentation below describes how OPC Expert enables Excel to read OPC items containing 1-dimension arrays. Excel can show the entire array (containing multiple elements), or a single array element.
OPC Classic (DA) Example
Consider we have an OPC DA server with the following information
- Computer name: MyComputer
- OPC Server ProgID: MyOpcServer.ProgID
- OPC ItemID: MyItem
- Update rate: 1000 milliseconds (we want updates as fast as once per second, but not faster)
- Array contents: Apple, Orange, Pear
The table below shows an Excel example:
| # | Cell Contents | Cell Value |
| 1 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,,1000) | Apple, Orange, Pear |
| 2 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,”array(0)”,1000) | Apple |
| 3 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,”array(1)”,1000) | Apple |
| 4 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,”array(2)”,1000) | Orange |
| 5 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,”array(3)”,1000) | Pear |
| 6 | =RTD(“expertrtd”,,”MyComputer”,”MyOpcServer.ProgID”,”MyItem”,”array(6)”,1000) | <blank> |
| 7 | =RTD(“expertrtd”,,”localhost”,”MyOpcServer.ProgID”,”MyItem”,”array(0)”,1000) | Apple |
OPC UA Example
Consider we have an OPC DA server with the following information
- Computer name: MyComputer
- OPC Server URL: opc.tcp://MyComputer:57888
- OPC ItemID: MyItem
- Update rate: 1000 milliseconds (we want updates as fast as once per second, but not faster)
- Array contents: Apple, Orange, Pear
The table below shows an Excel example
| # | Cell Contents | Cell Value |
| 1 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,,1000) | Apple, Orange, Pear |
| 2 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,”array(0)”,1000) | Apple |
| 3 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,”array(1)”,1000) | Apple |
| 4 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,”array(2)”,1000) | Orange |
| 5 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,”array(3)”,1000) | Pear |
| 6 | =RTD(“expertrtd”,,”MyComputer”,”opc.tcp://MyComputer:57888″,”MyItem”,”array(6)”,1000) | <blank> |
| 7 | =RTD(“expertrtd”,,”localhost”,”opc.tcp://localhost:57888″,”MyItem”,”array(0)”,1000) | Apple |
Notes:
- The only difference between the OPC DA and UA examples is the OPC server reference
- Array element numbering is “1-based”; therefore, the first element is 1, and the tenth element is 10 (elements 0 and 1 are the same)
- Remember to enclose array element reference in quotations
- Example #6 shows a blank value because array only has 3 elements, and we are asking for element number 6
- Example #7 uses a generic reference of “localhost” instead of “MyComputer”
See also: