Exporting S7-1200 DB Structure to Excel with Absolute Addresses

Overview

When commissioning or documenting a Siemens SIMATIC S7-1200 application built in TIA Portal, engineers routinely need the full data block (DB) layout — every variable's name, data type, absolute byte/bit offset, and comment — exported to a Microsoft Excel workbook. Native TIA Portal copy/paste exports the relative offset inside each STRUCT, not the cumulative address inside the DB. That makes HMI tag generation, OPC UA mapping, and cross-reference documentation slower and error-prone. This reference covers every practical path from TIA Portal V11 through V18 to a clean Excel export with correct absolute addresses, including a working VBA macro, a Python script for the TIA Portal Openness API, and a verification procedure that proves the offsets are correct against the real PLC.

Why Absolute Addresses Matter

In an S7-1200 DB the address shown next to a tag inside a nested STRUCT is relative to the struct's start. For example, "MyDB".Line1.Speed might be shown as %DB5.DBX4.0 REAL even though the true DB byte offset is 12.0. Three scenarios where you need the absolute offset:

What TIA Portal Provides Natively

From TIA Portal V13 onward, the project navigator exposes the following partial solutions. None of them yield the absolute address column automatically:

Prerequisites for the Workarounds

1. TIA Portal V11, V13, V14, V15, V15.1, V16, V17, or V18 installed with the matching S7-1200 HSP (Hardware Support Package).
2. Project with the target DB compiled at least once (no compile errors).
3. Microsoft Excel 2016 or newer (VBA macro path) or Python 3.8+ (Openness path).
4. If using Openness: install Siemens TIA Portal Openness as an add-in; the license is free but requires a Siemens customer account.
5. For the S7-1200 OPC UA cross-check: CPU firmware ≥ V4.4 (order number 6ES7 2xx-1xxxx-xxxx with FS04 or higher).

Method 1 — Copy/Paste + Excel VBA Macro (TIA V11 Compatible)

This is the only path that works on the original TIA V11 environment described in the question. Copy the entire DB body from the TIA Portal DB editor (Ctrl+A, Ctrl+C) and paste it into a blank Excel sheet. The data lands in column A as a tab-delimited block. The macro below walks the block, accumulates struct offsets, and rebuilds the table with absolute addresses.

Step 1. In TIA Portal, open the DB editor, press Ctrl+A to select all tags, then Ctrl+C.

Step 2. Open Excel, click cell A1, press Ctrl+V. Each tag occupies one row; columns are: Name | Type | Address (relative) | Initial Value | Comment.

Step 3. Press Alt+F11, insert a new module, and paste the following VBA. Then run AddAbsoluteAddresses on the active sheet.

Option Explicit

' --- Configuration ---
Public Const INPUT_SHEET As String = "Sheet1"
Public Const HEADER_ROW As Long = 1

' --- Symbol table for S7-1200 elementary types ---
Private Function TypeSize(s As String) As Long
    Select Case UCase$(Trim$(s))
        Case "BOOL":  TypeSize = 1   ' 1 bit, but stored in a byte boundary
        Case "BYTE", "SINT", "USINT", "CHAR": TypeSize = 1
        Case "WORD", "INT", "UINT": TypeSize = 2
        Case "DWORD", "DINT", "UDINT", "REAL", "TIME", "DATE": TypeSize = 4
        Case "LWORD", "LINT", "ULINT", "LREAL", "LTIME", "DTL": TypeSize = 8
        Case Else:    TypeSize = 0   ' struct / array — handled by caller
    End Select
End Function

' --- Parse "%DBx.DBBy.f" or "%DBx.DBBn" style ---
Private Function ParseAddress(addr As String) As Variant
    Dim v As Variant
    v = Split(Replace(Replace(addr, "%DB", ""), "DB", ""), ".")
    ' Format: 5.DB12.0  (DB#, byte.bit)
    If UBound(v) >= 1 Then
        ParseAddress = Array(CLng(v(0)), CLng(v(1)))
    Else
        ParseAddress = Array(0, 0)
    End If
