Configuring WinCC OLE DB Provider for Excel Archive Export

Overview

The SIMATIC WinCC OLE DB Provider exposes Tag Logging and Alarm Logging runtime archive data to external Windows applications through the standard OLE DB interface. By binding a Microsoft Excel workbook to the provider through a UDL file, a Microsoft Query data source, or a small VBScript automation routine, you can pull historical process values and alarm events directly into a worksheet without manual CSV export or operator intervention. The provider ships with the WinCC Connectivity Pack and is the supported mechanism for direct archive access from WinCC V7.0 and later, including the TIA Portal WinCC Professional runtime variants.

The sample workbook OLE-DB Tag and Alarm Export.xls that Siemens ships for this purpose is preconfigured to query the provider, but it depends on three things being correct on the engineering station or the archive server: a registered WinCC OLE DB Provider of the matching major version, a valid UDL file that resolves to the running WinCC project, and an unlocked WinCC Runtime so the archive segment files (*.mdf/*.ldf under \<server>\<project>\ArchiveManager) are accessible. Errors such as The OLE DB provider could not be instantiated, Test connection failed because of an error in initializing provider, or Data source name not found and no default driver specified all trace back to one of the same three root causes: missing provider, mismatched version, or a protected runtime database.

This reference walks through the prerequisites, the UDL configuration, the SQL syntax for archive queries, and two fallback approaches (WinCC VBScript and SQL Server OPENQUERY via the WinCC Link Server) that cover cases where the OLE DB Provider cannot be installed.

Prerequisites

Before opening the Excel template, verify that the following software is installed on the machine that will host the workbook. The WinCC OLE DB Provider is version-bound to the WinCC Runtime it was compiled against: a Connectivity Pack V7.4 will not register against a WinCC V7.3 Runtime, and the TIA Portal variant is not interchangeable with the classic WinCC V7.x provider.

How the WinCC OLE DB Provider Is Registered

The installer places WinCCOLEDBProvider.dll in %ProgramFiles%\Siemens\Automation\WinCC\bin and registers the ProgID WinCCOLEDBProvider.1 under HKEY_CLASSES_ROOT. You can confirm registration from an elevated command prompt:

reg query "HKCR\WinCCOLEDBProvider.1" /s
regsvr32 "%ProgramFiles%\Siemens\Automation\WinCC\bin\WinCCOLEDBProvider.dll"

If the DLL is missing, the regsvr32 command returns 0x8007007E The specified module could not be found. If the dependency WinCCUtil.dll is missing or mismatched in version, the call returns 0x80029C4A Error loading type library/DLL. Reinstalling the Connectivity Pack resolves both conditions.

Building the UDL Connection

The exported workbook expects a UDL file that resolves to a WinCC Runtime project. Create a file named WinCC.udl on the desktop or in the workbook folder, then double-click it to launch the OLE DB Select Data Source dialog.

1. On the Provider tab, pick WinCC OLE DB Provider for Archives. Two related entries appear in V7.3 and later: WinCC OLE DB Provider for Archives (runtime, read-only) and WinCC OLE DB Provider (configuration, used internally by the WinCC Explorer). Pick the Archives variant.
2. On the Connection tab, fill in the parameters from the table below. The most common mistakes are leaving Catalog blank or pointing it at the SQL Server catalog instead of the WinCC archive catalog.
3. Click Test Connection. A successful test returns Test connection succeeded; a failure returns an HRESULT that maps to one of the troubleshooting rows below.
4. Save the UDL and reference it from the workbook via Microsoft Query (Data > From Other Sources > From Microsoft Query) or by editing the workbook's existing data connection string.

The resulting connection string, which Microsoft Query writes into the workbook, looks like the example below:

Provider=WinCCOLEDBProvider.1;User ID=WinCCAdmin;Password=<pwd>;Data Source=.\WinCC;Catalog=CC_Plant_22R;
Persist Security Info=False

Querying Tag Logging Archives

Archive data is exposed through a virtual schema named Archive. Each Tag Logging archive is represented as a column-prefixed view: TLG_F<archiveID> for fast (process value) archives and TLG_S<archiveID> for slow (statistics) archives. The standard query selects a time range using the Timestamp column and the WHERE clause with literal or RFC-compliant date literals.

SELECT Timestamp, RealValue, Quality
FROM Archive
WHERE Timestamp BETWEEN '2024-01-15 06:00:00.000' AND '2024-01-15 18:00:00.000'
  AND ArchiveName = 'ProcessArchive'
ORDER BY Timestamp ASC

For aggregation over a window, use GROUP BY with the AVG, MIN, MAX, SUM, or COUNT aggregate functions. The provider implements the standard OLE DB aggregation grammar; subqueries and joins across archives are allowed but evaluate locally because the WinCC OLE DB Provider does not push computation to SQL Server.

SELECT TOP 1000
   DATEPART(YEAR, Timestamp)  AS [Year],
   DATEPART(MONTH, Timestamp) AS [Month],
   AVG(RealValue)             AS [AvgValue],
   MIN(RealValue)             AS [MinValue],
   MAX(RealValue)             AS [MaxValue]
FROM Archive
WHERE ArchiveName = 'ProcessArchive'
  AND RealValue > 0
GROUP BY DATEPART(YEAR, Timestamp), DATEPART(MONTH, Timestamp)
ORDER BY [Year], [Month]

Querying Alarm Logging Archives

Alarm archives are exposed under the same Archive schema but with the columns defined by the alarm message structure. Common columns are MsgNr, State, Priority, MsgText, TimeCome, TimeGo, AckTime, and ComputerName. A typical report query:

SELECT TimeCome, MsgNr, Priority, State, MsgText
FROM Archive
WHERE TimeCome BETWEEN '2024-01-15 00:00:00.000' AND '2024-01-16 00:00:00.000'
  AND Priority >= 8
ORDER BY TimeCome DESC

The provider returns State as an integer that decodes through the WinCC Alarm Logging configuration: 1 = Came In, 2 = Came In / Acknowledged, 3 = Went Out, 4 = Acknowledged, 5 = Went Out / Acknowledged. Convert these in Excel with a VLOOKUP table if you need plain-language status.

Using the WinCC Link Server (OPENQUERY Path)

Where installing the Connectivity Pack is not possible (locked-down operator stations, Citrix, or a 64-bit-only Excel build), use the SQL Server Linked Server mechanism. The Connectivity Pack installer also registers an OLE DB provider named WinCC Link Server, which is the recommended provider for cross-process queries from a remote SQL Server.

1. Open SQL Server Management Studio on the machine that hosts SQL Server (or has the SQL client).
2. Expand Server Objects > Linked Servers > Providers and confirm WinCC Link Server is listed and enabled.
3. Create a linked server with the provider string WinCC Link Server and the product name WinCC.
4. Run the query from SSMS, then surface the result set in Excel through a standard SQL Server ODBC or OLE DB connection.

EXEC sp_addlinkedserver
   @server    = 'WINCC_ARCH',
   @srvproduct= 'WinCC',
   @provider  = 'WinCC Link Server',
   @datasrc   = '.\WinCC',
   @catalog   = 'CC_Plant_22R',
   @provstr   = 'User ID=WinCCAdmin;Password=<pwd>';

SELECT *
FROM OPENQUERY(WINCC_ARCH,
   'SELECT Timestamp, RealValue FROM Archive
    WHERE ArchiveName = ''ProcessArchive''
      AND Timestamp BETWEEN ''2024-01-15 06:00:00.000'' AND ''2024-01-15 18:00:00.000''');

Excel Workbook Configuration

Open the OLE-DB Tag and Alarm Export.xls workbook shipped by Siemens and follow this sequence if the connection is not pre-populated or has been replaced:

1. Enable the Developer tab: File > Options > Customize Ribbon > Developer.
2. Open Developer > Connections. The workbook ships with two named connections: TagLoggingExport and AlarmLoggingExport.
3. Select TagLoggingExport, click Properties, and on the Connection tab set Connection string to the UDL string built earlier. Enable Always use connection file and browse to the WinCC.udl you created.
4. On the Definition tab, set the command type to SQL and the command text to a parameterized archive query. Use square-bracket prompts to create user-supplied date/time filters, for example WHERE Timestamp > ?.
5. Repeat for AlarmLoggingExport.
6. Click Refresh All on the Data tab. Excel prompts for parameter values, the OLE DB query executes against the WinCC archive, and the result set populates the configured sheet.

For an operator-facing report where the user types a date and time and clicks a button, replace the parameter prompts with two named cells (B2 and B3) and a small VBA macro that rebuilds the connection string and refreshes the query:

Sub RefreshTagExport()
    Dim startDate As String, endDate As String
    startDate = Format(Range("B2").Value, "yyyy-mm-dd hh:nn:ss.000")
    endDate   = Format(Range("B3").Value, "yyyy-mm-dd hh:nn:ss.000")
    With ActiveWorkbook.Connections("TagLoggingExport").OLEDBConnection
        .CommandText = Array( _
            "SELECT Timestamp, RealValue, Quality FROM Archive " & _
            "WHERE ArchiveName = 'ProcessArchive' " & _
            "AND Timestamp BETWEEN '" & startDate & "' AND '" & endDate & "' " & _
            "ORDER BY Timestamp ASC")
        .Refresh
    End With
End Sub

VBScript Alternative (No OLE DB Provider Required)

If the operator workstation cannot install the Connectivity Pack, you can drive Excel from inside the WinCC Runtime with a Global Script action. WinCC Runtime exposes the HMIRuntime object, which can attach to an Excel instance through CreateObject("Excel.Application").

Dim objExcel, objWorkbook, objSheet
Set objExcel   = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet    = objWorkbook.Worksheets(1)

objSheet.Cells(1,1).Value = "Timestamp"
objSheet.Cells(1,2).Value = "Value"

Dim i, ts, val
i = 2
Dim conn, rs
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=WinCCOLEDBProvider.1;Data Source=.\WinCC;" & _
          "Catalog=CC_Plant_22R;User ID=WinCCAdmin;Password=<pwd>;"
Set rs = conn.Execute("SELECT TOP 1000 Timestamp, RealValue " & _
                       "FROM Archive WHERE ArchiveName='ProcessArchive' " & _
                       "ORDER BY Timestamp DESC")
Do Until rs.EOF
    objSheet.Cells(i,1).Value = rs.Fields(0).Value
    objSheet.Cells(i,2).Value = rs.Fields(1).Value
    i = i + 1
    rs.MoveNext
Loop
rs.Close
conn.Close

Verification

After the connection is configured, run the following checks before declaring the export working:

1. UDL test passes: double-click the UDL, click Test Connection, confirm Test connection succeeded.
2. SQL Server sees the catalog: from SSMS, expand Linked Servers > WINCC_ARCH > Catalogs; the CC_<Project> catalog must list the tag archive tables.
3. Excel returns rows: Data > Refresh All produces a non-empty result set. A blank sheet with the status No data was returned usually means the Runtime is deactivated or the Catalog name is wrong.
4. Time-range filter respected: edit the date prompts and confirm the row count and the first/last Timestamp match the requested window.
5. Repeated refresh stable: refresh ten times in a row. Intermittent failures on the 2nd or 3rd attempt indicate the UDL is caching the wrong credentials or the WinCC Runtime is recycling the archive connection pool.

Troubleshooting Matrix

Performance and Sizing Notes

The WinCC OLE DB Provider streams archive segments sequentially and does not use SQL Server indexes; a query that scans a full year of a 1 Hz process archive on a 10,000-tag plant can return several million rows. To keep Excel responsive, restrict the time window to the smallest range that satisfies the report, use TOP N when only a preview is needed, and prefer the TLG_S aggregated archives for shift and daily reports instead of recomputing aggregates from TLG_F on every refresh. For multi-million-row exports, route the query through SSMS or a WinCC user archive export job and load the resulting CSV into Excel with Power Query > From File > From Text/CSV; this avoids OLE DB row-by-row fetch overhead in Excel.

Related Official Documentation

• Siemens Support: Export of archive data using the SIMATIC WinCC Connectivity Pack (OLE DB Provider)
• Siemens Support: Export of WinCC / CAS Archive Data to Excel using the WinCC OLE DB Provider
• Siemens Industry Online Support portal for firmware and Connectivity Pack installers