S7-To-Excel-Tool

 S7-To-Excel

 Traeger Industry Components GmbH

© by TIS


Documentation for version 2.0.0.0


Summary

The S7-To-Excel-Tool generates an Excel file with process data from a SIMATIC-S7.
Appearance and format (formulas etc.) of the Excel file will be created as a template in the form of an Excel file.
The template contains all the information such as:

  • Target directory
  • Target file including format specifications for date and time
  • Connection parameters to the PLC
  • Addresses of the PLC variable
  • The condition for generating the target file

This information is stated by means of key words in a random position in the Excel worksheet. Variables from the PLC appear in the same position (cell) on the destination file where the relevant variable address was entered.

In version 1.x you can only replace the PLC variables.
With version 2.x you can now append sheets dynamically in horizontal and vertical direction by using regions.

Feature Standard Advanced Expert Evaluation
Trigger based log file creation x x x x
Maximum number of channels 1 1 1 1
Maximum number of triggers 1 / Sheet 1 / Sheet 1 / Sheet 1 / Sheet
Interval trigger x x x x
Value change trigger x x x x
Boolean trigger x x x x
Conditional / edge trigger x x x x
Append Sheet by region(s) x x
Maximum number of variables 100 unlimited unlimited unlimited

The Excel file can contain any number of worksheets.
However, there are names for worksheets that are specifically reserved for the S7-To-Excel-Tool:

Worksheet name use
PLC Connection contains the connection parameters to the PLC such as IP address, rack, slot, and the properties for saving

Otherwise, you can take any name for a worksheet.
The processing / updating is controlled by a trigger (= event in the PLC or time interval) for each worksheet.

 S7-To-Excel process
sample:
template
 S7-To-Excel
result
 S7-To-Excel

File settings:
 S7-To-Excel
Notifications:
 S7-To-Excel

The License key is also available as a USB-Dongle.
We use the products from MARX Software Security. To use the Dongles you have to do following steps:

Since 12/2015 the Licensekey is also available as a USB-Dongle.
We use the products from MARX Software Security. To use the Dongles you have to do following steps:

  • .Net Framework V 4.6 (check version / install)
  • install MARX “CBIOS Server Windows” as a service https://www.marx.com/de/support/downloads
  • plug in the Dongle
  • start our software
  • Under you will find the license informations




  • Create workbook
  • Create and format worksheets
  • Insert the address for the PLC variable register ($PLC_ADDR-value) into the desired cells
  • Save workbook, this file is your template
  • Start S7ToExcel.exe
  • Open Template (File → Select Template)
  • Determine target path / file destination
  • Edit / set / enable triggers
  • Select PLC connection parameters
  • Save template, the settings are automatically saved in the template
  • Note:
    • By clicking “Stop Trigger” you stop the evaluation.
    • By activating “autorun” the evaluation is started automatically with the template last used when you start the application.
    • “Overwrite file” overwrites, if existing, the file with the same name.

Launch the application and select the template. Then the available worksheets are presented to you.
By clicking Trigger starten you start the logging.
In the table you can find the last events. Here, the error messages are also displayed.

Gray controls are not available at the moment.
Menu

License Insert License Generate your machine code or insert your license key
Language Select Language Switch between German and English
About Information about the software Here you can find all information about the software and the currently used license data.

toolbar

open template Opens the selected template file
save settings Saves all settings from the program and the template (Shortcut STRG+S)
reload template reloads the current template
open template Trys to open the template file with the default program
snapshot All found variables in all sheets will be triggered
start Trigger start all triggers
stop Trigger stop all triggers
Is working? green bar: shows if analysis was started
gray bar: no analysis started

Excel settings

Destination file name Optional file name for the storage. If set, the name plus the current date and time will be saved. For example MASCHINE1_20150203_120304.xls. The issue date can be individually configured.
Time Format See keyword $PLC_FILENAME_POSTFIX,Format

 SPS settings

PLC Address Display of the read, defined PLC address in the template
PLC Type Display of the read, defined PLC
Rack Display of the read, defined rack number
Slot Display of the read, defined slot number

Note:
Please also note the settings for S7-1200 / S7-1500 / LOGO!.

The defined region is extended in the worksheet (below sheet) according to the defined rules.
Each sheet can have multiple regions, please refer to the keyword Region.

The following settings of the cell will be taken on:

  • cell formats (font, font size, formating of the display, colors)
  • borders
  • column width
  • row hight
  • formulas