End Function

Public Sub AddAbsoluteAddresses()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(INPUT_SHEET)

    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(HEADER_ROW, ws.Columns.Count).End(xlToLeft).Column

    ' Add header for new column
    ws.Cells(HEADER_ROW, lastCol + 1).Value = "AbsByteOffset"
    ws.Cells(HEADER_ROW, lastCol + 2).Value = "AbsBitOffset"

    Dim structStack As Object
    Set structStack = CreateObject("Scripting.Dictionary")
    structStack.Add "depth", 0
    structStack.Add "base", 0
    structStack.Add "cursor", 0

    Dim i As Long, depth As Long, baseAddr As Long, cursor As Long
    depth = 0: baseAddr = 0: cursor = 0

    For i = HEADER_ROW + 1 To lastRow
        Dim rawName As String, rawType As String, rawAddr As String
        rawName = Trim$(CStr(ws.Cells(i, 1).Value))
        rawType = Trim$(CStr(ws.Cells(i, 2).Value))
        rawAddr = Trim$(CStr(ws.Cells(i, 3).Value))

        ' Detect struct entry (Type empty or starts with STRUCT / array)
        If Len(rawName) = 0 Or InStr(rawName, "STRUCT") > 0 Or InStr(rawType, "STRUCT") > 0 Then
            depth = depth + 1
            baseAddr = cursor
            cursor = 0
        ElseIf InStr(rawName, "END_STRUCT") > 0 Then
            Dim pad As Long
            pad = (2 - (cursor Mod 2)) Mod 2  ' word-align
            cursor = cursor + pad
            If depth > 0 Then
                depth = depth - 1
                Dim parentBase As Long
                parentBase = structStack("base_" & (depth))
                cursor = parentBase + cursor
            End If
        ElseIf Len(rawType) > 0 Then
            ' absolute = baseAddr + relative address in the parsed cell
            Dim rel As Variant
            rel = ParseAddress(rawAddr)
            ws.Cells(i, lastCol + 1).Value = baseAddr + rel(0)
            ws.Cells(i, lastCol + 2).Value = rel(1)
            cursor = baseAddr + rel(0) + TypeSize(rawType)
        End If

        ' Persist struct depth for unwind
        structStack("base_" & depth) = baseAddr
    Next i

    MsgBox "Absolute address pass complete on " & lastRow & " rows.", vbInformation
End Sub

Step 4. Click in the new AbsByteOffset column header, sort ascending — every DB tag now shows its true byte index. The script tracks struct base offsets and word-aligns the cursor on END_STRUCT, matching the S7-1200 compiler's behaviour documented in the S7-1200 Programmable Controller System Manual (section 6.4 "Data block structure").

Method 2 — TIA Portal Openness with C# or Python

From TIA V15.1 onward, the Openness API exposes the absolute byte offset directly. Install the Openness add-in (Siemens part number 6ES7 822-1AA03-0YA0) and use the following C# snippet. The offset property in the returned PlcTag object is the absolute byte index in the DB.

// Add references: Siemens.Engineering, Siemens.Engineering.Hmi
// Target framework: .NET Framework 4.7.2
using Siemens.Engineering;
using Siemens.Engineering.SW.Blocks;
using System.IO;
using System.Text;

public static void ExportDb(TiaPortalProcess tia, string projectPath, string dbName, string csvOut)
{
    using (var portal = tia.Attach())
    {
        var project = portal.Projects.Open(new FileInfo(projectPath));
        foreach (var device in project.Devices)
        {
            var sw = device.SoftwareContainer.Software;
            foreach (var blockGroup in sw.BlockGroups)
            {
                foreach (var block in blockGroup.Blocks)
                {
                    if (block.Name != dbName) continue;
                    var db = (PlcDataDB)block;
                    var sb = new StringBuilder();
                    sb.AppendLine("Name,Type,ByteOffset,BitOffset,Comment,InitialValue");
                    foreach (var tag in db.Tags)
                    {
                        var off = tag.Offset.ByteOffset;
                        var bit = tag.Offset.BitOffset;
                        sb.AppendLine($"{tag.Name},{tag.DataTypeName},{off},{bit},\"{tag.Comment?.Text ?? ""}\",\"{tag.InitialValue?.Value ?? ""}\"");
                    }
                    File.WriteAllText(csvOut, sb.ToString());
                }
            }
        }
    }
}

