Adding Logo and Borders to Excel Reports in WinCC Flexible 2008 SP2 Runtime

WinCC Flexible 2008 SP2 Runtime does not expose a built-in, dialog-driven way to embed a corporate logo or to apply cell borders inside the formatted output of an Excel report. The standard report object writes tag values, alarm archives, and process values into a defined layout, but the visual treatment of the report — header graphics, lines, frames, and corporate identity elements — must be carried in by the template. The supported and field-proven approach is to author a fully formatted Excel template (.xlt or .xls) that already contains the company logo, header text, cell borders, fonts, and number formats, and to use a VBScript function in the WinCC Flexible project to copy this template, populate it from runtime tags, and save the result under a runtime-generated filename. This article documents that workflow end-to-end with code, parameter tables, and the diagnostic checks that commissioning engineers typically need.

1. Architecture: How Excel Reporting Works in WinCC Flexible 2008

WinCC Flexible 2008 SP2 ships two native report objects that can target Excel as a print/export target:

• Report (alarm / process value log) — drawn from the configured alarm archive or process value archive and rendered through a layout. Excel export is available when the layout is configured with a spreadsheet target.
• Recipe report — produces a single-line-per-ingredient tabular view of a recipe set; it is the closest native analog of a "batch sheet" and is the layout the source conversation modified.

Both objects are bound to a layout file stored on the runtime filesystem. Layout files in WinCC Flexible are proprietary and do not give access to the chart-frame, picture-insertion, or border properties the customer requested. The widely deployed and Siemens-supported workaround is to drive Excel directly from a VBScript triggered by a button, a scheduled task, or a value-change on a tag. The script becomes the layout.

The runtime model is therefore:

When the script fires, the template is opened, the placeholder cells are written with live tag values, and a new workbook is written to the report destination. The original template is never modified, so every batch report inherits the customer's branding and frame automatically.

2. Prerequisites

1. Microsoft Excel installed on the RT PC. A 32-bit Excel installation is recommended when the WinCC Flexible RT is 32-bit; mixing 32/64-bit Office with 32/64-bit RT will not work because VBScript binds to the first matching registered class id.
2. WinCC Flexible 2008 SP2 (or SP3/SP4) engineering software with a project compiled for the PC RT target.
3. VBScript editor access on the RT, enabled in Project > Settings > Runtime Settings > Services. The default is enabled; verify before commissioning.
4. Write permission for the runtime user account on the directory that will hold the template and the generated reports. On a multi-user RT, the path should be on a local fixed disk or a UNC path the RT service can reach; mapped network drives lose their mapping when the RT runs as a service.
5. Template authoring workstation with Excel 2003/2007/2010. The template should be saved as Excel 97-2003 Workbook (.xls) if any RT PC still has Office 2003, or as Excel Template (.xlt) when forcing the file to be treated as a template on open.

3. Step 1 — Author the Excel Template with Logo and Borders

Open a new workbook in Excel and lay out the report as it should look at runtime. The convention is to designate specific cells as data cells and to leave every other cell fully formatted. The script only writes to the data cells.

3.1 Cell layout convention

3.2 Applying cell borders

Select the report range (e.g. A4:F25) and apply borders from the ribbon. The Microsoft Excel support article on cell borders documents the user-interface path and the underlying constants used by the Excel object model. The same constants are reused by the script in step 4.

Recommended pattern for a corporate frame is to set xlEdgeTop, xlEdgeBottom, xlEdgeLeft, xlEdgeRight to Thin / Continuous / Automatic, and the inside lines to Hairline / Continuous / Automatic. This combination is readable on print and survives a highlighter pass from a quality auditor.

3.3 Inserting the company logo

Place the cursor in cell A2 (or any cell that will hold the image) and use Insert > Pictures > This Device. Anchor the picture To Cell, untick Move with cells only if you want the image pinned to its row during sorting — for a report it should be anchored to the cell. Acceptable formats are PNG, JPG, BMP, GIF, and TIFF; PNG at 150-300 DPI gives the best print quality without bloating the file. A logo of 200×60 pixels at 96 DPI fits a typical header band and keeps the workbook under 100 kB.

3.4 Save the template

Save the workbook as report_template.xlt into the runtime-visible path, typically:

• Local RT folder: C:\Program Files\Siemens\Automation\WinCC Flexible 2008 RT\Reports\report_template.xlt
• Or an operator-facing path: D:\Reports\report_template.xlt

Use Save As > Excel Template (.xlt) to flag the file as a template. Excel will then open it as a copy in memory, leaving the original untouched. If you save as .xls instead, the script must explicitly use SaveAs to write a new file, which is the approach the source conversation adopted.

4. Step 2 — Configure the Runtime Storage Path

The runtime needs read access to the template and write access to the report destination. In the engineering project, declare both paths as paths so they can be edited at the panel without re-compiling:

1. Open Project > Paths.
2. Add REPORT_TEMPLATE pointing to the .xlt location.
3. Add REPORT_DESTINATION pointing to the output directory (e.g. D:\Reports\Archive\).

These are not filesystem variables — they are project constants surfaced under Runtime > Project Settings > Paths on the target. Keep them lowercase on the filesystem; Excel does not care, but case-sensitive backup tools do.

