plc.myforum.ro

tampa · Junior Joined: 20 Jan 2017 Posts: 8 Location: usa

How do you lucrate an OPC client with Microsoft Excel?
I saw there is an interest on reading data on Excel
https://support.industry.siemens.com/cs/document/24299099/how-do-you-create-an-opc-client-with-microsoft-excel-?dti=0&lc=en-WW

This instructions are for creating an OPC client with VBA in Microsoft Excel and how to read data from the PLC and write data to the PLC via the OPC interface.
Microsoft Excel as OPC client initiates the communication via the OPC interface and sends read/write requests to the OPC server. The OPC server executes these read/write requests.

Proceed as follows to configure Microsoft Excel as OPC client.

Realizate an interface in Excel and store the separate control elements with statements.

How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 01

Incorporate the "Siemens OPC DA Automation 2.0" components in the VisualBasic editor under "Tools->References..." so that VisualBasic knows the OPC objects.
Now all the necessary objects are created, e.g.: the "MyOPCGroup" object
Dim: Create a variable
WithEvents: The object should be able to provide events (e.g. DataChange)
MyOPCGroup: Object name
As OPCGroup: Type of variable
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 02

Provide the "MyOPCServer" object with memory:
Set MyOPCServer = New ...
Microsoft Excel sets up the connection to the OPC server:
Call MyOPCServer.Connect(ServerName)
Use the following function to access the information of a cell in Microsoft Excel. In this example, the name of the OPC server is taken from the cell (line 4, column B).
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 03

Once Microsoft Excel has set up the connection to the OPC server, the OPC groups are created. This is done with the collection object:
MyOPCServer.OPCGroups.Add(group name)
In this example the newly created group is addressed via the "MyOPCGroup" variable. Set MyOPCGroup.IsSubscribed = True, so that the OPC group supplies events like DataChange.
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 04

Create the OPC items. This is done with the collection object:
MyOPCItems(i) = MyOPCGroup.OPCItems.AddItem(ItemID)
In this example, the ItemIDs are stored in the following cells (line 9-12, column B). Using the following function, you determine the ItemIDs and transfer them to the collection object "MyOPCGroup.OPCItems.AddItem":
Cells(line, column)
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 05

Read the values from the PLC using the following function:
MyOPCGroup.SyncRead()
Transfer the server handles of the OPC items as parameters to this function.
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 06

The values read out of the PLC are displayed in the cells (line 9-10, column E).
The quality and time stamp of the values read are shown in the cells (line 9-10, columns H and I). The function "MyOPCGroup.SyncRead()" provides this data as return value.
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 07

Call the following event handling routine for the OPC group for the DataChange events of the OPC server to be processed in Microsoft Excel:
DataChange()
This event handling routine supplies a number of parameters like ItemValue (value of the OPC Item), client handle etc.
Via the DataChange event, you receive the data when the value of the OPC item changes. The current value of the OPC item is shown in the cells (line 9-12, column D).
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 08

If you enter a value in the cell (line 9-13, column F) in Microsoft Excel, the OPC item is written to the PLC with the following function:
MyOPCGroup.SyncWrite()
The values to be written and the server handle are transferred to this function as parameters.
How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

Fig. 09

The function GetQualityText supplies an error message as string for a predefined error code.

tampa · Junior Joined: 20 Jan 2017 Posts: 8 Location: usa

Simple Plant visualization with OCX Data Control in VBA for Excel
https://support.industry.siemens.com/cs/document/23829402/simple-plant-visualization-with-ocx-data-control-in-vba-for-excel

The application on hand shows a very simple process for generating individual visualization user interfaces with Microsoft Excel and VBA. In this application, a simple production plant is simulated with a SIMATIC S7 controller. The required process information is exchanged between the Excel application, via the OCX Data Control, and the SIMATIC NET OPC server and the S7-CPU via the MPI interface.

Hardware configuration

How do you create an OPC client with Microsoft Excel? How do you create an OPC client with Microsoft Excel?

In VBA Code you learn how

S7 process data with SIMATIC NET ActiveX Controls (without programming skills) and Excel Standard Controls (with individual programming) are linked and displayed
larger data volumes are written to the S7-CPU or read from it
the most important S7 variable types are converted and displayed in VBA

https://support.industry.siemens.com/cs/attachments/23829402/23829402_OPC_DATCON_EXCEL_DOKU_v10_d.pdf
https://support.industry.siemens.com/cs/attachments/23829402/23829402_OPC_DATCON_EXCEL_DOKU_v10_e.pdf
https://support.industry.siemens.com/cs/attachments/23829402/23829402_OPC_DATCON_EXCEL_CODE_v10.zip