WinCC VBScript: Read Excel Values and Write Tags via HMIRuntime

Overview

WinCC (TIA Portal, WinCC Professional, RT Professional, and the legacy WinCC / PCS 7 V7.x faceplates) exposes a VBScript runtime that can drive Microsoft Excel through the Excel.Application COM object and exchange data with the tag database through the HMIRuntime object. A common field requirement is to load a recipe, parameter setpoint, or test matrix from a spreadsheet and write the cell values into internal or external WinCC tags without a CSV import wizard. This reference describes the complete VBScript pattern: opening an Excel workbook, reading UsedRange data, comparing two columns, highlighting mismatched rows in red/green, and writing validated values back to the WinCC tag database through HMIRuntime.Tags(...).Write.

The reference is written for PCS 7 V7.0 SP3 / V8.x faceplates but applies to all WinCC runtimes that ship the VBScript engine, including WinCC Comfort, WinCC RT Advanced, and WinCC RT Professional. Script execution is triggered from a button event (OnLButtonDown), from a scheduled Global Script action, or from a C-script callback. The patterns below are valid for all three.

Prerequisites

1. Software: PCS 7 V7.0 SP3 or higher, WinCC Explorer, Microsoft Office 2007 / 2010 / 2013 / 2016 / 2019 / 365 installed on the Runtime station.
2. Licensing: The WinCC Runtime license must permit VBS scripting (standard on every WinCC RT and PCS 7 OS). No additional license key is required for Excel COM automation.
3. Tags: The destination tags (Tag1, Tag2, ...) must exist in the WinCC tag database and be reachable in Runtime. Internal binary tags work without PLC connection; external tags require an active S7 / OPC channel.
4. File path: The Excel workbook (D:\Excel file.xls in the example below) must be reachable from the Runtime account. On a WinCC service-account install, prefer C:\Recipes\ with read/write NTFS rights for the CCAdmin / WinCCUser service identity.
5. DCOM security: If the Runtime runs as a Windows service (WinCC OS Server), grant the WinCC service account Launch/Activation permissions for the Excel COM application via dcomcnfg → Component Services → Computers → DCOM Config → Microsoft Excel Application.
6. Graphic object: A button graphic on the target screen/picture that fires the script on OnLButtonDown. The script can also be fired from a Global Script Action (cyclic or tag-triggered).

HMIRuntime Object Model and Tag Access

The HMIRuntime object is the root namespace for all VBS actions in WinCC Runtime. It exposes the Tags collection, the ScreenItems collection (for screen-bound I/O fields, bars, sliders, etc.), and the DataSet interface (WinCC RT Professional and WinCC V7.4+).

When tags are linked automatically by a PCS 7 faceplate library (e.g. CTRL_PID, CTRL_DOSE, VALVE_ANA), the same HMIRuntime.Tags("...") accessor reaches the structured Process Tag; no screen I/O field is needed. The Set objTag = HMIRuntime.Tags("...") call does not require objTag.Read for write-only flows, but calling .Read first is mandatory when the tag value is read before being overwritten.

Excel COM Automation in WinCC

Excel is driven through the classic COM IDispatch interface. The three mandatory object references are:

1. objExcelApp – Excel.Application instance.
2. objWorkSheet – a specific Worksheet inside objExcelApp.ActiveWorkbook.
3. objWorkSheet.Cells(row, col) – a single cell (1-based row and column indices).

The lifecycle of objExcelApp must be managed carefully. The WinCC script host is a long-lived process; an Excel instance that is not properly closed with .Quit and unhooked with Set ... = Nothing will accumulate as ghost processes and eventually block the station. Every reference collected below must be released, not only the top-level objExcelApp.

Basic Read/Write Code Structure

The minimal working pattern reads two cell values and writes them into two WinCC tags. This is the canonical snippet that ships with the Siemens support entry 37572697 and is the basis for the comparison variant below.

