Reading S7 PLC Data to Excel: OPC, TIA, and Direct Methods

1. Problem Overview

Engineers frequently need to lift live values out of a SIMATIC S7 CPU into Microsoft Excel for two reasons: trending/logging and ad-hoc operator control (toggle bits for drives, valves, and lamps directly from a spreadsheet cell). The challenge is that Excel cannot speak any of the SIMATIC backplane protocols (MPI/PROFIBUS, ISO-on-TCP/RFC1006, S7Comm, or PROFINET) natively. A translation layer is required.

Six practical translation paths exist for S7-300/400/1200/1500 CPUs:

1. OPC DA classic via a third-party server (e.g., KEPServerEX) + Excel OPC-DA client add-in.
2. OPC UA via the Siemens SIMATIC S7-1500 OPC UA Server (integrated in firmware) or the SIMATIC NET OPC UA Server.
3. TIA Portal DB snapshot — manually grab online values from a configured DB.
4. Standalone DB-to-xlsx exporter (open-source utilities that walk the S7 project and write a formatted workbook).
5. Direct ISO-on-TCP via Snap7/libnodave — a custom Excel VBA macro or .NET VSTO add-in that reads/writes the CPU directly over port 102.
6. Legacy DDE using Siemens' MicroComputing for S7-200 or older Prodave/DDE packages — retained only for legacy migrations.

The remainder of this article covers prerequisites, configuration, bit-toggling, performance limits, and a verification matrix for each option.

2. Architecture and Protocol Stack

Excel is the client. It does not talk to the PLC; the server does. The server translates between the SIMATIC transport (S7Comm/ISO-on-TCP) and a Windows inter-process mechanism (DDE, COM/OPC DA, or OPC UA TCP binary).

3. Prerequisites

• Excel 2016 or later (32-bit recommended for older COM/OPC DA add-ins; 64-bit works for OPC UA and Snap7 .NET wrappers).
• Static IP on the engineering PC in the same subnet as the CPU (e.g., PC 192.168.0.10, CPU 192.168.0.1).
• PG/PC interface set to S7ONLINE → TCP/IP → Intel(R) xxx with the correct access point selected in Set PG/PC Interface.
• CPU PUT/GET enabled. In TIA Portal, under CPU Properties → Communication → Access to the CPU via PUT/GET the option "Permit access with PUT/GET communication from remote partner" must be checked. Without this, ISO-on-TCP and OPC DA reads still work for the configured connection, but PUT/GET bit writes from Excel will be rejected.
• CPU protection level configured at least to Full access (no protection) for the OPC UA server or at Read/Write for OPC DA write access.
• Firewall rule permitting inbound TCP 102 (ISO-on-TCP), TCP 4840 (OPC UA), and outbound TCP 135 + DCOM range for legacy OPC DA.
• For S7-1200/1500 OPC UA: CPU firmware ≥ V2.0 for S7-1500 (V2.5 recommended); S7-1200 ≥ V4.4 for server capability.

4. Option A — OPC DA via KEPServerEX (Recommended for Legacy S7-300/400)

4.1 Install and license

1. Download KEPServerEX v6.x from the Kepware product page: KEPServerEX — OPC Server Connectivity Platform. The installer bundles the Siemens TCP/IP Ethernet driver.
2. Apply a runtime license. The installer ships a 2-hour demo; for production you need a paid runtime key bound to the host MAC.
3. Start the KEPServerEX Configuration application and add a Siemens TCP/IP Ethernet channel. Use Default scan mode (100 ms) and Do not generate for tag generation.

4.2 Configure the device

Add a device under the channel with these critical parameters:

4.3 Add tags

Tags use the syntax DB1.DBD0 (DB number, dot, element). Examples for a typical recipe DB (DB10):

DB10.DBD0   REAL    Setpoint_temperature
DB10.DBD4   REAL    Setpoint_pressure
DB10.DBW8   INT     Cycle_counter
DB10.DBX10.0  BOOL  Pump_run
DB10.DBX10.1  BOOL  Valve_open
DB10.DBX12.0  BOOL  Lamp_red
DB10.DBX12.1  BOOL  Lamp_green

Address syntax varies by data type: DBx.DBb<y> for bits, DBx.DBB<y> for bytes, DBx.DBW<y> for words, DBx.DBD<y> for doublewords.

4.4 Read with Excel (live)

Use the free OPC Data Client or any DDE-aware add-in. In KEPServerEX, click Tools → Launch OPC Quick Client to verify tags are good. For Excel, the canonical method is the MatrikonOPC Excel Add-in or a custom VBA macro referencing the OPC DA Automation 2.0 wrapper:

' VBA — requires reference: OPC Automation 2.0 Type Library
Dim WithEvents svr As OPCServer
Dim grp As OPCGroup

Sub Connect()
  Set svr = New OPCServer
  svr.Connect "Kepware.KEPServerEX.V6"
  Set grp = svr.OPCGroups.Add("ExcelGrp")
  grp.UpdateRate = 250
  grp.IsSubscribed = True
  grp.DataChangeInterval = 250
End Sub