5. Step 3 — Write the VBScript Function

The function below opens the template, writes the tag values into the placeholder cells, applies a thicker border around the measurement block, embeds a logo if the template does not already have one, and saves the result. It is the same skeleton that ships in customer projects derived from the source conversation, extended with explicit object cleanup, error handling, and configurable paths.

'--------------------------------------------------------------
' WinCC Flexible 2008 SP2 RT — Excel Report Generator
' Trigger: scheduled task, button press, or value-change on
'          tag "Generate_Report_Trigger"
'--------------------------------------------------------------
Option Explicit

Const xlContinuous      = 1
Const xlAutomatic       = -4105
Const xlThin            = 2
Const xlMedium          = -4138
Const xlEdgeTop         = 8
Const xlEdgeBottom      = 9
Const xlEdgeLeft        = 7
Const xlEdgeRight       = 10
Const xlInsideHorizontal= 12
Const xlInsideVertical  = 11
Const msoTrue           = -1
Const msoFalse          = 0

Sub Generate_Batch_Report()
    Dim objExcel, objWkb, objWks, objFSO, objFolder
    Dim sTemplate, sDestDir, sBatchID, sOutFile, sErr
    Dim iRow, iCol

    On Error GoTo EH

    '--- 1. Resolve paths from project constants ---
    sTemplate = SmartTags("REPORT_TEMPLATE")
    sDestDir  = SmartTags("REPORT_DESTINATION")
    sBatchID  = SmartTags("Batch_ID")

    '--- 2. Validate inputs ---
    If Len(sTemplate) = 0 Or Len(sDestDir) = 0 Or Len(sBatchID) = 0 Then
        SmartTags("Report_Status") = "E: missing path or batch id"
        Exit Sub
    End If

    '--- 3. Ensure destination directory exists ---
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If Not objFSO.FolderExists(sDestDir) Then objFSO.CreateFolder sDestDir

    '--- 4. Launch Excel invisibly ---
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible        = False
    objExcel.DisplayAlerts  = False
    objExcel.ScreenUpdating = False

    '--- 5. Open the template (logo + borders pre-applied) ---
    Set objWkb = objExcel.Workbooks.Open(sTemplate, , True)  ' ReadOnly=True keeps template intact
    Set objWks = objWkb.Sheets(1)

    '--- 6. Optional: dynamically insert a logo if template lacks one ---
    If objWks.Shapes.Count = 0 Then
        objWks.Shapes.AddPicture _
            Replace(sTemplate, "report_template.xlt", "company_logo.png"), _
            msoTrue, msoTrue, 10, 10, 200, 60
    End If

    '--- 7. Write tag values to placeholder cells ---
    With objWks
        .Range("B5").Value = sBatchID
        .Range("B6").Value = SmartTags("Operator")
        .Range("B7").Value = SmartTags("Batch_Start")
        .Range("B8").Value = SmartTags("Batch_End")

        ' Measurement block A10:F25 — write 16 rows from array tag
        For iRow = 0 To 15
            .Cells(11 + iRow, 1).Value = iRow + 1
            .Cells(11 + iRow, 2).Value = SmartTags("Meas_Name_"  & (iRow + 1))
            .Cells(11 + iRow, 3).Value = SmartTags("Meas_Value_" & (iRow + 1))
            .Cells(11 + iRow, 4).Value = SmartTags("Meas_Unit_"  & (iRow + 1))
            .Cells(11 + iRow, 5).Value = SmartTags("LSL_"        & (iRow + 1))
            .Cells(11 + iRow, 6).Value = SmartTags("USL_"        & (iRow + 1))
        Next iRow
    End With

    '--- 8. Re-assert borders on the measurement block in case
    '        the template author left them off ---
    With objWks.Range("A10:F26").Borders
        .Item(xlEdgeTop).LineStyle  = xlContinuous : .Item(xlEdgeTop).Weight  = xlMedium
        .Item(xlEdgeBottom).LineStyle=xlContinuous : .Item(xlEdgeBottom).Weight=xlMedium
        .Item(xlEdgeLeft).LineStyle = xlContinuous : .Item(xlEdgeLeft).Weight = xlMedium
        .Item(xlEdgeRight).LineStyle= xlContinuous : .Item(xlEdgeRight).Weight= xlMedium
        .Item(xlInsideHorizontal).LineStyle = xlContinuous
        .Item(xlInsideVertical).LineStyle   = xlContinuous
    End With

    '--- 9. Save under runtime-generated filename ---
    sOutFile = sDestDir & "Report_" & sBatchID & "_" & _
               FormatDateTime(Now, 2) & "_" & _
               FormatDateTime(Now, 4) & ".xls"
    sOutFile = Replace(sOutFile, "/", "-")    ' Excel does not like / in sheet names
    objWkb.SaveAs sOutFile, -4143            ' xlWorkbookNormal

    SmartTags("Report_Status")   = "OK: " & sOutFile
    SmartTags("Report_FilePath") = sOutFile

    '--- 10. Clean up ---
    objWkb.Close False
    objExcel.Quit
    Set objWks = Nothing : Set objWkb = Nothing : Set objExcel = Nothing
    Set objFSO = Nothing
    Exit Sub