' Triggered on button mouse-down event on the WinCC picture
Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
    Dim objExcelApp
    Dim objWorkSheet
    Dim objTag1, objTag2
    Dim intRet

    Set objExcelApp  = CreateObject("Excel.Application")
    objExcelApp.Visible = True                       ' set False in production
    objExcelApp.Workbooks.Open "D:\Excel file.xls"
    Set objWorkSheet  = objExcelApp.ActiveWorkbook.Worksheets(1)

    Set objTag1 = HMIRuntime.Tags("Tag1_name")
    Set objTag2 = HMIRuntime.Tags("Tag2_name")

    objTag1.Value = objWorkSheet.Cells(2, 4).Value   ' D2
    objTag2.Value = objWorkSheet.Cells(3, 4).Value   ' D3

    intRet = objTag1.Write
    intRet = objTag2.Write

    objExcelApp.ActiveWorkbook.Save
    objExcelApp.Workbooks.Close
    objExcelApp.Quit

    Set objWorkSheet = Nothing
    Set objTag1      = Nothing
    Set objTag2      = Nothing
    Set objExcelApp  = Nothing
End Sub

What each line does

For screen-bound writes (no PLC, only I/O field), the RT Professional equivalent is documented at Examples of VBS — RT Professional and uses ScreenItems("IOField1").Value = objExcelApp.Cells(1,1).Value.

Iterating UsedRange and Comparing Cells

For a multi-row recipe or test matrix, hard-coding cell coordinates does not scale. UsedRange returns the rectangle that bounds all populated rows and columns. Two important properties:

• UsedRange.Rows.Count — the number of populated rows, starting from the first non-empty row.
• UsedRange.Columns.Count — the number of populated columns, starting from the first non-empty column.

Always declare loop counters with Dim i; missing the Dim statement silently creates an implicit Variant and breaks string vs numeric comparison.

Sub CompareExcelColumns()
    Dim objExcelApp, objWorkSheet
    Dim i, a, b, intBad

    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = False
    objExcelApp.Workbooks.Open "D:\Recipes\test_matrix.xls"
    Set objWorkSheet = objExcelApp.ActiveWorkbook.Worksheets(1)

    a = objWorkSheet.UsedRange.Rows.Count
    b = objWorkSheet.UsedRange.Columns.Count
    intBad = 0

    For i = 2 To a            ' assume row 1 is the header
        If (objWorkSheet.Cells(i, 3).Value <> _
            objWorkSheet.Cells(i, 4).Value) Then
            ' mismatch on row i
            objWorkSheet.Cells(i, 5).Interior.ColorIndex = 3   ' red
            intBad = intBad + 1
        Else
            objWorkSheet.Cells(i, 5).Interior.ColorIndex = 4   ' green
        End If
    Next

    HMIRuntime.Tags("MISMATCH_COUNT").Value = intBad
    HMIRuntime.Tags("MISMATCH_COUNT").Write

    objExcelApp.ActiveWorkbook.Save
    objExcelApp.Workbooks.Close
    objExcelApp.Quit
    Set objWorkSheet = Nothing
    Set objExcelApp  = Nothing
End Sub

ColorIndex reference (Excel default palette)

Highlighting Mismatched Cells

The highlighting line objWorkSheet.Cells(i, 5).Interior.ColorIndex = 3 is the minimum-effort path. For more advanced visual feedback, use:

' Custom color (BGR-encoded in older Excel, RGB in modern Excel)
objWorkSheet.Cells(i, 5).Interior.Color = RGB(255, 102, 102)    ' red tone

' Add a comment for traceability
objWorkSheet.Cells(i, 5).AddComment _
    "Mismatch detected at " & Now & " by WinCC Runtime"

' Bold the result column
objWorkSheet.Cells(i, 5).Font.Bold = True

For extremely large sheets, write the color result directly into a WinCC tag instead of coloring the cell, to keep the COM round-trip time predictable. WinCC Runtime COM calls to Excel are synchronous on the script thread and block the UI for the duration of the call; a 10 000-row UsedRange scan can take 3-8 seconds.

Robust Error Handling and Resource Cleanup

WinCC VBS does not support On Error Resume Next reliably when COM objects are involved, because an error in a COM call leaves the reference half-initialized. Use the structured pattern below, which checks object states and forces Excel termination through a separate cleanup routine.

