Backlinks Add to book Export to PDF Rename Page Book Creator Add this page to your book Book Creator Remove this page from your book Manage book (0 page(s)) Help S7-To-Excel-Tool © 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. Features of the Editions 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 Working with Worksheets 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. Process of the S7-To-Excel-Tool sample: template result Screenshots File settings: Notifications: Use the USB-Dongle 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: minimum .NET Framework V 4.6.0 (check version / install) install MARX "CBIOS Server Windows" as a service" plug in the Dongle only using S7-FileLogger: install S7-FileLogger for Dongle start the program Under you will find the license informations Create Template 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. Activate Logging Launch the application and select the template. Then the available worksheets are presented to you. By clicking you start the logging. In the table you can find the last events. Here, the error messages are also displayed. User Interface Gray controls are not available at the moment. Insert License Generate your machine code or insert your license key Select Language Switch between German and English Information about the software Here you can find all information about the software and the currently used license data. Toolbar Opens the selected template file Saves all settings from the program and the template (Shortcut STRG+S) reloads the current template Trys to open the template file with the default program All found variables in all sheets will be triggered start all triggers stop all triggers green bar: shows if analysis was started gray bar: no analysis started Excel File 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 PLC 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!. Regions 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. If you click on the arrow, you will see the properties of the region: 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 “…” The following dialog for editing the region(s) opens: 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: 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 Result after triggering three times: 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 Result after triggering three times: 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 Result after triggering two times: 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 Result after triggering two times (Note: All not empty cells are written): Trigger General settings (File name selected): At click on the sheet name: 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 Keywords The configuration is done via the corresponding keywords. Syntax: $keyword,n0,n1,…,nx The parameters n0,n1,…,nx are stated as separate by ','. Worksheet "PLC Configuration" 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 Worksheets with Process Data 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: 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) Settings for S7-1200 / S7-1500 / LOGO! S7-1200/1500 The optimized block access needs to be deactivated in the data block attributes for access to the S7-1500 and S7-1200. In the S7-1500 must be enabled in the communication setting in addition to the PUT / GET access . How this works you see here (snapshot from TIA Portal) . LOGO! 1. Use the Logo Soft Comfort the IP address of a logo! PLCs: 2. Configure PLCs so that connections from an HMI device accepted the Logo!. To do so, go to “Tools- > Ethernet Connections” and then add a new connection. 3. Double-click on the newly created connection to access the properties. Select: Server Connection Local TSAP: 02:00 - 02:00 decentralized TSAP accept all connections. You can access DB1, inputs , outputs, flags , counters and timers with IP -S7 -LINK . Now put on “ Tools- > VM parameter map ” the variables that are to be transferred to the DB1. 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. Table of Contents Summary Features of the Editions Working with Worksheets Process of the S7-To-Excel-Tool Screenshots Use the USB-Dongle Create Template Activate Logging User Interface Toolbar Excel File Settings PLC Settings Regions Edit Region(s) Trigger Keywords Worksheet "PLC Configuration" Configuration of the PLC Connection Configuration of the Destination File Name Worksheets with Process Data Define Process Data from the PLC PLC Addressing Timestamp Region Define Triggers Settings for S7-1200 / S7-1500 / LOGO! S7-1200/1500 LOGO!