WinCC VBScript: Read and Write Excel Data from HMI Tags

Overview

WinCC Comfort, WinCC Advanced, and WinCC Professional (TIA Portal) runtimes expose a VBScript engine that can be triggered from HMI events (tag change, value change, click, scheduled task) or executed from a button, function key, or scheduled task configured under "Scheduled tasks." The script host supports COM automation, which means a script can launch Excel.Application as an out-of-process COM server, open a workbook, and either read cell values into HMI tags/text fields or write HMI tag values back into specific cells.

The pattern below is widely used on packaging lines, knife-head stations, recipe selection screens, and traceability stations where a maintenance engineer prefers to edit setpoints in Excel on a USB stick or a network share rather than inside the HMI project. The HMI tag (for example Golovka_nozha) acts as a discrete selector; when the operator chooses mode 1, 2, or 3, the script exchanges the corresponding cell with the workbook D:\Data.xlsx.

Prerequisites

1. TIA Portal project with HMI device configured. Create the HMI tags that will exchange data with Excel. For the example below, create an internal or external tag Golovka_nozha of type Int (16-bit signed) and a text tag Tag_2 of type String with sufficient length (recommended 50..255 characters) for the imported value.
2. Microsoft Excel installed on the Runtime PC for WinCC RT Advanced / Professional. Confirm the installed version with excel.exe /? or in Apps & features; Office 2016, 2019, 2021, and Microsoft 365 Apps for enterprise are all compatible with the COM interface used by the script.
3. File path that the Runtime can read and write. For local files use a path on the Runtime PC such as D:\Data\Data.xlsx. For network paths use UNC: \\PLCSVR\Recipes\Data.xlsx. Map the path to a user account that has write permission; the WinCC Runtime service normally runs under SiemensRuntimeUser or the local SYSTEM account.
4. Folder access rights. Grant the Runtime service account Modify rights on the folder containing the workbook. Without write permission, objExcelApp.ActiveWorkbook.Save returns error 0x800A03EC or silently fails.
5. Excel file is not open in protected view or in another process. If a user has the workbook open on the same machine, the script open call will succeed but the Save call will fail with a sharing violation. Force a single-instance writer pattern by always closing the workbook inside the script.

SmartTags() Function Reference

Inside a WinCC Runtime VBScript, every HMI tag is exposed through the SmartTags collection. The collection supports both read and write access; the tag's PLC connection direction is irrelevant because the script talks to the internal HMI tag database, not directly to the PLC.

For the bidirectional example the script uses an integer tag Golovka_nozha that the operator increments on the HMI through a numeric input field or stepper button. The script maps the integer value to a row index in Excel so that the operator can select recipe 1, recipe 2, or recipe 3 with a single button press.

Excel COM Object Model in WinCC

The VBScript host registers the standard Office COM type library at startup, so the Excel.Application ProgID resolves to the installed Excel instance. Each property and method call below is from the Microsoft Excel xx.0 Object Library.

Writing a Tag Value into an Excel Cell

The base pattern below writes a fixed value (9999) into cell C4 of D:\12.xlsx whenever the integer tag Tag_2 equals 3. This is the canonical write pattern and corresponds to the example from the operator's working script.

If SmartTags("Tag_2") = 3 Then
  Dim objExcelApp
  Set objExcelApp = CreateObject("Excel.Application")
  objExcelApp.Visible = False
  objExcelApp.DisplayAlerts = False
  objExcelApp.Workbooks.Open "D:\12.xlsx", False, False
  objExcelApp.Cells(4, 3).Value = 9999
  objExcelApp.ActiveWorkbook.Save
  objExcelApp.Workbooks.Close
  objExcelApp.Quit
  Set objExcelApp = Nothing
End If

Three enhancements tighten this script for a Runtime environment:

1. DisplayAlerts = False suppresses the "Save changes?" dialog that would otherwise block the Runtime if a user has touched the workbook.
2. The Open call uses named arguments: Open(fileName, UpdateLinks, ReadOnly). The third argument False opens the file in read/write mode, which is required because Save will fail otherwise.
3. The Set objExcelApp = Nothing release is the last line; the COM object goes out of scope and the WinCC script host releases its reference. Without it, the Excel process remains in the background until Runtime shuts down.

Reading an Excel Cell into an HMI Text Field

Reading mirrors the write path: open the workbook, read Cells(r, c).Value, assign the result to a string tag, and close the workbook. The script below reads row 1 column 1 (cell A1) when Golovka_nozha = 1 and writes the value into a string tag RecipeText that is bound to a text field on the screen.