Sub OnLButtonDown(ByVal Item, ByVal Flags, ByVal x, ByVal y)
    Dim objExcelApp, objWorkSheet
    Dim objTag1, objTag2
    Dim blnExcelStartedHere

    blnExcelStartedHere = False

    On Error Resume Next

    ' Try to reuse an existing Excel session if one is already running
    Set objExcelApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Clear
        Set objExcelApp = CreateObject("Excel.Application")
        blnExcelStartedHere = True
    End If
    On Error Goto 0

    If objExcelApp Is Nothing Then
        HMIRuntime.Trace "Excel COM unavailable - aborting"
        Exit Sub
    End If

    objExcelApp.Visible = False
    objExcelApp.Workbooks.Open "D:\Recipes\setpoint.xls"
    Set objWorkSheet = objExcelApp.ActiveWorkbook.Worksheets(1)

    Set objTag1 = HMIRuntime.Tags("SP_TEMP")
    Set objTag2 = HMIRuntime.Tags("SP_FLOW")

    objTag1.Value = CDbl(objWorkSheet.Cells(2, 4).Value)
    objTag2.Value = CDbl(objWorkSheet.Cells(3, 4).Value)

    objTag1.Write
    objTag2.Write

    ' ---- Cleanup ---------------------------------------------------
    On Error Resume Next
    objExcelApp.ActiveWorkbook.Save
    objExcelApp.Workbooks.Close
    If blnExcelStartedHere Then objExcelApp.Quit
    Set objWorkSheet = Nothing
    Set objTag1     = Nothing
    Set objTag2     = Nothing
    Set objExcelApp = Nothing
    On Error Goto 0
End Sub

HMIRuntime.Trace writes to the WinCC diagnostic file C:\Program Files\Siemens\Automation\WinCC\Diagnosis\WinCC_Sys_<date>.log. Every error path must leave a trace line, otherwise post-mortem analysis on a customer machine is impossible.

File Path, Format and Office Version Considerations

Use absolute paths or UNC paths. Workbooks.Open "Excel file.xls" is resolved against the current directory of the WinCC Runtime process, which is typically C:\Program Files\Siemens\Automation\WinCC\bin and almost never the location of your recipe file. Also confirm that the Runtime service account has Read/Write on the folder; missing NTFS rights are the single most common root cause of -2147024891 (0x80070005 Access denied).

Performance and Runtime Considerations

• Synchronous COM blocking: Every Cells(...).Value is a cross-process marshaling call. Reading 1 000 cells takes ~1.5 s; reading 50 000 cells takes > 60 s and will be observed as a frozen WinCC screen. Read into a 2D Variant array in one call: arr = objWorkSheet.UsedRange.Value.
• Async / background tasks: For long-running Excel work, fire the script from a Global Script action (cyclic 1 s) and check a trigger tag; do not block the UI thread.
• Process accounting: Excel does not always release file locks. Always .Save, .Close, .Quit, and Set objExcelApp = Nothing in that order.
• Anti-virus / EDR: Some EDR products intercept CreateObject("Excel.Application") as suspicious behavior. Whitelist the WinCC Runtime directory (C:\Program Files\Siemens\Automation\WinCC\bin) in the EDR policy.
• Excel DCOM on remote OS server: On a PCS 7 OS Server accessed from a thin client, Excel must be licensed per-device or per-user under the same identity that runs WinCC. A failing COM call returns 0x80040154 (Class not registered) or 0x80080005 (server execution failed).

Fast bulk read pattern (preferred for > 100 rows)

Dim arrData
arrData = objWorkSheet.UsedRange.Value       ' 2D Variant, 1-based
Dim i, intRows
intRows = UBound(arrData, 1)

For i = 2 To intRows
    HMIRuntime.Tags("SP_" & CStr(i)).Value = arrData(i, 4)
    HMIRuntime.Tags("SP_" & CStr(i)).Write
Next

Verification and Diagnostics

1. Trace output: Insert HMIRuntime.Trace "Cell D2=" & objWorkSheet.Cells(2,4).Value after the read and confirm the value in the WinCC diagnostic file.
2. Tag check: Open WinCC Explorer → Tag Management → right-click the target tag → Properties → confirm the Last Value field updates after the script fires.
3. PLC round trip: For external tags, monitor the corresponding DB / input word in STEP 7 / TIA Portal with an online watch table. The WinCC address is computed from the configured channel; for S7-300/400 it is DBxx.DBWy, for S7-1500/1200 it is %DBxx.DBWy%.
4. Process Historian: PCS 7 tags written through VBS produce a history entry; query Tag Logging to verify the timestamp and value.
5. Excel side: After .Save, re-open the workbook manually and confirm that the cell color has been applied to the rows that the script flagged.

Common error codes