EH:
    sErr = "E" & Err.Number & " " & Err.Description
    SmartTags("Report_Status") = sErr
    If Not objWkb Is Nothing Then objWkb.Close False
    If Not objExcel Is Nothing Then objExcel.Quit
    Set objWks = Nothing : Set objWkb = Nothing : Set objExcel = Nothing
    Set objFSO = Nothing
End Sub

5.1 Code notes

• Workbooks.Open(sTemplate, , True) opens the file in read-only mode so the original template is not modified. The subsequent SaveAs writes a new workbook to the destination.
• objExcel.DisplayAlerts = False suppresses the "Keep current format?" prompt that Excel shows when saving an .xls from a newer Excel version. Set this to True during commissioning to see the dialogs and identify the format being written.
• Border constants are defined at the top of the module. xlContinuous = 1 is the only line style used for solid corporate frames; the Microsoft Excel cell borders reference documents the full set.
• The FormatDateTime(Now, 2) returns yyyy/mm/dd on most locales; replace the date formatter with a fixed format string if your plant enforces a particular naming convention.

6. Step 4 — Bind the Script to a Trigger

There are three trigger patterns in WinCC Flexible 2008 that engineers typically choose between:

For batch reporting the recommended binding is tag change on a Boolean or integer trigger tag. Configure the tag with an event that calls Generate_Batch_Report on a rising edge only. A common implementation writes a one-shot pulse from the PLC (0 → 1 → 0) and uses the rising edge to fire the report. This avoids duplicate reports when the runtime reads the same value twice.

7. Border and Logo Reference Tables

The following tables consolidate the Excel object-model constants the script uses. They are the same constants a user would set in the Excel UI; the script reaches them programmatically.

7.1 LineStyle constants

7.2 Weight constants

7.3 Shapes.AddPicture parameters

8. Error Handling and Diagnostics

The most common failure modes seen in field deployments are listed below with their hex/decimal error codes and the corrective action.

Two operational rules eliminate most of the entries above:

1. Always store the template on a local fixed disk of the RT, never on a network share. The RT service may not have a mapped drive letter.
2. Always set the template as read-only at the filesystem level so a misbehaving script cannot corrupt it. The script opens it with ReadOnly:=True for the same reason.

9. Verification and Commissioning

A sign-off script for the QA team should include the following checks. Run them once during Site Acceptance Test (SAT) and again on any Excel/Office update.

1. Template path resolution — From the RT, open a command prompt under the same service account and confirm dir "%REPORT_TEMPLATE%" lists report_template.xlt.
2. First-run end-to-end — Trigger the report manually from the HMI button. Confirm the new file appears in the destination, contains the logo in the header, and shows borders on the measurement block.
3. 100-run stress — Loop the trigger 100 times with synthetic data. Verify (a) all 100 files are written, (b) file sizes are within 10% of each other, (c) the template file is unchanged (compare SHA-256).
4. Edge cases — Trigger a report with an empty Operator tag. Trigger one with a Batch_ID containing a slash. Verify that the script does not crash and that the output filename has illegal characters replaced.
5. Print preview — Open one of the generated files, send to print preview, and verify the logo and borders render on a real printer or to PDF.
6. Multi-user — If the RT supports concurrent operators, trigger two reports within one second. Verify both are written and that Excel does not raise a "file in use" error. If it does, the script must serialize the calls with a single global mutex tag.

10. Performance and Runtime Footprint

Each report open Excel.Application starts a new EXCEL.EXE process. The WinCC Flexible RT is forgiving of this for low-frequency reports (end of batch, end of shift), but it is not appropriate for tag-change-rate triggers on fast processes. Approximate numbers from the field:

For reports triggered more often than once per minute, consider keeping a single Excel.Application instance alive and reusing it via a long-lived script object. WinCC Flexible 2008 SP2 does not expose an in-process Excel server, so this is a workaround that is brittle across RT restarts and is not recommended in production for the typical batch-reporting workload.

11. Migration Path to WinCC Unified

Customers continuing to use WinCC Flexible 2008 SP2 should be aware that the platform is in the legacy / extended support phase and that the Excel-COM approach above is replaced in current TIA Portal projects by the WinCC Unified Reporting add-in for Excel. The new workflow:

1. Install the WinCC Unified Reporting add-in in Excel on the engineering station.
2. Open the add-in and connect it to the Unified runtime; tag values are exposed as a connected data model.
3. Author the report as a regular Excel file — the Reporting add-in in Excel page documents the enablement steps in the Trust Center.
4. Publish the report template to the runtime; reports are generated server-side, not via per-report Excel launches.

The data-cell philosophy (write tags into fixed cells of a styled template) is identical between the two platforms, which makes the migration mechanical: the template can be reused, only the binding mechanism changes.

12. Reference Material

• Microsoft Support — Apply or remove cell borders on a worksheet (line-style, weight, and edge constants)
• TIA Portal docs — Adding the Reporting add-in in Excel (RT Unified) (target architecture for migration)

FAQ