Dim objExcelApp
If SmartTags("Golovka_nozha") = 1 Then
  Set objExcelApp = CreateObject("Excel.Application")
  objExcelApp.Visible = False
  objExcelApp.DisplayAlerts = False
  objExcelApp.Workbooks.Open "D:\Data.xlsx", False, True
  SmartTags("RecipeText") = CStr(objExcelApp.Cells(1, 1).Value)
  objExcelApp.Workbooks.Close False
  objExcelApp.Quit
  Set objExcelApp = Nothing
End If

The CStr() conversion is required because the cell can be numeric, date, or boolean and the HMI string tag expects a String Variant. Without the conversion, a numeric cell would write the value as a number into the tag, which then displays as an integer in the text field rather than the formatted number the operator expects.

Bidirectional Script: Mode 1, 2 Read / Mode 3 Write

The combined script implements the operator requirement: when the integer tag Golovka_nozha equals 1 or 2, the script reads cell A1 or A2 into the text tag; when it equals 3, the script writes the current HMI counter into cell A3. The example assumes a counter tag KnifeCount of type Int that the PLC increments on each cut.

Dim objExcelApp
Dim sFile
sFile = "D:\Data.xlsx"

Select Case SmartTags("Golovka_nozha")
  Case 1
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = False
    objExcelApp.DisplayAlerts = False
    objExcelApp.Workbooks.Open sFile, False, True
    SmartTags("RecipeText") = _
      CStr(objExcelApp.Cells(1, 1).Value)
    objExcelApp.Workbooks.Close False
    objExcelApp.Quit
    Set objExcelApp = Nothing

  Case 2
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = False
    objExcelApp.DisplayAlerts = False
    objExcelApp.Workbooks.Open sFile, False, True
    SmartTags("RecipeText") = _
      CStr(objExcelApp.Cells(2, 1).Value)
    objExcelApp.Workbooks.Close False
    objExcelApp.Quit
    Set objExcelApp = Nothing

  Case 3
    Set objExcelApp = CreateObject("Excel.Application")
    objExcelApp.Visible = False
    objExcelApp.DisplayAlerts = False
    objExcelApp.Workbooks.Open sFile, False, False
    objExcelApp.Cells(3, 1).Value = _
      CLng(SmartTags("KnifeCount"))
    objExcelApp.ActiveWorkbook.Save
    objExcelApp.Workbooks.Close False
    objExcelApp.Quit
    Set objExcelApp = Nothing
End Select

Robust Error Handling

A production Runtime script must survive missing files, locked workbooks, and malformed cells. Wrap the COM section in an On Error block and route failures to an HMI alarm tag so the operator can see that the exchange failed.

Dim objExcelApp
Dim sFile
Dim iRow
Dim sResult
Dim iErr

On Error Resume Next

sFile = "D:\Data.xlsx"
iRow  = SmartTags("Golovka_nozha")

If iRow < 1 Or iRow > 99 Then
  SmartTags("ExcelStatus") = 11   ' out-of-range row
  Exit Sub
End If

Set objExcelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
  SmartTags("ExcelStatus") = 12   ' Excel not installed
  Err.Clear
  Exit Sub
End If

objExcelApp.Visible      = False
objExcelApp.DisplayAlerts = False
objExcelApp.Workbooks.Open sFile, False, True

If Err.Number <> 0 Then
  SmartTags("ExcelStatus") = 13   ' open failed
  objExcelApp.Quit
  Set objExcelApp = Nothing
  Err.Clear
  Exit Sub
End If

sResult = CStr(objExcelApp.Cells(iRow, 1).Value)
iErr    = Err.Number

objExcelApp.Workbooks.Close False
objExcelApp.Quit
Set objExcelApp = Nothing

If iErr <> 0 Then
  SmartTags("ExcelStatus") = 14   ' cell read failed
  Err.Clear
Else
  SmartTags("ExcelStatus") = 0    ' OK
  SmartTags("RecipeText")  = sResult
End If

Map SmartTags("ExcelStatus") to a status text list in the HMI editor (0 = OK, 11 = bad row, 12 = Excel missing, 13 = open failed, 14 = cell error) so the operator sees a meaningful message instead of a generic Runtime error.

File Path and Storage Considerations

