With the S7-1500/S7-1200 how do you clearly display the items from the diagnostics buffer in Excel?
The coherent diagnostics data that is displayed in multiple lines in the diagnostics buffer is merged correspondingly into one row in the Excel file and includes information about time stamps, status transitions, and the message status. You can have the diagnostics data displayed clearly by filtering the columns.
Proceed as follows for this:
- In the Project tree you mark the CPU to be diagnosed, "PLC_1 [CPU 1516F-3 PN/DP]", for example.
- In the toolbar you click the "Go online" function.
- Then in the Project tree you double-click "Online & Diagnostics".
- In the "Online access" dialog you mark the "Diagnostics buffer" item.
- In the diagnostics buffer you click the "Save as..." button and save the data in a directory on your hard disk. In this example the file name "Diagnostics" has been assigned for the txt file.
- Open the txt file with the Notepad editor. To do this you right-click the file and in the pop-up menu you select the item "Open with > Editor".
- Execute the following two menu commands: "Edit > Select All" and "Edit > Copy".
- Open the Excel file "109763364_STEP7_TIA_Portal_CPU_Diagnosepuffer" and in the lower toolbar you select the corresponding worksheet ("German" or "English").
Copy the English diagnostics texts into the "English" worksheet and the German diagnostics texts into the "German" worksheet.
- Paste the contents of the clipboard into row "A3". To do this you right-click in the "A3" cell and from the pop-up menu you select "Insert content ...". In the "Insert Content" dialog you select the "Unicode Text" format and click OK. The contents of the clipboard is inserted in the Unicode Text format.
- With the columns you have the option of filtering. To obtain a clearer view you first filter the "ID" column without "Blanks". You can set filters in each column, for example, according to date and time, or change of status of the CPU.
The Excel file consists of a formula field (no macros) and it can also be extended.
The download includes the Excel file described above for the S7-1200 and for the S7-1500. Unpack the Zip file in a separate directory on your hard disk. Then you can use the Excel file as described in the instructions.
109763364_STEP7_TIA_Portal_CPU_Diagnosepuffer.zip (4.5 MB)
The Excel file and screens 2 and 3 were created with Microsoft Excel 2010. Screen 1 was created with STEP 7 (TIA Portal) V15.