Generating Excel Reports in Siemens WinCC V7.0 with VB Script

Overview: Why Move From CSV to Native Excel Reports

WinCC V7.0 ships with a built-in report designer that defaults to comma-separated values (CSV) when the runtime job runs without a layout configured for the spreadsheet program. CSV is fast, but operations teams, quality engineers, and customers typically want a real Microsoft Excel workbook with frozen header rows, formula columns, formatted timestamps, and per-shift worksheets. The transition from CSV to XLS/XLSX in WinCC V7.0 is not a single property change; it requires either activating the Excel print job in the report designer, or driving Excel from a VB Script through OLE automation. This article documents the script-based approach used in the field, the button wiring on the SCADA picture, and the archive queries that fetch historical tag values between a configurable start time and end time.

The script-based approach is preferred over the print job approach when:

• The customer wants interactive Excel features (formulas, charts, conditional formatting, multiple sheets).
• The runtime workstation does not have a full Microsoft Office installation, or the print job fails on locked-down servers.
• You need to combine live WinCC tag values with historical archive values in the same workbook.
• The button on the SCADA picture must launch a workbook the operator can edit, save, and e-mail.

Throughout this reference the WinCC project is assumed to be a single-server or client-server project running on Windows 7 / Server 2008 R2 or later with WinCC V7.0 SP3 or later. The host must have Microsoft Excel 2007 / 2010 / 2013 installed; 32-bit Office is recommended because the WinCC V7.0 runtime is a 32-bit process and a 32-bit script against 64-bit Excel raises ActiveX component cannot create object errors.

Prerequisites and Software Requirements

Before writing the first line of script, verify the following components are present on the engineering station, the WinCC server, and every WinCC client that needs to run the report:

1. WinCC V7.0 (any Service Pack from SP1 onward). Open WinCC Explorer and check the About box for the exact build, e.g. "WinCC V7.0 + SP3 Update 6". The build string matters because the SQL archive schema changed between SP2 and SP3, and OLE DB queries written for one will not work on the other.
2. Microsoft Excel 2007 / 2010 / 2013 (32-bit). The script uses late binding by default so it does not require referencing the Excel object library at design time, but late binding is fragile if Office updates swap the CLSID. The mid-ground is early binding to Microsoft Excel 14.0 Object Library (Excel 2010) and re-compiling when the Office version is upgraded.
3. VB Script runtime (msvbvm60.dll / scrrun.dll). WinCC V7.0 installs this automatically. Do not block the script with an anti-virus product that strips WSH.
4. MDAC 2.8 or later (or the modern Windows Data Access components). Required for ADODB.Connection, ADODB.Recordset, and the WinCC OLE DB provider.
5. SQL Native Client 2005 / 2008 / 2012 matching the SQL Server instance that WinCC uses for the Tag Logging archive. The runtime archive database is named CC_<ServerName>_<ProjectName>_R by default.
6. Operator authorization level configured in User Administrator. The button event should be limited to operators with the "Report" or higher level to prevent accidental generation of large workbooks during commissioning.

WinCC V7.0 Report Architecture

To write a working export script you need to know which data source the script will read. WinCC V7.0 splits runtime data into three reservoirs:

The OLE DB provider is registered when WinCC V7.0 installs. Verify it from the registry:

HKEY_CLASSES_ROOT\WinCCOLEDBProvider.Connect\CLSID
HKEY_LOCAL_MACHINE\SOFTWARE\Siemens\WinCC\SQL\Connectivity

The connection string that the report script will use is:

Provider=WinCCOLEDBProvider.1;
Catalog=CC_<Server>_<Project>_R;
Data Source=<ServerName>\WinCC;
User Id=<WinCCUser>;
Password=<WinCCPassword>;

For local execution from a button the catalog and data source can be the same machine. The user needs rights on the Tag Logging archive, which in WinCC V7.0 means membership in the local group SQLServer2005MSSQLUser$<ComputerName>$<WINCC_INSTANCE> or the equivalent in the active SQL Server version.

The WinCC OLE DB Query Language

The WinCC OLE DB provider exposes a SQL-like language tailored to archives. The three table identifiers that the script will use are:

Standard SQL clauses (WHERE, BETWEEN, ORDER BY) are supported. The WinCC-specific clauses that distinguish the provider are:

• SAMPLING PERIOD n - returns one row per n milliseconds, useful for downsampling.
• AGGREGATION <function> - applies MAX, MIN, AVG, SUM, COUNT to REALVALUE before sampling.
• ROWCOUNT n - hard limit on the number of returned rows.

For example, a 24-hour temperature trend at 1-minute average:

SELECT TIMESTAMP, AVG(REALVALUE) AS AvgTemp
FROM   TAG:R:'Line1_Temperature'
WHERE  TIMESTAMP BETWEEN '2024-01-15 06:00:00' AND '2024-01-16 06:00:00'
GROUP BY TIMESTAMP
SAMPLING PERIOD 60000
ROWCOUNT 2000

Choosing the Reporting Method

Three methods are commonly used to move data from WinCC V7.0 into Excel. Pick by the constraints of the customer's site.

The rest of this document covers the VB Script method in depth, with notes on RBSReport at the end.

Method 1 - VB Script With OLE Automation to Excel

The VB Script lives in the WinCC Graphics Designer picture. The picture contains four input/output objects: a start time, an end time, a duration label, and a "Generate" button. When the operator presses "Generate" the script runs the export.

Picture-Level Object Model

Create the following I/O field objects on the picture:

Add a C action to the duration field to update every second:

/* Duration calculation - C action behind tDur output */
{
    double dStart = atof(GetTagChar("Report_StartTime"));
    double dEnd   = atof(GetTagChar("Report_EndTime"));
    double dDur   = (dEnd - dStart) / 3600.0;
    SetPropChar(lpszPictureName, "tDur", "OutputValue", dDur);
    return dDur;
}

For a pure VB approach without C, drop the C action and compute the duration in the button script instead.

Excel Object Library Choice

There are two binding strategies:

• Early binding: add "Microsoft Excel 14.0 Object Library" (or whatever matches the installed Office) to the script environment. Faster, IntelliSense works in the IDE, but the script breaks if the office version is upgraded.
• Late binding: instantiate the Excel application with CreateObject("Excel.Application"). The script survives Office upgrades but every constant has to be its numeric value (e.g. xlContinuous = 1).

For a production WinCC V7.0 project that runs on a fixed image, use early binding. For a project that ships to multiple customer sites, use late binding. The example below uses late binding so it runs unmodified against Excel 2007 / 2010 / 2013 / 2016.

Main Export Script (Late Binding)

Place this VB Script in the button's Mouse click event, configured to run as a VBS action:

'==========================================================================
' WinCC V7.0 - Excel report from Tag Logging archive
' Trigger: btnGen mouse click
'==========================================================================
Option Explicit

Const EXCEL_PATH_TEMPLATE = "C:\WinCC_Reports\Templates\ShiftReport.xltx"
Const EXCEL_PATH_OUTPUT   = "C:\WinCC_Reports\Out\"

' WinCC OLE DB connection
Const WINCC_OLESERVER     = "WinCCOLEDBProvider.1"

Dim sStart, sEnd
Dim oExcel, oBook, oSheet
Dim oConn, oRS
Dim sConn, sSQL
Dim iRow, iCol
Dim fso

' ---------- 1. Read the time window from the I/O fields ----------
sStart = HMIRuntime.Screens("ReportScreen").ScreenItems("tStart").OutputValue
sEnd   = HMIRuntime.Screens("ReportScreen").ScreenItems("tEnd").OutputValue

If sStart = "" Or sEnd = "" Then
    MsgBox "Enter start and end time first.", vbExclamation, "Report"
    Exit Sub
End If

If CDate(sEnd) <= CDate(sStart) Then
    MsgBox "End time must be greater than start time.", vbExclamation, "Report"
    Exit Sub
End If

' ---------- 2. Build OLE DB connection string ----------
sConn = "Provider=" & WINCC_OLESERVER & ";" & _
        "Catalog=CC_" & HMIRuntime.ComputerName & "_" & _
        HMIRuntime.ProjectName & "_R;" & _
        "Data Source=" & HMIRuntime.ComputerName & "\WinCC"