Sub AddTag(cellAddr As String, opcItem As String)
  Dim itm As OPCItem
  Set itm = grp.OPCItems.AddItem(opcItem, 0)
  itm.Read OPCDevice, 0   ' initial read
  Cells(Range(cellAddr).Row, Range(cellAddr).Column).Value = itm.Value
End Sub

Private Sub grp_DataChange(ByVal TransactionID As Long, _
                            ByVal NumItems As Long, _
                            ClientHandles() As Long, _
                            ItemValues() As Variant, _
                            Qualities() As Long, _
                            TimeStamps() As Date)
  Dim i As Long
  For i = 1 To NumItems
    Cells(1, i + 1).Value = ItemValues(i)
  Next i
End Sub

4.5 Toggle a bit from Excel

Sub ToggleBit()
  Dim itm As OPCItem
  Set itm = grp.OPCItems.Item("DB10.DBX10.0")
  Dim newVal As Boolean
  newVal = Not CBool(itm.Value)
  itm.Write newVal            ' write back to PLC
End Sub

Bind the macro to a worksheet button or to the Worksheet_Change event on a specific cell to make the spreadsheet itself act as a one-bit HMI.

5. Option B — OPC UA (Recommended for S7-1200/1500)

5.1 Enable the server in TIA Portal

S7-1500 CPUs with firmware V2.0+ ship with an integrated OPC UA server. To activate:

1. Open the device view for the CPU.
2. Navigate to Properties → OPC UA → Server.
3. Check "Activate OPC UA Server".
4. Set "Server port" to 4840 (default) or 4841 for second instance.
5. Define security policies: None (testing only) or Basic128Rsa15 / Basic256Sha256 (production). For new deployments use Basic256Sha256; Basic128Rsa15 is deprecated.
6. Add the DB tags you want exposed under OPC UA Server → Companion specifications or by configuring the DB attributes Accessible from OPC UA.
7. Compile and download to the CPU.

Reference: SIMATIC S7-1500 OPC UA Server Function Manual (Siemens entry ID 109769630).

5.2 Consume from Excel

OPC UA over TCP/4840 does not natively speak to Excel. Wrap a UA .NET client inside a VSTO add-in or use an OPC UA-to-DA bridge. With the OPC Foundation .NET Standard UA Client stack, the relevant C# snippet is:

var cfg = new ApplicationConfiguration {
    ApplicationName = "ExcelClient",
    ApplicationUri = "urn:ExcelClient",
    ApplicationType = ApplicationType.Client,
    SecurityConfiguration = new SecurityConfiguration {
        ApplicationCertificate = new CertificateIdentifier { StoreType = "Directory", StorePath = "%CommonApplicationData%\\OPC Foundation\\CertificateStores\\MachineDefault" }
    },
    TransportConfigurations = new TransportConfigurationCollection(),
    TransportQuotas = new TransportQuotas { OperationTimeout = 15000 },
    ClientConfiguration = new ClientConfiguration { DefaultSessionTimeout = 60000 }
};
await cfg.LoadApplicationConfigurationAsync(false);
cfg.CertificateValidator.AutoAcceptUntrustedCertificates = true;  // testing only

using var endpoint = CoreClientUtils.SelectEndpoint("opc.tcp://192.168.0.1:4840", useSecurity: false);
using var session = await Session.Create(cfg, endpoint, false, "ExcelOPC", 60000, null, null);

var readValue = session.ReadValue("ns=3;s=\"DB10\".\"Setpoint_temperature\"");
Console.WriteLine($"Temp = {readValue}");

5.3 Bit toggle from Excel button

// write Boolean true to the lamp tag
var nodes = new WriteValueCollection {
    new WriteValue {
        NodeId = new NodeId("ns=3;s=\"DB10\".\"Lamp_red\"", 3),
        AttributeId = Attributes.Value,
        Value = new DataValue(new Variant(true))
    }
};
var resp = await session.WriteAsync(null, nodes, CancellationToken.None);
Console.WriteLine($"Result: {resp.Results[0]}");

Expose both ReadValue and WriteAsync as COM-visible methods on the VSTO add-in so VBA in the Excel sheet can call them through Application.Run.

6. Option C — TIA Portal Online DB Snapshot to Excel

For one-off dumps (no live updates), TIA Portal can export every online DB value:

1. Open the project in TIA Portal V16 or later.
2. Go online to the target CPU.
3. Open the DB in the project tree, right-click → "Snapshot" (or "Monitor/Modify" → Snapshot of all values).
4. Select "Update values" in the snapshot toolbar.
5. Right-click the snapshot value column → "Copy".
6. Paste into Excel — the columns come through in DB element order with the declared names.

This method is read-only and intended for diagnostics, not for closing the loop. Excel ↔ CPU write-back requires OPC or direct TCP.

7. Option D — DB-to-xlsx Exporter Utilities

Standalone tools parse the S7 project file (a .s7p/.ap archive or a TIA Portal .alsub) and write a formatted workbook. Two common picks:

• Step7 Db To Excel on SourceForge — reads STEP 7 V5 project DBs and exports each DB to a separate .xlsx sheet, including symbolic names and comments. Free, MIT-style license.
• Simatic DB-Exporter — a commercial Windows utility; supports STEP 7 V5 and TIA V13+.

These produce a static dump; they do not maintain a live link to the CPU. Use them for documentation and code review rather than control.

8. Option E — Direct ISO-on-TCP with Snap7 / libnodave

For engineers who want zero third-party licensing and full read/write control, the open-source libraries Snap7 and libnodave speak S7Comm/ISO-on-TCP directly. From Excel VBA you load the DLL via Declare and call into it.

8.1 Snap7 example (C# helper, COM-exposed to VBA)

using Snap7;

public class S7ClientHelper {
    private S7Client client = new S7Client();

    public bool Connect(string ip, int rack, int slot) {
        int rc = client.ConnectTo(ip, rack, slot);
        return rc == 0;
    }

    public float ReadReal(int dbNumber, int byteOffset) {
        byte[] buf = new byte[4];
        client.DBRead(dbNumber, byteOffset, 4, buf);
        return BitConverter.ToSingle(buf.Reverse().ToArray(), 0);  // S7 is big-endian
    }

    public void WriteBool(int dbNumber, int byteOffset, int bitOffset, bool value) {
        client.DBWrite(dbNumber, byteOffset + bitOffset / 8, 1,
                       new[] { value ? (byte)1 : (byte)0 });
    }

    public void Disconnect() { client.Disconnect(); }
}

Register the helper as a COM-visible class; VBA calls:

Sub ReadTemp()
  Dim h As Object
  Set h = CreateObject("ExcelS7.S7ClientHelper")
  If h.Connect("192.168.0.1", 0, 2) Then
    Range("B2").Value = h.ReadReal(10, 0)   ' DB10.DBD0 REAL
    h.WriteBool 10, 12, 0, True             ' DB10.DBX12.0 lamp on
  End If
End Sub

8.2 Connection parameters by CPU family

9. Option F — Legacy DDE (S7-200 / MicroComputing)

DDE (Dynamic Data Exchange) is deprecated but still functional on Windows 10/11 for the S7-200 line. The Siemens product MicroComputing embeds a DDE server that exposes tags named DB1.DW0 and bit references as V100.0. Excel binds these via the DDE function:

=S7Micro|Database!DB1.DW0

For S7-300/400, the older Prodave library was historically used with custom DDE wrappers. These are no longer recommended; they require a 32-bit Office and are not supported on 64-bit Excel.

10. Bit Toggle Reference (All Paths)

When toggling a bit, read-modify-write the entire byte in your code; partial writes from Excel will overwrite neighbouring bits in the same byte.

11. Performance, Throughput, and CPU Limits

• S7-300/400 CPU connection resources: typically 4–16 simultaneous PG/OP/AS connections depending on the CPU order number. The OPC server or Snap7 client consumes one.
• S7-1500 OPC UA server: max sessions = 64 (firmware V2.5); max monitored items per session = 2000; max subscriptions = 100. See 109769630.
• KEPServerEX throughput: up to 10,000 tags at 100 ms scan, scale with CPU and license.
• Excel poll loop in VBA: 250 ms is a practical floor; faster rates saturate COM and CPU without user-visible benefit.
• S7-1200 PUT/GET: limit of 8 PUT/GET connections by default (configurable up to 16) — TIA Portal CPU Properties → Communication → Connection resources.

12. Security Considerations

• OPC DA relies on DCOM — it uses Windows authentication and is therefore exposed to credential-theft attacks. Restrict via Windows firewall and DCOMCNFG.
• OPC UA supports X.509 certificates, message signing, and encryption (Basic256Sha256). Use it for any link crossing a process or zone boundary.
• S7Comm over TCP 102 has no native security; isolate the engineering network on a separate VLAN.
• Disable PUT/GET on S7-1200/1500 if you only need to read; enforce it via the CPU access level (write password).

13. Verification Checklist

1. Open the OPC Quick Client (KEPServerEX) or UA Expert (OPC Foundation) and confirm tags update live at the configured scan rate.
2. From Excel, write a known value (e.g., 42) to a DBW and read it back via TIA Portal Monitor/Modify.
3. Toggle a bit from Excel and verify the corresponding lamp/contact in the panel.
4. Disconnect the network cable and confirm Excel flags a quality code OPC_QUALITY_BAD (192) instead of holding stale data.
5. Restart the OPC server and verify Excel auto-reconnects within the configured keep-alive window.
6. Re-run with Windows firewall turned on; on OPC DA, confirm the DCOM ports are reachable.

14. Troubleshooting Matrix

15. Selection Guide

• S7-300/400, single workstation, <100 tags, existing KEPServerEX license → Option A.
• S7-1500, modern security requirements, multi-client → Option B (OPC UA).
• One-time documentation dump, no live link → Option C (TIA snapshot) or Option D (DB exporter).
• Free, full read/write, no third-party license → Option E (Snap7 + VSTO).
• S7-200 with active MicroComputing install → Option F only as a stop-gap; plan migration to OPC UA.