How can you graphically display a variable of the PLC via EXCEL as OPC client with PC Access as OPC server?
In this entry we will use an example to demonstrate how you can graphically display a variable from the S7-200 CPU with an Excel client for S7-200 PC Access.
- Configuration of S7-200 PC Access
- Installation of the S7-200 add-in for implementing MS Excel as OPC client
- Description of the S7-200 PC Access Excel client with graphical display for a variable
- Changing the Excel client example
- S7-200 CPU
- Communications cable to the S7-200 CPU
- STEP 7 Micro/WIN V4.0
- S7-200 PC Access V188.8.131.52
- Microsoft Excel
- STEP 7 Micro/WIN sample project: PCAccess.mwp
The STEP 7 Micro/WIN sample project PCAccess.mwp is located in the installation path of S7-200 PC Access under C:\Programs\Siemens\S7-200 PC Access\Samples.
- S7-200 PC Access - Project: PCAccessDiags.pca
The S7-200 PC Access project PCAccessDiags.pca is located in the installation path of S7-200 PC Access under C:\Programs\Siemens\S7-200 PC Access\Samples.
- Load the STEP 7 Micro/WIN project PCAccess.mwp into the S7-200 CPU.
- Set the S7-200 PLC to RUN mode.
- Start S7-200 PC Access.
- Open the sample project PCAccessDiags.pca.
- Save the project so that the items are transferred to the OPC server.
- Close S7-200 PC Access.
- Start MS Excel.
- Select the menu command Options > Add-Ins....
- Click on the Browse button.
Fig. 1: Finding S7-200 Add-Ins
- Select the file OPCS7200ExcelAddin.xla in the installation path of S7-200 PC Access under C:\Programs\Siemens\S7-200 PC Access\Bin. Acknowledge the dialog with OK.
- In the Excel dialog field Add-Ins enable the option OPC-Client AddIn for Excel.
Fig. 2: Enabling add-in
- Close the open dialogs.
- Once the S7-200 add-in has been installed the Microsystems OPC function bar is displayed in the Excel toolbar.
Fig. 3: OPC function bar
The following download contains an S7-200 PC Access Excel client with an animated thermometer for displaying a variable (VW0) of the S7-200 controller.
PCAccessThermo1Scale.zip ( 9 KB )
The thermometer has been created using the Diagram Wizard and existing AutoFormats in Excel.
Fig. 4: Sample Excel client
The variable that S7-200 PC Access supplies to the Excel client has been scaled to the value range 0...100 degrees. The following formula is used in Cell 4 for reading out and scaling the variable with Excel: =TRUNC((OPC("2,VW0,WORD,RW")/65535)*100).
Fig. 5: Formula for output of the variable in Excel
In the Microsystems OPC function bar select the button Start data capture to enable the Excel client.
To start the Excel client it might be necessary to change the path for the Excel add-in in the formula.
Fig. 6: Start data capture
If you add another thermometer, for example, and wish to change the scale of the thermometer values in the Excel client, then please proceed as follows.
- Copying and changing the cell in which the values are output.
- Mark the original cell in which the values are output (here C4, for example). The scale formula is displayed.
- Copy the formula into a new cell.
- Change the scale in the new cell to a range of 50 values from 50 to 100.
Fig. 7: Changing the scale in the new cell
- Changing the thermometer scale
- Right-click the thermometer axis to open the menu Format Axis....
Fig. 8: Formatting the axis
- Under Scale you set the Minimum to "50" and Major unit to "5".
Fig. 9: Changing the scale
- Changing the data source for the thermometer.
- Right-click the diagram to open the menu "Source Data...".
Fig. 10: Changing the data source
- For Series1 change the value source from =Thermometer!$C$4 (old cell) to the new cell, e.g. =Thermometer!$K$4.
Fig. 11: Specifying the new cell as value range
- Changing the output field of the thermometer value
- Mark the thermometer bulb and remove the grouping via Grouping > Ungroup.
Fig. 12: Removing the grouping of the thermometer bulb
- Select a different object in the Excel client.
- Click again on the thermometer bulb.
- Change the formula displayed to the new cell data (e.g.=Thermometer!$K$4 ) so that the new value is displayed.
Fig. 13: Specifying the new cell for the output field
- Then regroup the objects of the thermometer bulb via Grouping > Regroup.
- In the Microsystems OPC function bar select the button Start data capture to enable the Excel client.
- More information on OPC Excel clients for S7-200 PC Access is available in the Online Help of S7-200 PC Access, chapter 4 "Working with Excel".
- Another sample OPC-Client application is available for S7-200 PC Access in MAS 27: "Flexible Decoding and Processing of Data Matrix Codes" (Entry ID: 27058037)
- Other general OPC-Client sample applications are available at http://www.opcfoundation.org/ under "Downloads" -> "Sample Code".