Borders:
maximum possible column: IP (250 columns)
maximum possible row: 60000

Note:
We have improved the write mechanism and set a write interval of 10 seconds by default.

The result file will be written when

  • the write interval has expired.
  • a new file shall be generated, because the file name and / or the postfix have been changed.
  • one border has been reached.
  • the trigger is stopped.

Regions

If you click on the arrow, you will see the properties of the region:
Region properties

You can find more information under the keyword for the Region

Edit Region(s)

If you want to edit a region or create a new one, there are two possibilities:
Click on “…”
Regions

The following dialog for editing the region(s) opens:
Regionen bearbeiten

Here you can see, edit and delete all the regions or create a new region on the left side.
On the right side you see the properties of the region.

If you just want to edit the region, click on the arrow and change the data:
Region properties

Samples:
In descending vertical direction:

If you want to read a lot of data from different machines at the same time, it makes sense to expand the data in descending vertical direction.

Region: A2 - I5
Row offset: 4
Column offset: 0
Max. number of entries: 7

Template 1

Result after triggering three times:

Template 1 Result

In ascending vertical direction:

If you, for example, want to write the data in descending historical order, you can enter a negative offset and the data is written from the bottom up.

Region: A74 - I77
Row offset: -4
Column offset: 0
Max count of entries: 7

Template 2

Result after triggering three times:

Template 2 Result

Expand in horizontal direction, extending to the right:

If you, for example, want to log a record per machine triggered once every hour one below the other, it makes sense to write the data vertically to the right.

Region: B1 - B3
Row offset: 0
Column offset: 1
Max count of entries: 7

Template 3

Result after triggering two times:

Template 3 Result

Expand in horizontal direction, extending to the left

If you, for example, want to log a record per machine in historically descending order one below the other, it makes sense to use a negative column offset.

Region: F32 - F34
Row offset: 0
Column offset: -1
Max. number of entries: 5

Template 4

Result after triggering two times (Note: All not empty cells are written):

Template 4 Result

General settings (File name selected):
Trigger
At click on the sheet name:
Trigger

Autorun True: Triggers start automatically when you start the application and when selecting a template
Address Trigger address in the PLC, see $PLC_TRIGGER_ADDR
Datatype Datatype of the trigger address, see PLC-Addressing
Interval ( ms ) using an interval trigger: time in ms until the next logging; otherwise: time in ms until the next review of the trigger address
Reference value using a Boolean trigger: verification value for the edge; otherwise: value on which shall be checked
Mode Display of the trigger mode, see trigger mode
Manual trigger All variables found in all sheets will be triggered

The configuration is done via the corresponding keywords.
Syntax: $keyword,n0,n1,…,nx
The parameters n0,n1,…,nx are stated as separate by ','.

Configuration of the PLC Connection

keyword description sample
$PLC_IPADDR,IP-Address IP-Address of the PLC, needed for creating the connection to the PLC $PLC_IPADDR,192.168.0.80
$PLC_RACK,Rack rack number of the PLC $PLC_RACK,0
$PLC_SLOT,Slot slot number of the PLC e.g. by S7 300:
$PLC_SLOT,2
$PLC_DEVICETYPE,PLCtype Possible PLC device types:
- S71500
- S71200
- S7300_400
- S7200
- LOGO!
$PLC_DEVICETYPE,S7300_400

Note:
Please also see settings for S7-1200 / S7-1500 / LOGO!.

Configuration of the Destination File Name

keyword description sample
$PLC_SAVEPATH,directory The directory of target files. If no path is selected, the location is taken from the template file $PLC_SAVEPATH,C:\Documents\Excel
$PLC_FILENAME,filename With no input, only the Postfix is used as the name.
Use the $ PLC_ADDR keyword for dynamic name generation by the PLC
$PLC_FILENAME,MaschinenWh1
$PLC_FILENAME,$PLC_ADDR,DB1000.DBB 700,string[100]
$PLC_FILENAME_POSTFIX,Format By default, a time postfix with the format “yyyy-MM-dd_hh-mm-ss” is always appended to the filename.

You can format it as desired:
For example, the file name: MaschineWh1_2015-01-13_09-35-22.xls shall be generated:

$PLC_FILENAME,MaschineWh1
$PLC_FILENAME_POSTFIX,yyyy-MM-dd_HH-mm-ss
Format description
yy year 2-digit
yyyy year 4-digit
MM month 2-digit
MMM short name of the month
MMMM name of the month
dd day 2-digit
ddd short name of day
dddd name of day
H hour 0-23
h hour 1-12
HH hour 00-23
hh hour 01-12
mm minute 2-digit
s seconds 1-59
ss seconds 01-59
fff milliseconds 3-digit
ffff Ten thousandths of a second 4-digit
$APPEND_TRIGGERED_SHEETNAMES,Flag When set, the triggered sheet names are appended to the file name. This facilitates, for example, the location of the desired file within several sheets
0 = don't append
1 = append
$APPEND_TRIGGERED_SHEETNAMES,1
$PLC_OVERRIDEFILE,Flag When edge = 1, the file is overwritten. The file name assembles through file name + (sheet name) + postfix $PLC_OVERRIDEFILE,1 overwrites the file e.g. when using regions

Define Process Data from the PLC

PLC Addressing

Operand

Name Abbreviation (Siemens, DE) Abbreviation(IEC)
Input E I
Output A Q
Flag M M
Peripherals P P
Counter Z C
Data Block DB DB
Timer T 16

Data types

Name Abbreviation Bit size Range Description Array
BOOL X 1 0 to 1 single bit representing true (1) or false (0) x
BYTE B 8 0 to 255 unsigned 8-bit x
WORD W 16 0 to 65.535 unsigned 16-bit (Word) x
DWORD D 32 0 to 232 -1 unsigned 32-bit (Double Word) x
CHAR B 8 A+00 to A+ff ASCII-Code unsigned 8-bit character x
INT W 16 -32.768 to 32.767 signed 16-bit integer x
DINT D 32 -231 to 231-1 signed 32-bit integer (Double Word) x
REAL D 32 +-1.5e-45 to +-3.4e38 IEEE754 32-bit single precision floating point number x
S5TIME W 16 00.00:00:00.100 to 00.02:46:30.000 binary coded decimal (BCD) number representing a time span
TIME D 32 00.00:00:00.000 to 24.20:31:23.647 signed 16-bit integer representing a time span in milliseconds
TIME_OF_DAY D 32 00.00:00:00.000 to 00.23:59:59.999 unsigned 16-bit integer representing a time span in milliseconds
DATE W 16 01.01.1990 to 31.12.2168 unsigned 16-bit integer representing a date in days
DATE_AND_TIME D 64 00:00:00.000 01.01.1990 to 23:59:59.999 31.12.2089 binary coded decimal (BCD) number representing a date and time
S7String B any A+00 to A+ff ASCII-Code, max. 254 Bytes

The variables are composed of operand and data type. Examples:

Examples Data type Example Siemens Example IEC
Input Byte 1, Bit 0 BOOL E 1.0 I 1.0
Output Byte 1, Bit 7 BOOL A 1.7 Q 1.7
Flag Byte 10, Bit 1 BOOL M 10.1 M 10.1
Data Block 1, Byte 1, Bit 0 BOOL DB1.DBX 1.0 DB1.DBX 1.0
Input Byte 1 BYTE EB 1 IB 1
Output Byte 10 BYTE AB 10 QB 10
Flag Byte 100 BYTE MB 100 MB 100
Peripherals Input Byte 0 BYTE PEB 0 PIB 0
Peripherals Output Byte 1 BYTE PAB 1 PQB 1
Data Block 1, Byte 1 BYTE DB1.DBB 1 DB1.DBB 1

Data Block 1, Data Block 1 Typ bool, Address 1.0 → DB1.DBX 1.0
Data Block 1, Data Block Typ Byte, Address 1 → DB1.DBB 1
Peripherals Input, Typ DWORD, Address 0 → PED 0

Help:
DB#.DBB # = Data Block#.Data Block Byte #
DB#.DBW # = Data Block#.Data Block Word #
DB#.DBD # = Data Block#.Data Block Doubleword #
# = Address

keyword:
$PLC_ADDR,Address,Datatype,Option

description sample
The PLC address to be read.
Is written into the cell to be read
sample for MW 20
type: WORD
$PLC_ADDR,MW 20,WORD

sample for DB 1000, address 1
type: String
length: 100
$PLC_ADDR,DB1000.DBB 1,string[100]
Address The PLC address which shall be read. See PLC Addressing
Data type The type of the address which shall be read.
Option for Type “string”: Number written in [] defines the length of the data to be read