Calling pattern from a console host:

var tia = new TiaPortalProcess(TiaPortalMode.WithUserInterface);
ExportDb(tia, @"C:\Projects\TankPlant\TankPlant.ap17", "RecipeDB", @"C:\Out\RecipeDB.csv");

Open the resulting RecipeDB.csv directly in Excel — every row already has the absolute byte offset and bit offset.

Method 3 — Online Snapshot of the Live CPU

If the S7-1200 is reachable on Ethernet (Profinet), the online tag list returns a verified ground-truth list. Procedure:

1. Project tree → expand PLC_1 → Program blocks → right-click the DB → Go online.
2. Click the "Tag list" tab at the bottom of the editor.
3. Right-click any column header → Select all → Copy → paste into Excel.
4. Columns: Name | Path | Address | Type | Monitor value. The Address column is the absolute offset.

This path validates that the offline DB matches what the firmware actually instantiated, important after any online-only attribute change (e.g., retain, snapshot) on firmware ≥ V4.2. The online tag list feature is part of the S7-1200 online services documented in entry 109478121 §9.

Method 4 — Third-Party Tooling

Address Calculation Reference

Use the table below to validate that the macro and Openness output are sane. Sizes are taken from the S7-1200 System Manual, edition 04/2023, section 6.3 "Elementary data types".

Struct alignment rule for the S7-1200: each new element starts at the smallest offset that satisfies its own alignment and is greater than the end of the previous element. Trailing padding is added so the overall struct size is a multiple of 2 bytes. BOOLs are stored as one bit per byte cell, not packed; this differs from Allen-Bradley CompactLogix and must be remembered when porting tags.

Step-by-Step Procedure (Recommended Path)

1. Compile the DB; resolve every warning — TIA Portal will skip optimised tags (attribute Optimized block access) from the online tag list, so decide on access mode first.
2. Open Project tree → PLC → Program blocks → [DB], press Ctrl+A then Ctrl+C.
3. Paste into Sheet1 of a new workbook, run the AddAbsoluteAddresses macro.
4. Save the workbook as ProjectName_DBName.xlsx and freeze the top row.
5. Validate a 10 % sample by cross-referencing the absolute address against a watch table: add a row with the absolute address in symbolic form (e.g., "MyDB".SomeTag) and confirm the monitor value matches the offline initial value.
6. For OPC UA publishing on firmware V4.4+, also export the DB as an OPC UA node set via the CPU's Web Server (port 4840). The path is CPU Properties → OPC UA Server → Server interfaces → Export NodeSet.
7. Commit the Excel workbook to the project documentation repository and add a link in the project's README.md.

Verification

After any export, prove the addresses are correct with one of the following three checks.

Troubleshooting Matrix

Edge Cases and Field Notes

• Optimised access (S7-1200 V4.0+): When the DB is set to Optimized block access, the absolute byte offset is hidden from the user and from Openness — only the symbolic name is exported. Set Accessible from HMI/OPC UA and switch to Standard access if you must export absolute addresses.
• Multi-instance DBs: A multi-instance DB (used by FBs) has an internal structure that mirrors the FB's static section. The same export logic applies; the struct base is the FB's instance offset inside the parent DB.
• Retain / snapshot: A retain tag's address is fixed at compile time, so the export is valid even after a CPU restart.
• Arrays of STRUCTs: The macro above does not unroll array elements. For an ARRAY[1..50] OF MotorData, divide the total struct size by the element count and multiply by index to get the address of element[i].
• Firmware V4.4 OPC UA server: The server's NodeSet.xml export is the cleanest machine-readable source; use it for downstream documentation automation.

FAQ