On WinCC Runtime PCs the service account under which WinCC Runtime runs (typically SiemensRuntimeUser or a custom service account) must be granted the appropriate file-system rights. A common commissioning failure is a script that works while a developer is logged in interactively but fails when Runtime is restarted by the autologon user, because the autologon session has no write permission to D:\Data.

For a network share, the COM call can hang for tens of seconds if the file server is unreachable. Wrap the Open call in a watchdog:

objExcelApp.Workbooks.Open sFile, False, True
If (Timer - t0) > 5 Then
  ' timeout: abort and release
  objExcelApp.Quit
  Set objExcelApp = Nothing
  SmartTags("ExcelStatus") = 15
  Exit Sub
End If

Triggering the Script from the HMI

The script must be associated with an event in the TIA Portal HMI editor. The most common triggers are:

• Value change on a tag: Configure on the tag's properties under "Events > Value change"; choose "Execute VBS function" and select the function that contains the script above. This fires the script every time the tag value changes in the Runtime.
• Click on a button: Wire the button's "Click" event to the VBS function. Suitable for operator-initiated read/write.
• Scheduled task: Use the HMI's scheduled tasks to run the function on a periodic interval (for example every 60 s) to log counters to Excel automatically.

For a value-change trigger, debounce the event by storing the previous value and only executing the script when the value actually changes. Otherwise, every PLC cycle the HMI re-acquires the tag and the script re-fires.

Reading Multi-Column Recipe Data

Real recipe files have multiple columns (setpoint 1, setpoint 2, dwell time, tolerance, etc.). Replace the single Cells(r, c) with a row read using Range and assign each column to its own HMI tag.

Dim objExcelApp
Dim ws
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False
objExcelApp.DisplayAlerts = False
objExcelApp.Workbooks.Open "D:\Data.xlsx", False, True
Set ws = objExcelApp.ActiveSheet

SmartTags("Setpoint1")     = CLng(ws.Cells(SmartTags("Golovka_nozha"), 1).Value)
SmartTags("Setpoint2")     = CLng(ws.Cells(SmartTags("Golovka_nozha"), 2).Value)
SmartTags("DwellTime")     = CLng(ws.Cells(SmartTags("Golovka_nozha"), 3).Value)
SmartTags("Tolerance")     = CLng(ws.Cells(SmartTags("Golovka_nozha"), 4).Value)
SmartTags("RecipeName")    = CStr(ws.Cells(SmartTags("Golovka_nozha"), 5).Value)

objExcelApp.Workbooks.Close False
objExcelApp.Quit
Set objExcelApp = Nothing

Create the column header row in the Excel file (A1 = Setpoint1, B1 = Setpoint2, ...) so the maintenance engineer can edit the recipe by editing cell values without touching the HMI project.

Verification

1. Compile the VBS function in TIA Portal by clicking "Compile > Software (rebuild all)". Any syntax error in the script is reported with line number in the inspector window.
2. Start the HMI Runtime in simulation mode (Start > Simulation) on the engineering PC. Set the Golovka_nozha tag via the HMI tag simulation table to 1, then to 2, and confirm the text field updates with the contents of A1 and A2 respectively.
3. Set the tag to 3 and confirm that cell A3 in D:\Data.xlsx contains the current value of the counter tag. Re-open the file in Excel to verify the value persisted after the script ended.
4. Trigger a deliberate failure by renaming Data.xlsx to Data.xlsx.bak. Run the script and confirm the alarm tag ExcelStatus shows 13 (open failed) and the Runtime does not freeze.
5. On a live Runtime PC, restart the machine and confirm the autologon user can still read/write the file. If the file is on a network share, disconnect the share and re-test the timeout watchdog.

Troubleshooting Matrix

Security and Operational Notes

• Antivirus interaction: Some antivirus suites inspect Excel.exe on every launch and add 2..3 s of latency. Whitelist the Excel executable and the data folder to avoid Runtime timeouts.
• Runtime user context: The WinCC Runtime service account should be a local administrator on the Runtime PC so DCOM launch of Excel is permitted without manual DCOMCNFG steps.
• File locking: Always close the workbook before quitting Excel. Excel retains a write lock on the file until all handles are released; a script that crashes between Open and Close leaves a ~lock file that blocks the next run.
• Backups: Maintain a revisioned copy of the recipe workbook on the engineering server. The Runtime PC should pull a fresh copy at machine start, not edit the master.
• Auditing: For pharmaceutical and food applications, combine the Excel write with the WinCC audit trail so the GMP record captures the operator, timestamp, and cell value of every change.