data types:
* BOOL
* WORD
* DWORD
* INT
* DINT
* REAL
* STRING
* CHAR
* BYTE
Not yet implemented:
* S5TIME
* TIME
* TIME_OF_DAY
* DATE
* DATE_AND_TIME
Option Optional statement e.g. r = read, w = write back. NOT IMPLEMENTED_

Timestamp

$TIMESTAMP Writes the current timestamp in the cell. Your defined formatting is retained.
Tip: In Excel milliseconds are defined with the format “,00” for a double-digit output and “,000” for three-digit output.

sample:
samples timestamp

Region

keyword:
$APPEND_REGION,[SheetName],Region,RowOffset,ColOffset,count of entries

parameter description sample
Sheet name If emtpy: actual sheet; otherwise: the sheet name in [] Sheet: “wh1”
Region A2 till I5
append max. 8 times

$APPEND_REGION,,A2:I5,4,0,8
or
$APPEND_REGION,A2:I5,4,0,8
or
$APPEND_REGION,[wh1],A2:I5,4,0,8
Region Region which should be appended.
Format:
StartColumn StartRow:EndColumn EndRow z.B. A2:I9
Row offset Row offset for the next entry of the region. If you have 7 rows and want to expand in row direction, you need to enter an offset of at least 7.

A negative offset extends the region in ascending horizontal direction.
Column offset Columnn offset for the next entry of the region. If you have 2 columns and want to expand in column direction, you need to enter an offset of at least 2.

A neagtive offset extends the region in vertical direction to the left.
Number of entries Maximum number of entries before a new file is to be generated when none of the cases described in Regionen applies for the generatation of a file.
-1 marks when a new file is created (file name is changing in Prefix or postfix).

Define Triggers

To trigger a worksheet, you must set the following three values:

  • Trigger address
  • Trigger interval
  • Trigger mode

The interval time should be at least half of the time the value is kept in the PLC. This ensures that the trigger can identify the changed data reliably .

keyword description sample
$PLC_TRIGGER_ADDR,Address,Type,Value PLC address for the trigger sample for MW 20
type: WORD
value: 10

$PLC_TRIGGER_ADDR,MW 20,WORD,10
Address Address in the PLC e.g. MW 20. See PLC Addressing for PLC addressing
Type Data type to be read. See $PLC_ADDR for possible types
Value Value on which shall be checked
$PLC_TRIGGER_INTERVAL,ms Interval in milliseconds
Option 1: Period in which the trigger variable is checked
Option 2: Period in which a new query is issued in the PLC (interval trigger)
sample for triggering every second:
$PLC_TRIGGER_INTERVAL,1000
trigger modes for generating Excel files
Has to be included in each worksheet in which PLC addresses are to be read
sample interval trigger:
$PLC_TRIGGER_MODE,0
Mode Type Description
-1 No trigger Worksheet is not triggered. Defined trigger survives
0 interval trigger Triggering according to a set interval
1 Value change trigger triggers if the value of the trigger address changes
2 Conditional trigger triggers if trigger address has the specified value when checking
3 Edge trigger Cyclically checks the value according to a set interval at the set address and starts the logging when the value becomes equalt to the reference value. Logging is triggered once each time at the transition to the defined area (edge treatment)

Error List

Data has possibly been lost
When creating or saving documents with LibreOffice Calc.
Sollution: Sage the template file with an Excel application or save the file again with LibreOffice.

ReadExcelSheet: Read Template: Sheet “Worksheet” Row “line number” Column “column number”, PLC Addr: “PLC Address”, Type: “Entered type”
Error: The Address.RawType can not be used for the type specified.

For the entered PLC Address an incorrect type has been specified, see PLC addressing.

The addressed data area does not exist. “PLC Address”: Sheet “Worksheet Name” Row “line number” Cell “column number”
To be read PLC address does not exist.

The specified CPU Could not be found .: “PLC Address”: Sheet “Worksheet” Row “line number” Cell “column number”
Check the connection data to the PLC.

Copy template file to destination: “Error message”
An error occured when copying the template to the destination directory.

CreatePLCConnection: “Error message”
An error occured when opening the PLC connection.

ReadWrite Given Data: “error message”
An error occured when processing the template.

Read PLC Data “error message”
An error occured when obtaining the PLC variables.

PLC: “Status.Text”
An error occured when reading a PLC variable.