' ---------- 3. Open Excel with the customer template ----------
Set fso    = CreateObject("Scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible        = True
oExcel.DisplayAlerts = False
oExcel.ScreenUpdating = False

If fso.FileExists(EXCEL_PATH_TEMPLATE) Then
    Set oBook = oExcel.Workbooks.Add(EXCEL_PATH_TEMPLATE)
Else
    Set oBook = oExcel.Workbooks.Add()
    oBook.Sheets(1).Name = "ShiftData"
End If

Set oSheet = oBook.Sheets(1)

' ---------- 4. Write the report header ----------
With oSheet
    .Cells(1, 1).Value = "Process report"
    .Cells(1, 1).Font.Size = 14
    .Cells(1, 1).Font.Bold = True
    .Range("A1:D1").Merge
    .Cells(2, 1).Value = "Start time:"
    .Cells(2, 2).Value = sStart
    .Cells(3, 1).Value = "End time:"
    .Cells(3, 2).Value = sEnd
    .Cells(4, 1).Value = "Duration (h):"
    .Cells(4, 2).Value = Round((CDate(sEnd) - CDate(sStart)) * 24, 3)
    .Cells(2, 1).Font.Bold = True
    .Cells(3, 1).Font.Bold = True
    .Cells(4, 1).Font.Bold = True
End With

' ---------- 5. Column headers (row 6) ----------
Dim tags
tags = Array("Line1_Temperature", "Line1_Pressure", "Line1_Flow")

oSheet.Cells(6, 1).Value = "Timestamp"
oSheet.Cells(6, 2).Value = tags(0)
oSheet.Cells(6, 3).Value = tags(1)
oSheet.Cells(6, 4).Value = tags(2)
oSheet.Range("A6:D6").Font.Bold = True
oSheet.Range("A6:D6").Interior.Color = RGB(200, 200, 200)

' ---------- 6. Query Tag Logging archive via OLE DB ----------
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = sConn
oConn.CursorLocation   = 3  ' adUseClient
oConn.Open

iRow = 7
Dim tag, i, colOffset
For Each tag In tags
    For i = 0 To UBound(tags)
        If LCase(tags(i)) = LCase(tag) Then
            colOffset = 2 + i
            Exit For
        End If
    Next

    sSQL = "SELECT TIMESTAMP, REALVALUE, QUALITY " & _
           "FROM TAG:R:'" & tag & "' " & _
           "WHERE TIMESTAMP BETWEEN '" & sStart & "' AND '" & sEnd & "'"

    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, oConn, 1, 3  ' adOpenKeyset, adLockOptimistic

    Do While Not oRS.EOF
        oSheet.Cells(iRow, 1).Value = CDate(oRS.Fields("TIMESTAMP").Value)
        oSheet.Cells(iRow, 1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
        oSheet.Cells(iRow, colOffset).Value = oRS.Fields("REALVALUE").Value
        oSheet.Cells(iRow, colOffset).NumberFormat = "0.00"
        iRow = iRow + 1
        oRS.MoveNext
    Loop
    oRS.Close
Next

oConn.Close

' ---------- 7. Apply formatting and save ----------
oSheet.Columns("A:D").AutoFit
oSheet.Range("A6:D" & iRow - 1).Borders.LineStyle = 1   ' xlContinuous

Dim sFile
sFile = EXCEL_PATH_OUTPUT & "Report_" & _
        Replace(Replace(Now, ":", "-"), "/", "-") & ".xlsx"
oBook.SaveAs sFile, 51   ' xlOpenXMLWorkbook (xlsx)
oExcel.ScreenUpdating = True

' Leave Excel visible so the operator can verify; oExcel.Quit is intentionally
' omitted if you want a non-blocking export.

Set oConn  = Nothing
Set oRS    = Nothing
Set oSheet = Nothing
Set oBook  = Nothing
Set oExcel = Nothing
Set fso    = Nothing

Variant - Side-By-Side Rows With SQL Server Native Client

The WinCC OLE DB provider returns one row per archive entry per tag, so the data lands in stacked blocks. To get one row per timestamp with all tags in columns, connect to the runtime database directly with the SQL Server provider and pivot the result.

' ---------- Side-by-side query via SQL Native Client ----------
Const SQL_NATIVE = "SQLNCLI10"   ' 2008 native client; use SQLNCLI11 for 2012+

Dim sConn2
sConn2 = "Provider=" & SQL_NATIVE & ";" & _
         "Data Source=" & HMIRuntime.ComputerName & "\WinCC;" & _
         "Initial Catalog=CC_" & HMIRuntime.ComputerName & "_" & _
         HMIRuntime.ProjectName & "_R;" & _
         "Integrated Security=SSPI;"

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = sConn2
oConn.Open

' WinCC Tag Logging tables follow the naming convention
' TLG_<TagName>_F (fast) and TLG_<TagName>_S (slow)
Dim sSQL2
sSQL2 = "SELECT a.TIMESTAMP, a.REALVALUE AS Temp, " & _
        "       b.REALVALUE AS Pres, c.REALVALUE AS Flow " & _
        "FROM   dbo.TLG_Line1_Temperature_F a " & _
        "LEFT JOIN dbo.TLG_Line1_Pressure_F    b ON a.TIMESTAMP = b.TIMESTAMP " & _
        "LEFT JOIN dbo.TLG_Line1_Flow_F        c ON a.TIMESTAMP = c.TIMESTAMP " & _
        "WHERE  a.TIMESTAMP BETWEEN '" & sStart & "' AND '" & sEnd & "' " & _
        "ORDER BY a.TIMESTAMP"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL2, oConn, 1, 3

iRow = 7
oSheet.Cells(6, 1).Value = "Timestamp"
oSheet.Cells(6, 2).Value = "Temperature"
oSheet.Cells(6, 3).Value = "Pressure"
oSheet.Cells(6, 4).Value = "Flow"

Do While Not oRS.EOF
    oSheet.Cells(iRow, 1).Value = CDate(oRS.Fields("TIMESTAMP").Value)
    oSheet.Cells(iRow, 1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
    oSheet.Cells(iRow, 2).Value = oRS.Fields("Temp").Value
    oSheet.Cells(iRow, 3).Value = oRS.Fields("Pres").Value
    oSheet.Cells(iRow, 4).Value = oRS.Fields("Flow").Value
    iRow = iRow + 1
    oRS.MoveNext
Loop
oRS.Close
oConn.Close

Wiring the SCADA Buttons

WinCC V7.0 ties VB Script to events on a Graphics Designer object. To attach the script to the Generate button:

1. Open the report picture in Graphics Designer.
2. Right-click btnGen and select Properties > Events > Mouse > Click.
3. In the action picker choose VB Action and paste the script body. WinCC will compile the script when the picture is saved; the runtime supports Option Explicit even if the editor hides it.
4. Click the Authorization tab and set the operator level that may trigger the report (typically level 4 - "Reporting").
5. For runtime loading time, compile the picture: File > Check Consistency then save and download to the runtime station.
6. Open the picture in WinCC Runtime, press Generate, and confirm the workbook opens in Excel.

To attach the duration calculation to the I/O field output, the recommended pattern is a global C action scheduled on a 1 s cycle that writes the duration string into a tag; the field has its Output property bound to that tag. This avoids the C action being lost when the picture is closed.

Method 2 - Built-In Excel Print Job (No Script)

If scripting is too heavy for the maintenance team, the report designer can produce an XLS file without any custom code. The catch is that the layout is fixed and there is no operator interaction.

1. Open WinCC Explorer > Report Designer > Page Layout.
2. Select the print job (e.g. ReportFileStateLog).
3. On the Properties tab, set Output format to RTF-File if you want rich text or XLS if a flat Excel file is acceptable.
4. Add the required archive columns to the layout, then schedule the print job with the Time Base set to Daily 06:00 for shift hand-over.
5. Trigger the print job from a button using a C action that calls RP_Print with the job name.

Customers who insist on a "click a button and the workbook opens" experience are not satisfied with the print job. For them, the script approach is the answer.

Method 3 - Third-Party Add-On: RBSReport

RBSReport, the third-party add-on mentioned in the field report, is a WinCC plug-in that runs as a Windows service and can render scheduled and on-demand reports to XLS / XLSX / PDF. The product is published at www.rbsreport.com. Compared with the in-house script:

• Pros: no Office install on the SCADA station, built-in scheduling, built-in e-mail distribution, native support for archive pivots.
• Cons: third-party license cost, version coupling with each WinCC service pack, and the report designer is a separate tool that has to be learned in addition to WinCC.

For customers that need a sustainable shift-reporting solution with operator self-service and e-mail delivery, RBSReport is worth evaluating. For a one-off report that has to be in place next week, the VB Script approach delivers faster.

Performance and Archive Considerations

A poorly designed report can lock the runtime database. The rules below are field-proven on multi-thousand-tag WinCC V7.0 plants.

1. Bound the time window. The Tag Logging archive is compressed with a swing buffer; a 24 h window on a 100 ms tag can still return millions of rows. The button script should refuse a window longer than 31 days.
2. Use server-side aggregation. The WinCC OLE DB provider supports a SAMPLING PERIOD clause. For trend lines, ask for 1 min averages instead of raw values:

   SELECT TIMESTAMP, AVG(REALVALUE)
   FROM TAG:R:'Line1_Temperature'
   WHERE TIMESTAMP BETWEEN '2024-01-15 06:00:00' AND '2024-01-16 06:00:00'
   GROUP BY TIMESTAMP
   SAMPLING PERIOD 60000

3. Run heavy reports from a client, not the server. A WinCC client with the script connected to the server's archive off-loads the CPU from the runtime.
4. Excel is single-threaded for VBA work. If the workbook needs formulas, fill them after the data is in (in a separate post-processing step) and only on a copy of the file. Forcing Excel to recalc while the script still has a reference to the workbook hangs the process.
5. Open Excel out of process for long reports. oExcel.Visible = False, save with oBook.SaveAs, then oBook.Close False and oExcel.Quit. The operator gets a pop-up only when the file is on disk, which keeps the SCADA picture responsive.
6. Clean up COM objects. The last Set oExcel = Nothing line is not optional. Skipping it leaves a phantom EXCEL.EXE in Task Manager and the next export fails with The server threw an exception.
7. Pre-create the output directory in the project startup script. If the directory is missing the SaveAs call throws Path not found and Excel stays open holding a temp file.

Common Edge Cases and Pitfalls

Verification and Acceptance Test

After the script is deployed, run the following acceptance checklist before hand-over:

1. Open the picture on a runtime client and press Generate with a 1 min window. The workbook should open in Excel with three columns and a row count equal to the number of archived values in the window (rounded to the archive cycle).
2. Open the workbook, verify the header block (Start / End / Duration) and that the timestamps in column A are sorted ascending.
3. Repeat the test with a 24 h window on a 1 s tag. The export should complete in under 30 s on a quad-core SCADA client. If it takes more than 2 min, the OLE DB provider is being asked for raw values; switch to SAMPLING PERIOD.
4. Trigger the export on a different client. Both outputs should be byte-identical except for the file name (which is timestamp-based).
5. Open the SQL profiler trace on the runtime database while the export runs and confirm the OLE DB provider issues a single SELECT per tag. A flood of small queries indicates the script is reading values one at a time and must be re-written with a single Recordset read.
6. Close Excel with the workbook still open, then re-open it from Windows Explorer. The file should display the same data. If the file is empty, the script saved before the data write completed; move the SaveAs call to the very end of the script.
7. Restart the WinCC runtime and re-run the export. The same data must appear; if not, the script depends on a session-bound ADODB connection that the runtime did not re-open.

Troubleshooting Matrix

Deployment and Migration Notes

When the script is moved from the engineering station to a customer runtime, observe the following migration steps:

1. Export the picture from Graphics Designer on the engineering station (File > Export > WinCC Picture).
2. On the target runtime, install the same Office build and the same SQL Native Client build. Mixed Office versions on a multi-server WinCC project are the leading cause of late-binding errors on the operator client but not the engineering client.
3. Re-create the internal tags Report_StartTime and Report_EndTime in the target project if they were not exported with the picture.
4. Grant the WinCC runtime user read on the SQL Server runtime database. The minimum permission is db_datareader on CC_<Server>_<Project>_R.
5. Pre-create C:\WinCC_Reports\Out and the Templates subfolder. Copy the customer's ShiftReport.xltx template to the Templates folder.
6. Test the export with the same 1 min window used in the engineering acceptance test before commissioning the picture on the operator client.

For customers upgrading from WinCC V6.2 to V7.0 the script is mostly portable. The two changes that are commonly required are: the catalog suffix changed from _R in V6.2 to the same _R in V7.0 (no change) but the default archive column VALUE was renamed to REALVALUE; and the runtime now requires the OLE DB provider registration step that was implicit in V6.2.

FAQ