Automating Historical Excel Report Sorting by Date and Time via WinCC VBScript (Range.Sort Method)

To sort an exported historical data table in Microsoft Excel by date and time in descending order (newest on top) via WinCC VBScript, invoke the Excel COM object's Range.Sort method on the data range. Set Key1 to the date/time header cell (e.g., objSheet.Range("A2")), specify sort order descending using the Excel COM constant xlDescending = 2, and define Header:=1 (xlYes). Always terminate the script by saving the workbook, closing the application (objExcel.Quit), and explicitly setting all COM object references to Nothing to prevent orphaned EXCEL.EXE background processes from exhausting OS server memory.

WinCC VBScript and Microsoft Excel COM Automation Architecture

In SIMATIC PCS 7 and WinCC, operators and plant supervisors frequently require automated shift production reports, tank level logs, or batch summaries exported directly into Microsoft Excel spreadsheets (.xlsx). WinCC VBScript accomplishes this by instantiating the Microsoft Excel COM automation server via CreateObject("Excel.Application").

As operational events occur throughout the day, new records are appended sequentially to the spreadsheet. By default, appended data appears chronologically with the oldest records at the top and the newest records at the very bottom. For executive dashboards, supervisors mandate that the table be automatically sorted so that the newest timestamp appears at row 2 immediately beneath the column headers.

Implementing the Excel Range.Sort Method in VBScript

Microsoft Excel provides the native Range.Sort method to sort arbitrary tabular ranges. However, in standalone VBScript (which is late-bound without access to Excel type libraries), named constants like xlDescending or xlYes do not exist. If an engineer types Order1:=xlDescending without defining the constant, VBScript evaluates xlDescending as Empty (value 0), causing the sort method to fail or default to ascending order.

To successfully execute the sort in WinCC VBScript, the integer values of the Excel enumeration constants must be defined explicitly at the beginning of the script:

Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1 (Header present)
Const xlNo = 2 (No header)

Syntax and Parameter Configuration for Descending Chronological Sort

To sort data dynamically based on the total number of populated rows, calculate the last row using UsedRange or End(xlUp):

Dim lastRow, sortRange, keyCell
lastRow = objSheet.Cells(objSheet.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
Set sortRange = objSheet.Range("A1:F" & lastRow)
Set keyCell = objSheet.Range("A2")

sortRange.Sort keyCell, 2, , , , , , 1 ' 2 = xlDescending, 1 = xlYes

Here, sortRange includes the header row, keyCell identifies Column A (Fecha y Hora) as the sort key, 2 enforces descending order, and 1 informs Excel to preserve the header row at row 1.

Complete Production-Grade WinCC VBScript Implementation

The following script demonstrates the complete, production-grade implementation including file creation, formatting, sorting, saving, and memory leak prevention:

Sub ExportAndSortExcelReport()
    Const xlDescending = 2
    Const xlYes = 1
    Const xlUp = -4162

    Dim objExcel, objBook, objSheet, objFSO
    Dim filePath, lastRow, sortRange, keyRange
    filePath = "D:\Historico.xlsx"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    objExcel.DisplayAlerts = False

    If objFSO.FileExists(filePath) Then
        Set objBook = objExcel.Workbooks.Open(filePath)
        Set objSheet = objBook.Worksheets(1)
    Else
        Set objBook = objExcel.Workbooks.Add
        Set objSheet = objBook.Worksheets(1)
        objSheet.Name = "Historico"
        objSheet.Range("A1:F1").Value = Array("Fecha y Hora", "Entrada Inf", "Entrada Sup", "Total", "Temp In", "Temp Out")
        objSheet.Range("A1:F1").Font.Bold = True
    End If

    ' Determine last populated row
    lastRow = objSheet.Cells(objSheet.Rows.Count, 1).End(xlUp).Row

    ' Perform Sort only if data rows exist
    If lastRow > 1 Then
        Set sortRange = objSheet.Range("A1:F" & lastRow)
        Set keyRange = objSheet.Range("A2")
        sortRange.Sort keyRange, xlDescending, , , , , , xlYes
    End If

    ' Save and clean up COM objects to prevent memory leak
    objBook.SaveAs filePath
    objBook.Close False
    objExcel.Quit

    Set keyRange = Nothing
    Set sortRange = Nothing
    Set objSheet = Nothing
    Set objBook = Nothing
    Set objExcel = Nothing
    Set objFSO = Nothing
End Sub

Excel COM Constants Reference Matrix for VBScript

The following table provides the required numeric constant values for Excel COM automation in WinCC VBScript:

Excel Enum ConstantNumeric ValuePurpose in VBScript Range.SortxlAscending1Sorts values from lowest to highest (A-Z, oldest to newest)xlDescending2Sorts values from highest to lowest (Z-A, newest to oldest)xlYes1First row of range contains column headers; do not sort headerxlNo2Range contains no headers; sort entire block of rowsxlGuess0Excel guesses if headers exist (Avoid in automation)xlUp-4162Directional constant used in Range.End() to find last row

Verification

Verify script execution and process isolation through the following steps:

Trigger the VBScript from a button click in WinCC Graphics Designer or via a scheduled cyclic action.
Open D:\Historico.xlsx in Excel. Verify that row 1 retains bold headers.
Verify that row 2 contains the most recent timestamp and that subsequent rows descend chronologically.
Open Windows Task Manager on the OS station. Check the Details tab; verify that EXCEL.EXE terminates immediately upon script completion and does not remain running as an orphaned background process.

Limitations and Operational Precautions

Consider these operational rules when automating Excel from WinCC:

Never Display Dialog Boxes: Always set objExcel.DisplayAlerts = False and objExcel.Visible = False; an unexpected save-as prompt will freeze the VBScript execution thread indefinitely, blocking WinCC user interaction.
Orphaned EXCEL.EXE Accumulation: If a script errors out before reaching objExcel.Quit, an orphaned EXCEL.EXE process remains in memory. Over weeks of operation, hundreds of orphaned processes will consume all RAM, crashing the OS Server.
Excel Installation on OS Servers: In mission-critical DCS installations, running desktop Office software on runtime servers is discouraged by cybersecurity standards; prefer exporting CSV files or using SIMATIC Information Server.

Engineer FAQ

How do I find the last populated row in an Excel column in VBScript?

Use objSheet.Cells(objSheet.Rows.Count, 1).End(-4162).Row, where -4162 is the numeric value for the xlUp direction constant.

What happens if a user opens D:\Historico.xlsx while the script runs?

The script will encounter a file-lock sharing violation (Permission Denied). Implement error handling (On Error Resume Next) to log the lock and retry later.

Is there an alternative to Excel COM automation for exporting WinCC data?

Yes, writing directly to text-based CSV files using Scripting.FileSystemObject is much faster, consumes zero Office licenses, and eliminates background process crashes.