Overview
Siemens SIMATIC WinCC stores process values in a Tag Logging archive that is backed by Microsoft SQL Server. Exporting these archives to Excel is a common requirement for report generation, ad-hoc analysis, and offline trending. When the WinCC/Connectivity Pack is licensed, the official OLE DB path is the recommended approach, but engineers can also read archive data directly from a WinCC runtime system through the WinCCOLEDBProvider.1 COM interface using VBScript and ADODB, without requiring Microsoft Excel to be installed on the runtime PC.
This article documents the field-proven procedure for extracting approximately 1,000 timestamped values from a four-variable tag archive using a runtime-side VBScript. The procedure covers connection string construction, the Tag:R and Tag:A query syntax, slow/fast archive configuration decisions, and the licensing constraints that govern direct SQL access.
Prerequisites
Confirm the following before scripting the export:
- SIMATIC WinCC V6.0 SP3 or later (V6.2 / V7.0 / V7.2 / V7.4 / V7.5 all support the same OLE DB provider name; provider name does not change with major version).
- WinCC Runtime is active on the engineering station or a dedicated server. The provider is initialized only while the WinCC Runtime service is running.
- The Microsoft SQL Server instance installed by WinCC is online. Default instance name is
WINCCfor V6.x andWINCC<version>for V7.x. - Tag Logging archive is configured for the target variables, with valid archive IDs and acquisition cycles.
- VBScript execution rights are enabled in Computer Properties > Graphics Runtime > Scripting.
- Optional: a database query tool such as Microsoft SQL Server Management Studio (for V7.x) or Enterprise Manager (for V6.x) for verification only. Note: direct SQL access to the WinCC database outside the OLE DB provider is licensed to WinCC and is not permitted when the Connectivity Pack is absent.
WinCC Archive Architecture: Slow vs Fast
WinCC stores process values in two distinct archive configurations. The choice determines how the OLE DB provider returns data.
| Parameter | TagLoggingFast | TagLoggingSlow |
|---|---|---|
| Storage | Compressed (Huffman / rotation algorithm) | Uncompressed raw rows |
| Default cycle | 500 ms | User-defined (typically 1 s to 1 day) |
| Query return | Aggregated values when using Tag:R
|
Discrete timestamped rows |
| OLE DB read | Requires Connectivity Pack for direct SELECT | Readable via runtime OLE DB without Connectivity Pack |
| Typical use | High-speed acquisition, trend compression | Batch reports, regulatory logs, ad-hoc export |
Configuring Slow Archive for Direct Export
- Open the WinCC Explorer and navigate to Tag Logging > Archive Configuration.
- Right-click TagLoggingFast > Archive Contents and unmark Acyclic Measured Values. Set the cycle to 1 × 500 ms.
- Right-click TagLoggingSlow > Archive Configuration and set the time period for all single segments to the maximum (~5 GB).
- In TagLoggingSlow > Archive Contents, set the acquisition cycle to 1 × 500 ms and ensure each tag's acquisition cycle in the master data is greater than 1 × 500 ms.
- Reset both archives via right-click > Reset on each node. This clears the compressed cache and re-initializes the SQL storage.
WinCC OLE DB Provider Connection String
The OLE DB provider is registered as WinCCOLEDBProvider.1 on every WinCC runtime system. The canonical connection string has three parts: provider, catalog, and data source.
Provider=WinCCOLEDBProvider.1;Catalog=<DatabaseName>;Data Source=.\WinCC
The Catalog parameter is the SQL database that holds the runtime archive. The catalog name can be discovered at runtime through the internal tag ProjectDSN (data source name) which WinCC populates with the active project's database name. Append the suffix R to the catalog name when querying the runtime database as opposed to the configuration database. The runtime database is the one that holds the live tag values; the configuration database holds the engineering schema.
| Segment | Purpose | Example |
|---|---|---|
| Provider | COM class ID for the WinCC OLE DB provider | WinCCOLEDBProvider.1 |
| Catalog | SQL database name (runtime DB) | CC_Project_03_02_04_16_19_15R |
| Data Source | SQL Server instance (local default) | .\WinCC |
Query Syntax: Tag:R, Tag:A, and Tag:U
The WinCC OLE DB provider accepts a proprietary SQL dialect that wraps the OPEN operator around archive data. The three forms used in field deployments are:
| Form | Syntax | Use |
|---|---|---|
Tag:R |
Tag:R,<ValueID>,'<RelStart>','<RelStop>' |
Relative time range, compressed archive, returns interpolated/aggregated values |
Tag:A |
Tag:A,<ValueID>,'<AbsStart>','<AbsStop>' |
Absolute time range, compressed archive |
Tag:U |
Tag:U,<ValueID>,'<AbsStart>','<AbsStop>' |
Uncompressed archive, returns raw rows |
The ValueID is the internal numeric ID of an archive variable, not the tag name. The ID can be obtained from the WinCC Configuration Studio under Tag Logging > Archives > [Archive Name] > [Tag Name] > Properties > Archive / ID. A typical ID is a small integer such as 104 or 106.
Time format is 'YYYY-MM-DD HH:MM:SS.mmm'. A special sentinel of '0000-00-00 00:00:00.000' means "now" for the stop timestamp and "now minus offset" for the start timestamp when used in a Tag:R query.
VBScript Implementation for Runtime Export
The following VBScript runs inside a WinCC picture on a runtime button. It uses HMIRuntime.Tags and ScreenItems to read user parameters from input fields, then opens an ADODB connection, executes the archive query, and writes the result set to a ListView control. This is the same structure documented in the official Siemens export application example.
' ============================================================
' WinCC Tag Logging Export - VBScript skeleton
' Tested: WinCC V7.0 SP3 / V7.4 / V7.5
' Requires WinCC Runtime active
' ============================================================
Dim sPro, sDsn, sSer, sCon, sSql
Dim sVid, sStart, sStop, sVal, lRet, dVal, dGT
Dim conn, oRs, oCom, oList, oItem
Dim m, n, s
Dim objIOMinuten, objIOWert
Dim DSNName
' 0.0 Get parameters from picture
Set DSNName = HMIRuntime.Tags("ProjectDSN")
DSNName.Read
HMIRuntime.Trace "Value DSNName: " & DSNName.Read & vbCrLf
sDsn = DSNName.Value
' Tag logging archive variable ID (example: 106)
sVid = CStr(106)
Set objIOMinuten = ScreenItems("Minuten")
sStart = objIOMinuten.OutputValue ' minutes back, e.g. "05"
sStop = "0000-00-00 00:00:00.000" ' "now"
Set objIOWert = ScreenItems("Wert")
sVal = objIOWert.OutputValue ' filter threshold
' 1.1 Build OLE DB connection string
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog=" & sDsn & ";" ' append "R" if needed: Catalog=<name>R;
sSer = "Data Source=.\WinCC"
sCon = sPro & sDsn & sSer
HMIRuntime.Trace "sCon: " & sCon & vbCrLf
' 1.2 Build command text - relative time, compressed archive
sSql = "Tag:R," & sVid & _
",'0000-00-00 00:" & sStart & ":00.000'," & _
"'" & sStop & "'"
lRet = MsgBox("Open with:" & vbCr & sCon & vbCr & sSql & vbCr & sVal, _
vbOKCancel)
If lRet <> 1 Then Exit Sub
' 2.1 Open ADODB connection
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sCon
conn.CursorLocation = 3 ' adUseClient
conn.Open
' 2.2 Define command text
Set oRs = CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
oCom.CommandType = 1 ' adCmdText
Set oCom.ActiveConnection = conn
oCom.CommandText = sSql
' 2.3 Execute and read
Set oRs = oCom.Execute
m = oRs.Fields.Count
' 3.0 Populate ListView control
Set oList = ScreenItems("ListTable")
oList.ListItems.Clear
If (m > 0) Then
oRs.MoveFirst
n = 0
dGT = CDbl(sVal)
Do While Not oRs.EOF
n = n + 1
dVal = oRs.Fields(2).Value ' field 2 = measured value
If dVal > dGT Then
s = Left(CStr(oRs.Fields(1).Value), 23)
Set oItem = oList.ListItems.Add()
oItem.Text = Left(CStr(oRs.Fields(1).Value), 23)
oItem.SubItems(1) = FormatNumber(dVal, 4)
oItem.SubItems(2) = Hex(oRs.Fields(4).Value)
End If
If (n > 100) Then Exit Do ' safety break
oRs.MoveNext
Loop
oRs.Close
End If
Set oRs = Nothing
Set conn = Nothing
3 for CursorLocation corresponds to adUseClient in MDAC. It is required for the WinCC OLE DB provider to materialize the result set into memory; using adUseServer (0) returns an empty record set for Tag:R queries.Recordset Field Layout
The Tag:R query returns a fixed column order for each row:
| Field index | Type | Description |
|---|---|---|
| 0 | Long | Value ID |
| 1 | String (Date) | Timestamp (YYYY-MM-DD HH:MM:SS.mmm) |
| 2 | Double | Real value (REAL/IEEE754) |
| 3 | Long | Quality code (WinCC quality flags) |
| 4 | Long | Flags / state field |
Exporting the Result Set to .CSV Without Excel Installed
When Microsoft Excel is not available on the runtime PC, write the result set directly to a CSV file using the FileSystemObject. The following snippet replaces the ListView population and is suitable for unattended export jobs triggered by a WinCC scheduler.
' Continue from the oRs.Execute above
Const ForWriting = 2
Dim fso, ts
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:\Export\ArchiveExport_" & _
Replace(Now, "/", "-") & ".csv", _
ForWriting, True)
ts.WriteLine "Timestamp;Value;QualityHex;FlagsHex"
If (m > 0) Then
oRs.MoveFirst
Do While Not oRs.EOF
ts.WriteLine Left(CStr(oRs.Fields(1).Value), 23) & ";" & _
FormatNumber(oRs.Fields(2).Value, 4) & ";" & _
Hex(oRs.Fields(3).Value) & ";" & _
Hex(oRs.Fields(4).Value)
oRs.MoveNext
Loop
oRs.Close
End If
ts.Close
Set ts = Nothing
Set fso = Nothing
The CSV file can then be opened in Excel on a separate engineering workstation, or imported into Python / Power BI for further analysis. The semicolon delimiter is the standard choice for European locales where the comma is the decimal separator.
Alternative: Connectivity Pack Path (When Licensed)
When the SIMATIC WinCC/Connectivity Pack is licensed, the preferred production path is the WinCC OLE DB Provider queried through the Connectivity Pack's COM proxy DLLs (CCAlgDatHdl.dll, CCArchiveHlpr.dll). Siemens documents this path, including a complete Excel VBA client, in the support entry referenced below. The Connectivity Pack path offers:
- Direct
SELECTagainstdbo.ARCHIVE,dbo.AT_XYZraw tables. - Ability to run scripts outside WinCC Runtime (Excel-side automation).
- Multiplexed read across multiple archive IDs in a single query.
- Stable programmatic interface across WinCC V7.x minor versions.
For an exhaustive walkthrough of the Excel-VBA client, the archive query wizard, and configuration of the Connectivity Pack, refer to the official Siemens support document: Export of archive data using the SIMATIC WinCC/Connectivity Pack (OLE DB Provider).
Licensing and Legal Constraints
SELECT statements from outside WinCC, is a license violation regardless of whether WinCC is in runtime mode.The runtime-side VBScript approach shown above is the supported method when the Connectivity Pack is not licensed. The script runs inside the WinCC Runtime process, uses the licensed OLE DB provider, and writes its output to a CSV. The CSV is then available for any external tool.
Verification Procedure
- Enable the WinCC Global Script diagnostic window: WinCC Explorer > Computer Properties > Graphics Runtime > Scripting > Activate Global Script diagnostic window.
- Trigger the export button. Confirm that the diagnostic window prints:
-
Value DSNName: <ProjectName>— confirms the internal tagProjectDSNis populated. -
sCon: Provider=WinCCOLEDBProvider.1;Catalog=...;Data Source=.\WinCC— confirms the connection string syntax. -
sSql: Tag:R,106,'0000-00-00 00:05:00.000','0000-00-00 00:00:00.000'— confirms the relative-time query.
-
- Check the
GSC Diagnosticswindow for any HRESULT errors returned fromconn.OpenoroCom.Execute. - Open the produced CSV in Notepad and confirm the row count matches the expected number of archived values for the chosen window.
- Open the CSV in Excel and confirm that the timestamp column parses as a real Excel date and the value column is numeric.
Troubleshooting Matrix
| Symptom | Likely cause | Corrective action |
|---|---|---|
conn.Open fails with E_FAIL |
WinCC Runtime not started, or wrong catalog suffix | Confirm WinCC Runtime is active; append R to the catalog name for the runtime database |
| Empty record set, no error | CursorLocation = adUseServer (0) on Tag:R queries |
Set conn.CursorLocation = 3
|
| No rows returned for fast archive without Connectivity Pack | Compressed storage requires Connectivity Pack for direct SELECT | Move the target variable to TagLoggingSlow or license the Connectivity Pack |
| Value IDs not found in configuration | Tags archived in a different archive name | Use the WinCC Configuration Studio to enumerate Value IDs per archive |
| Time mismatch of one hour | Runtime PC time zone not matching SQL Server | Synchronize both to UTC; do not use DST-local time in Tag:A queries |
Type mismatch on oRs.Fields(2).Value
|
Tag is a string, not a number | Cast with CStr for string tags; the field index 2 is always numeric for process value tags |
Error -2147467259 on oCom.Execute
|
Catalog name does not exist (case-sensitive on case-sensitive SQL collations) | Verify catalog name in SSMS / Enterprise Manager and match exactly |
| CSV file not created | Target directory missing or write permission denied | Pre-create C:\Export and grant the WinCC Runtime service account write rights |
Performance and Scaling Notes
- For 1,000 values the runtime OLE DB provider typically returns in under 2 seconds on a 1 GbE LAN.
- For export windows of more than 24 hours on a 500 ms cycle (~172,800 rows per tag), prefer the
Tag:Raggregated form with a coarser resolution to limit row count. - Set the safety break (
If n > 100 Then Exit Doin the skeleton) to the expected maximum row count to avoid runaway loops in case of an incorrectly built time range. - For four variables, do not multiplex the
Tag:Rquery; open four separateADODB.Recordsetobjects in parallel or sequence them. The provider does not support a singleSELECTover multiple Value IDs in the runtime OLE DB form. - Use the
Hex(oRs.Fields(4).Value)rendering for the flags field; this exposes the WinCC internal quality bits in a form that can be filtered in Excel.
How do I find the Value ID of an archive tag in WinCC?
Open the WinCC Configuration Studio, navigate to Tag Logging > [Archive Name] > [Tag Name], and read the ID from the property pane under Archive. Value IDs are small integers assigned by the configuration tool when the archive is first compiled.
Can I export the WinCC tag archive without the Connectivity Pack?
Yes. Run a VBScript inside WinCC Runtime that uses WinCCOLEDBProvider.1 through ADODB. Configure the variables in TagLoggingSlow (uncompressed) for direct SELECT access without a license. See the procedure in the VBScript Implementation section above.
Why does my Tag:R query return zero rows?
Three common causes: (1) the Value ID does not exist in the target archive, (2) the relative time window does not overlap with any archived values, or (3) CursorLocation is set to adUseServer (0). The provider requires adUseClient (3) for Tag:R queries.
How do I export to a true .xlsx file without Excel installed?
Export to CSV as shown above, then use a separate engineering workstation with Excel to open and re-save as .xlsx. For programmatic .xlsx creation without Excel, install the open-source EPPlus .NET library or LibreOffice headless on a non-runtime server; the WinCC Runtime PC should remain clean of Office components.
Is the runtime OLE DB provider licensed with every WinCC installation?
WinCCOLEDBProvider.1 COM class is part of the base WinCC license. The Connectivity Pack is required only for direct SELECT statements against the SQL archive tables from outside the WinCC Runtime process (for example, from Excel VBA or a C# service).What is the maximum number of rows I can read in a single query?
Tag:R query returning more than ~500,000 rows will degrade performance and may time out. For larger windows, page the query by time range or use the Connectivity Pack's bulk methods.