WinCC VBScripting Practice: 28 Hands-On SCADA Examples

Practice 01 — Create Internal WinCC Tags

Build the WinCC tag database used by every later VBScript, SQL and reporting practical.

1. Open WinCC Explorer → Tag Management → Internal Tags.
2. Create the process tags below with names that match the SQL project.
3. Use Binary for status/commands, a floating-point type for process values, a 32-bit integer for IDs/SQF number, and Text for charge/event strings.

Practice 02 — Read a WinCC Tag Using VBScript

Read an actual process value from WinCC Runtime.

1. Create a button in Graphics Designer.
2. Open the button Mouse Click event and select VBScript.
3. Paste the script and start Runtime.

Dim Temp
Temp = HMIRuntime.Tags("Temp_Act").Read
MsgBox "Actual Temperature = " & Temp & " °C"

Dim TestValue, ResultText
ResultText = ""
TestValue = InputBox("Enter simulated value for Temp_Act", "Easy Test 1 — InputBox + MsgBox", "825.5")
ResultText = ResultText & "Temp_Act = " & TestValue & vbCrLf
MsgBox ResultText & vbCrLf & "Now compare these values with the original HMIRuntime .Read example.", vbInformation, "Tag Read Quick Test"

Practice 03 — Write a WinCC Tag

Write a command or setpoint from VBScript into a WinCC tag.

1. Create a test button.
2. Use the Write method on the required WinCC tag.
3. Verify the changed value in an I/O field or Tag Management diagnostics.

HMIRuntime.Tags("Temp_Set").Write 850
HMIRuntime.Tags("ChargeNo").Write "CHG-001"

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Temp_Set", "Easy Test 2 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Temp_Set" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 04 — Start / Stop Motor Through Script

Control motor command tags from Graphics Designer buttons.

1. Create Start and Stop buttons.
2. Assign one script to each button.
3. Keep PLC-side permissives and safety logic authoritative in the controller.

' START button
HMIRuntime.Tags("Motor_Start").Write 1
HMIRuntime.Tags("Motor_Stop").Write 0

' STOP button
' HMIRuntime.Tags("Motor_Start").Write 0
' HMIRuntime.Tags("Motor_Stop").Write 1

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Motor_Start", "Easy Test 3 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Motor_Start" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 05 — Analog Value Monitoring

Evaluate temperature, carbon potential, oil and jacket values in Runtime.

1. Read the actual process tag.
2. Compare it against defined operating limits.
3. Show an operator status or set a WinCC alarm/interlock tag.

Dim Temp
Temp = HMIRuntime.Tags("Temp_Act").Read

If Temp >= 900 Then
    MsgBox "Critical Temperature = " & Temp & " °C"
ElseIf Temp >= 850 Then
    MsgBox "High Temperature = " & Temp & " °C"
Else
    MsgBox "Temperature Normal = " & Temp & " °C"
End If

Dim TestValue, ResultText
ResultText = ""
TestValue = InputBox("Enter simulated value for Temp_Act", "Easy Test 4 — InputBox + MsgBox", "825.5")
ResultText = ResultText & "Temp_Act = " & TestValue & vbCrLf
MsgBox ResultText & vbCrLf & "Now compare these values with the original HMIRuntime .Read example.", vbInformation, "Tag Read Quick Test"

Practice 06 — Setpoint Entry

Accept an operator-entered setpoint and write it only when the input is numeric.

1. Read the current setpoint as the InputBox default.
2. Validate the returned string with IsNumeric.
3. Convert and write the value to Temp_Set.

Dim Value
Value = InputBox("Enter Temperature Setpoint", _
                 "SQF Temperature Setpoint", _
                 HMIRuntime.Tags("Temp_Set").Read)

If Value <> "" Then
    If IsNumeric(Value) Then
        HMIRuntime.Tags("Temp_Set").Write CDbl(Value)
    Else
        MsgBox "Please enter a numeric value."
    End If
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Temp_Set", "Easy Test 5 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Temp_Set" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 07 — IF / ELSE Process Logic

Use process conditions to make a WinCC decision.

1. Read setpoint and actual temperature.
2. Compare PV against SP.
3. Write the Temp_High status according to the result.

Dim TempSet, TempAct
TempSet = HMIRuntime.Tags("Temp_Set").Read
TempAct = HMIRuntime.Tags("Temp_Act").Read

If TempAct > TempSet Then
    HMIRuntime.Tags("Temp_High").Write 1
Else
    HMIRuntime.Tags("Temp_High").Write 0
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Temp_Set", "Easy Test 6 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Temp_Set" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 08 — Multiple-Condition Interlock

Combine temperature, CP and oil conditions into one process interlock.

1. Read all set and actual values.
2. Use OR conditions for abnormal states.
3. Set Process_Interlock and remove the start request when any limit is exceeded.

Dim TempSet, TempAct, CpSet, CpAct, OilSet, OilAct
TempSet = HMIRuntime.Tags("Temp_Set").Read
TempAct = HMIRuntime.Tags("Temp_Act").Read
CpSet = HMIRuntime.Tags("Cp_Set").Read
CpAct = HMIRuntime.Tags("Cp_Act").Read
OilSet = HMIRuntime.Tags("Oil_Set").Read
OilAct = HMIRuntime.Tags("Oil_Act").Read

If TempAct > TempSet + 30 Or _
   CpAct > CpSet + 0.2 Or _
   OilAct > OilSet + 10 Then
    HMIRuntime.Tags("Process_Interlock").Write 1
    HMIRuntime.Tags("Motor_Start").Write 0
Else
    HMIRuntime.Tags("Process_Interlock").Write 0
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Temp_Set", "Easy Test 7 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Temp_Set" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 09 — InputBox Operator Entry

Collect a charge number from the operator.

1. Open an InputBox from a button event.
2. Reject an empty response.
3. Write the accepted charge number to ChargeNo.

Dim Charge
Charge = InputBox("Enter Charge Number", "SQF Charge Entry")

If Charge <> "" Then
    HMIRuntime.Tags("ChargeNo").Write Charge
    MsgBox "Charge Number Updated: " & Charge
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: ChargeNo", "Easy Test 8 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: ChargeNo" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 10 — MsgBox Confirmation

Require operator confirmation before an action.

1. Create the confirmation message.
2. Check whether the operator selected Yes.
3. Execute the command only after confirmation.

Dim Answer
Answer = MsgBox("Do you want to start the SQF process?", _
                vbYesNo + vbQuestion, _
                "Process Confirmation")

If Answer = vbYes Then
    HMIRuntime.Tags("Motor_Start").Write 1
Else
    HMIRuntime.Tags("Motor_Start").Write 0
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Motor_Start", "Easy Test 9 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Motor_Start" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 11 — Screen Navigation

Navigate between WinCC process, trend and report pictures.

1. Confirm the target PDL picture names in Graphics Designer.
2. Assign the navigation script to menu buttons.
3. Test navigation in Runtime.

' Open process screen
HMIRuntime.BaseScreenName = "SQF_Process.pdl"

' Other examples:
' HMIRuntime.BaseScreenName = "SQF_Trend.pdl"
' HMIRuntime.BaseScreenName = "SQF_Report.pdl"

Dim TestState
TestState = InputBox("Enter test state: 1 = ON / 0 = OFF", "Easy Test 10 — InputBox + MsgBox", "1")
If TestState = "1" Then
    MsgBox "Expected Runtime result: object/screen should show the ACTIVE state.", vbInformation, "Graphics Quick Test"
Else
    MsgBox "Expected Runtime result: object/screen should show the INACTIVE state.", vbInformation, "Graphics Quick Test"
End If

Practice 12 — Popup Handling

Open and close an equipment popup using a Picture Window object.

1. Create a Picture Window named PW_ProcessPopup.
2. Create the popup picture SQF_Popup.pdl.
3. Set PictureName and Visible from the VBScript event.

' Open popup
ScreenItems("PW_ProcessPopup").PictureName = "SQF_Popup.pdl"
ScreenItems("PW_ProcessPopup").Visible = True

' Close popup example:
' ScreenItems("PW_ProcessPopup").Visible = False

Dim TestState
TestState = InputBox("Enter test state: 1 = ON / 0 = OFF", "Easy Test 11 — InputBox + MsgBox", "1")
If TestState = "1" Then
    MsgBox "Expected Runtime result: object/screen should show the ACTIVE state.", vbInformation, "Graphics Quick Test"
Else
    MsgBox "Expected Runtime result: object/screen should show the INACTIVE state.", vbInformation, "Graphics Quick Test"
End If

Practice 13 — Dynamic Object Properties

Change a graphic object at Runtime according to a WinCC tag.

1. Create a rectangle named RECT_TempStatus.
2. Read Temp_High.
3. Change visibility or another supported object property.

Dim AlarmState
AlarmState = HMIRuntime.Tags("Temp_High").Read

If AlarmState = 1 Then
    ScreenItems("RECT_TempStatus").Visible = True
Else
    ScreenItems("RECT_TempStatus").Visible = False
End If

Dim TestState
TestState = InputBox("Enter test state: 1 = ON / 0 = OFF", "Easy Test 12 — InputBox + MsgBox", "1")
If TestState = "1" Then
    MsgBox "Expected Runtime result: object/screen should show the ACTIVE state.", vbInformation, "Graphics Quick Test"
Else
    MsgBox "Expected Runtime result: object/screen should show the INACTIVE state.", vbInformation, "Graphics Quick Test"
End If

Practice 14 — FOR Loop

Execute repetitive VBScript logic with a controlled loop.

1. Declare the loop counter.
2. Run the loop from 1 to 10.
3. Use HMIRuntime.Trace to observe execution in diagnostics.

Dim i
For i = 1 To 10
    HMIRuntime.Trace "WinCC VBScript Loop = " & i & vbCrLf
Next

Dim CountTo, i, ResultText
CountTo = CInt(InputBox("How many loop cycles?", "Easy Test 13 — InputBox + MsgBox", "5"))
ResultText = ""
For i = 1 To CountTo
    ResultText = ResultText & "Cycle " & i & vbCrLf
Next
MsgBox ResultText, vbInformation, "Loop Quick Test"

Practice 15 — Date / Time Processing

Create SQL/report-friendly date and time values.

1. Use Now for the authoritative current date/time.
2. Build the legacy TM display string as HH:MM:SS.
3. Store DT as datetime2(3) in SQL Server and treat TM only as a display field.

Dim DT, TM
DT = Now
TM = Right("0" & Hour(Now), 2) & ":" & _
     Right("0" & Minute(Now), 2) & ":" & _
     Right("0" & Second(Now), 2)

MsgBox "DT = " & DT & vbCrLf & "TM = " & TM

Dim LabelText, TM
LabelText = InputBox("Enter a label for this timestamp", "Easy Test 14 — InputBox + MsgBox", "SQF Event")
TM = Right("0" & Hour(Now), 2) & ":" & _
     Right("0" & Minute(Now), 2) & ":" & _
     Right("0" & Second(Now), 2)
MsgBox LabelText & vbCrLf & _
       "Date/Time = " & Now & vbCrLf & _
       "TM = " & TM, vbInformation, "Date/Time Quick Test"

Practice 16 — Error Handling

Prevent external communication errors from terminating the Runtime action without a useful diagnostic.

1. Enable On Error Resume Next only around the operation that may fail.
2. Check Err.Number immediately after the operation.
3. Report/trace the error, clear it and restore normal error handling.

On Error Resume Next

Dim Temp
Temp = HMIRuntime.Tags("Temp_Act").Read

If Err.Number <> 0 Then
    HMIRuntime.Trace "Error " & Err.Number & ": " & Err.Description & vbCrLf
    Err.Clear
Else
    MsgBox "Temperature = " & Temp
End If

On Error GoTo 0

On Error Resume Next
Dim A, B, Result
A = CDbl(InputBox("Enter numerator", "Easy Test 15 — InputBox + MsgBox", "10"))
B = CDbl(InputBox("Enter divisor (try 0)", "Easy Test 15 — InputBox + MsgBox", "0"))
Result = A / B

If Err.Number <> 0 Then
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error Quick Test"
    Err.Clear
Else
    MsgBox "Result = " & Result, vbInformation, "Error Quick Test"
End If
On Error GoTo 0

Practice 17 — Create SQL Database

Create the SQL Server database used by the WinCC reporting project.

1. Open SQL Server Management Studio.
2. Run the idempotent database creation script.
3. Verify SQF_DB in sys.databases.

IF DB_ID(N'SQF_DB') IS NULL
BEGIN
    CREATE DATABASE [SQF_DB];
END;
GO

SELECT name
FROM sys.databases
WHERE name = 'SQF_DB';
GO

Dim DbName, Answer
DbName = InputBox("Enter database name for this lab", "Easy Test 16 — InputBox + MsgBox", "SQF_DB")
Answer = MsgBox("DDL PREVIEW ONLY" & vbCrLf & _
                "Database: " & DbName & vbCrLf & _
                "Review the SQL above in SSMS before executing it." & vbCrLf & vbCrLf & _
                "Do you understand what object will be created?", _
                vbYesNo + vbQuestion, "SQL DDL Quick Test")

If Answer = vbYes Then
    MsgBox "Good. Next, run the original SQL in SSMS on the training database.", vbInformation, "Quick Test"
Else
    MsgBox "Re-read the CREATE DATABASE / CREATE TABLE section first.", vbExclamation, "Quick Test"
End If

Practice 18 — Create dbo.tblEvent

Create the exact event/process table used by the WinCC VBScript examples.

1. Select SQF_DB.
2. Create dbo.tblEvent only if it does not already exist.
3. Keep DT as the authoritative datetime2(3) timestamp and TM as the legacy display value.

USE [SQF_DB];
GO

IF OBJECT_ID(N'dbo.tblEvent', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[tblEvent]
    (
        [ID]             int IDENTITY(1,1) NOT NULL,
        [DT]             datetime2(3) NOT NULL,
        [TM]             varchar(10) NULL,
        [SQF_No]         int NULL,
        [ChargeNo]       varchar(50) NULL,
        [Event_From]     varchar(100) NULL,
        [Event_To]       varchar(100) NULL,
        [Temp_Set]       decimal(9,3) NULL,
        [Temp_Act]       decimal(9,3) NULL,
        [Cp_Set]         decimal(9,3) NULL,
        [Cp_Act]         decimal(9,3) NULL,
        [Oil_Set]        decimal(9,3) NULL,
        [Oil_Act]        decimal(9,3) NULL,
        [Jacket_Set]     decimal(9,3) NULL,
        [Jacket_Act]     decimal(9,3) NULL,
        [Fan_Status]     bit NULL,
        CONSTRAINT [PK_tblEvent]
            PRIMARY KEY CLUSTERED ([ID])
    );
END;
GO

Dim DbName, Answer
DbName = InputBox("Enter database name for this lab", "Easy Test 17 — InputBox + MsgBox", "SQF_DB")
Answer = MsgBox("DDL PREVIEW ONLY" & vbCrLf & _
                "Database: " & DbName & vbCrLf & _
                "Review the SQL above in SSMS before executing it." & vbCrLf & vbCrLf & _
                "Do you understand what object will be created?", _
                vbYesNo + vbQuestion, "SQL DDL Quick Test")

If Answer = vbYes Then
    MsgBox "Good. Next, run the original SQL in SSMS on the training database.", vbInformation, "Quick Test"
Else
    MsgBox "Re-read the CREATE DATABASE / CREATE TABLE section first.", vbExclamation, "Quick Test"
End If

Practice 19 — Connect WinCC with SQL Server

Test an ADO connection from WinCC Runtime to SQF_DB.

1. Replace YOUR_SERVER\INSTANCE with the real SQL Server instance.
2. Use Windows authentication when the Runtime account has permission.
3. Show the connection result and always close the connection.

On Error Resume Next

Dim Conn
Set Conn = CreateObject("ADODB.Connection")

Conn.Open "Provider=MSOLEDBSQL;" & _
          "Server=YOUR_SERVER\INSTANCE;" & _
          "Database=SQF_DB;" & _
          "Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    HMIRuntime.Tags("SQL_Status").Write "SQL CONNECTION FAILED"
    MsgBox "SQL Connection Failed" & vbCrLf & Err.Description
    Err.Clear
Else
    HMIRuntime.Tags("SQL_Status").Write "SQL CONNECTED"
    MsgBox "SQL Server Connection Successful"
End If

If Conn.State = 1 Then Conn.Close
Set Conn = Nothing
On Error GoTo 0

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 18 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

Practice 20 — Insert WinCC Tags into SQL

Save the complete SQF process snapshot into dbo.tblEvent.

1. Read the WinCC tags.
2. Escape text values and normalize decimal text for SQL.
3. Insert DT with SYSDATETIME() and store TM as HH:MM:SS.

On Error Resume Next
Dim Conn, SQL, TM
Dim SQFNo, Charge, EventFrom, EventTo
Dim TempSet, TempAct, CpSet, CpAct, OilSet, OilAct, JacketSet, JacketAct, FanStatus

SQFNo = HMIRuntime.Tags("SQF_No").Read
Charge = Replace(CStr(HMIRuntime.Tags("ChargeNo").Read), "'", "''")
EventFrom = Replace(CStr(HMIRuntime.Tags("Event_From").Read), "'", "''")
EventTo = Replace(CStr(HMIRuntime.Tags("Event_To").Read), "'", "''")
TempSet = Replace(CStr(HMIRuntime.Tags("Temp_Set").Read), ",", ".")
TempAct = Replace(CStr(HMIRuntime.Tags("Temp_Act").Read), ",", ".")
CpSet = Replace(CStr(HMIRuntime.Tags("Cp_Set").Read), ",", ".")
CpAct = Replace(CStr(HMIRuntime.Tags("Cp_Act").Read), ",", ".")
OilSet = Replace(CStr(HMIRuntime.Tags("Oil_Set").Read), ",", ".")
OilAct = Replace(CStr(HMIRuntime.Tags("Oil_Act").Read), ",", ".")
JacketSet = Replace(CStr(HMIRuntime.Tags("Jacket_Set").Read), ",", ".")
JacketAct = Replace(CStr(HMIRuntime.Tags("Jacket_Act").Read), ",", ".")
FanStatus = HMIRuntime.Tags("Fan_Status").Read
TM = Right("0" & Hour(Now),2) & ":" & Right("0" & Minute(Now),2) & ":" & Right("0" & Second(Now),2)

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"

SQL = "INSERT INTO dbo.tblEvent (DT,TM,SQF_No,ChargeNo,Event_From,Event_To," & _
      "Temp_Set,Temp_Act,Cp_Set,Cp_Act,Oil_Set,Oil_Act,Jacket_Set,Jacket_Act,Fan_Status) VALUES (" & _
      "SYSDATETIME(),'" & TM & "'," & SQFNo & ",'" & Charge & "','" & EventFrom & "','" & EventTo & "'," & _
      TempSet & "," & TempAct & "," & CpSet & "," & CpAct & "," & OilSet & "," & OilAct & "," & _
      JacketSet & "," & JacketAct & "," & FanStatus & ")"

Conn.Execute SQL
If Err.Number <> 0 Then
    HMIRuntime.Tags("SQL_Status").Write "INSERT FAILED"
    MsgBox Err.Description
    Err.Clear
Else
    HMIRuntime.Tags("SQL_Status").Write "DATA SAVED"
End If

If Conn.State = 1 Then Conn.Close
Set Conn = Nothing
On Error GoTo 0

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 19 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 19 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

Practice 21 — Read SQL Records

Read the latest historical row and load selected fields into WinCC tags.

1. Open an ADODB.Connection and ADODB.Recordset.
2. Select TOP 1 ordered by ID descending.
3. Check EOF before accessing fields.

Dim Conn, RS, SQL
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")

Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"
SQL = "SELECT TOP 1 * FROM dbo.tblEvent ORDER BY ID DESC"
RS.Open SQL, Conn

If Not RS.EOF Then
    HMIRuntime.Tags("SQL_ID").Write RS.Fields("ID").Value
    HMIRuntime.Tags("ChargeNo").Write RS.Fields("ChargeNo").Value
    HMIRuntime.Tags("Temp_Set").Write RS.Fields("Temp_Set").Value
    HMIRuntime.Tags("Temp_Act").Write RS.Fields("Temp_Act").Value
    HMIRuntime.Tags("Fan_Status").Write RS.Fields("Fan_Status").Value
Else
    MsgBox "No SQL records found."
End If

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 20 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

Practice 22 — Update SQL Records

Modify a selected tblEvent row by ID.

1. Load or enter the record ID into SQL_ID.
2. Read the latest operator values.
3. Execute UPDATE with a WHERE ID filter.

Dim Conn, SQL, ID, Charge, TempSet, TempAct
ID = HMIRuntime.Tags("SQL_ID").Read
Charge = Replace(CStr(HMIRuntime.Tags("ChargeNo").Read), "'", "''")
TempSet = Replace(CStr(HMIRuntime.Tags("Temp_Set").Read), ",", ".")
TempAct = Replace(CStr(HMIRuntime.Tags("Temp_Act").Read), ",", ".")

If ID > 0 Then
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"
    SQL = "UPDATE dbo.tblEvent SET ChargeNo='" & Charge & "',Temp_Set=" & TempSet & ",Temp_Act=" & TempAct & " WHERE ID=" & ID
    Conn.Execute SQL
    Conn.Close
    Set Conn = Nothing
Else
    MsgBox "Enter a valid SQL ID."
End If

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 21 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 21 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

Practice 23 — Delete SQL Records

Delete one selected historical record safely after operator confirmation.

1. Read SQL_ID.
2. Ask for explicit confirmation.
3. Execute DELETE with a WHERE ID condition only after Yes.

Dim Conn, SQL, ID, Answer
ID = HMIRuntime.Tags("SQL_ID").Read

If ID > 0 Then
    Answer = MsgBox("Delete SQL Record ID " & ID & "?", vbYesNo + vbExclamation, "Confirm Delete")
    If Answer = vbYes Then
        Set Conn = CreateObject("ADODB.Connection")
        Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"
        SQL = "DELETE FROM dbo.tblEvent WHERE ID=" & ID
        Conn.Execute SQL
        Conn.Close
        Set Conn = Nothing
    End If
Else
    MsgBox "Enter a valid SQL ID."
End If

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 22 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 22 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

Practice 24 — Search Historical Records

Find the latest database record for a charge number.

1. Ask the operator for ChargeNo.
2. Escape apostrophes in the search text.
3. Select the latest matching row and display its ID/time.

Dim Conn, RS, SQL, Charge
Charge = InputBox("Enter Charge Number", "Historical Search")

If Charge <> "" Then
    Charge = Replace(Charge, "'", "''")
    Set Conn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"
    SQL = "SELECT TOP 1 * FROM dbo.tblEvent WHERE ChargeNo='" & Charge & "' ORDER BY DT DESC"
    RS.Open SQL, Conn

    If Not RS.EOF Then
        HMIRuntime.Tags("SQL_ID").Write RS.Fields("ID").Value
        MsgBox "Record found" & vbCrLf & "ID = " & RS.Fields("ID").Value & vbCrLf & "DT = " & RS.Fields("DT").Value
    Else
        MsgBox "Charge number not found."
    End If

    RS.Close
    Conn.Close
    Set RS = Nothing
    Set Conn = Nothing
End If

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 23 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

Practice 25 — Log Alarms into SQL

Store a high-temperature transition as an event record in tblEvent.

1. Use a one-shot/edge condition so one alarm transition produces one row.
2. Read the relevant process values.
3. Insert an event description such as HIGH TEMPERATURE ALARM.

Dim TempSet, TempAct
TempSet = HMIRuntime.Tags("Temp_Set").Read
TempAct = HMIRuntime.Tags("Temp_Act").Read

If TempAct > TempSet + 20 Then
    HMIRuntime.Tags("Event_From").Write "PROCESS"
    HMIRuntime.Tags("Event_To").Write "HIGH TEMPERATURE ALARM"
    ' Call the approved SQL event-insert action here once per alarm transition.
End If

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: Temp_Set", "Easy Test 24 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: Temp_Set" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 26 — Log Production Data

Record charge/process transitions such as Charging → Heating.

1. Set ChargeNo, Event_From and Event_To from operator/process state.
2. Capture the current set/actual values.
3. Execute the same validated insert routine used in Practice 20 at the event transition.

HMIRuntime.Tags("ChargeNo").Write "CHG-2026-001"
HMIRuntime.Tags("Event_From").Write "Charging"
HMIRuntime.Tags("Event_To").Write "Heating"

' Then execute the validated tblEvent INSERT routine
' at the actual production state transition.

Dim TestValue
TestValue = InputBox("Enter value to test for WinCC tag: ChargeNo", "Easy Test 25 — InputBox + MsgBox", "1")
If TestValue = "" Then
    MsgBox "Test cancelled.", vbInformation, "Tag Write Quick Test"
Else
    MsgBox "SIMULATION ONLY" & vbCrLf & _
           "Tag: ChargeNo" & vbCrLf & _
           "Value that would be written: " & TestValue & vbCrLf & vbCrLf & _
           "After checking interlocks, use the original .Write code above in Runtime.", _
           vbInformation, "Tag Write Quick Test"
End If

Practice 27 — Generate Excel Report

Export the complete tblEvent history from SQL Server into Microsoft Excel.

1. Query the required SQL columns.
2. Create Excel.Application and a new workbook.
3. Write headings and loop through every record.
4. AutoFit columns and save the workbook.

On Error Resume Next
Dim Conn, RS, SQL, XL, WB, WS, Row
Set Conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"

SQL = "SELECT ID,DT,TM,SQF_No,ChargeNo,Event_From,Event_To,Temp_Set,Temp_Act,Cp_Set,Cp_Act,Oil_Set,Oil_Act,Jacket_Set,Jacket_Act,Fan_Status FROM dbo.tblEvent ORDER BY DT"
RS.Open SQL, Conn

Set XL = CreateObject("Excel.Application")
XL.Visible = True
Set WB = XL.Workbooks.Add
Set WS = WB.Worksheets(1)
WS.Name = "SQF Event Report"

WS.Cells(1,1).Value = "ID"
WS.Cells(1,2).Value = "Date Time"
WS.Cells(1,3).Value = "Time"
WS.Cells(1,4).Value = "SQF No"
WS.Cells(1,5).Value = "Charge No"
WS.Cells(1,6).Value = "Event From"
WS.Cells(1,7).Value = "Event To"
WS.Cells(1,8).Value = "Temp Set"
WS.Cells(1,9).Value = "Temp Actual"
WS.Cells(1,10).Value = "CP Set"
WS.Cells(1,11).Value = "CP Actual"
WS.Cells(1,12).Value = "Oil Set"
WS.Cells(1,13).Value = "Oil Actual"
WS.Cells(1,14).Value = "Jacket Set"
WS.Cells(1,15).Value = "Jacket Actual"
WS.Cells(1,16).Value = "Fan Status"

Row = 2
Do While Not RS.EOF
    WS.Cells(Row,1).Value = RS.Fields("ID").Value
    WS.Cells(Row,2).Value = RS.Fields("DT").Value
    WS.Cells(Row,3).Value = RS.Fields("TM").Value
    WS.Cells(Row,4).Value = RS.Fields("SQF_No").Value
    WS.Cells(Row,5).Value = RS.Fields("ChargeNo").Value
    WS.Cells(Row,6).Value = RS.Fields("Event_From").Value
    WS.Cells(Row,7).Value = RS.Fields("Event_To").Value
    WS.Cells(Row,8).Value = RS.Fields("Temp_Set").Value
    WS.Cells(Row,9).Value = RS.Fields("Temp_Act").Value
    WS.Cells(Row,10).Value = RS.Fields("Cp_Set").Value
    WS.Cells(Row,11).Value = RS.Fields("Cp_Act").Value
    WS.Cells(Row,12).Value = RS.Fields("Oil_Set").Value
    WS.Cells(Row,13).Value = RS.Fields("Oil_Act").Value
    WS.Cells(Row,14).Value = RS.Fields("Jacket_Set").Value
    WS.Cells(Row,15).Value = RS.Fields("Jacket_Act").Value
    WS.Cells(Row,16).Value = RS.Fields("Fan_Status").Value
    Row = Row + 1
    RS.MoveNext
Loop

WS.Rows(1).Font.Bold = True
WS.Columns.AutoFit
WB.SaveAs "D:\SQF_Event_Report.xlsx"

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
Set WS = Nothing
Set WB = Nothing
Set XL = Nothing
On Error GoTo 0

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 26 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

Practice 28 — Date-Wise Excel Report

Generate a daily report using DT as the authoritative SQL timestamp.

1. Ask for a date in YYYY-MM-DD format.
2. Filter with a half-open range: DT >= date and DT < DATEADD(DAY,1,date).
3. Export only the returned rows to Excel and create a date-specific filename.

Dim ReportDate, Conn, RS, SQL, XL, WB, WS, Row, FileName
ReportDate = InputBox("Enter Report Date (YYYY-MM-DD)", "SQF Date-wise Report")

If ReportDate <> "" Then
    Set Conn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    Conn.Open "Provider=MSOLEDBSQL;Server=YOUR_SERVER\INSTANCE;Database=SQF_DB;Trusted_Connection=Yes;"

    SQL = "SELECT ID,DT,TM,SQF_No,ChargeNo,Event_From,Event_To,Temp_Set,Temp_Act,Cp_Set,Cp_Act,Oil_Set,Oil_Act,Jacket_Set,Jacket_Act,Fan_Status " & _
          "FROM dbo.tblEvent WHERE DT >= '" & ReportDate & "' AND DT < DATEADD(DAY,1,'" & ReportDate & "') ORDER BY DT"
    RS.Open SQL, Conn

    If RS.EOF Then
        MsgBox "No records found for " & ReportDate
    Else
        Set XL = CreateObject("Excel.Application")
        XL.Visible = True
        Set WB = XL.Workbooks.Add
        Set WS = WB.Worksheets(1)
        WS.Name = "SQF Daily Report"
        WS.Cells(1,1).Value = "SQF DAILY REPORT"
        WS.Cells(2,1).Value = "Report Date"
        WS.Cells(2,2).Value = ReportDate
        WS.Cells(4,1).Value = "ID"
        WS.Cells(4,2).Value = "Date Time"
        WS.Cells(4,3).Value = "SQF No"
        WS.Cells(4,4).Value = "Charge No"
        WS.Cells(4,5).Value = "Event From"
        WS.Cells(4,6).Value = "Event To"
        WS.Cells(4,7).Value = "Temp Set"
        WS.Cells(4,8).Value = "Temp Actual"

        Row = 5
        Do While Not RS.EOF
            WS.Cells(Row,1).Value = RS.Fields("ID").Value
            WS.Cells(Row,2).Value = RS.Fields("DT").Value
            WS.Cells(Row,3).Value = RS.Fields("SQF_No").Value
            WS.Cells(Row,4).Value = RS.Fields("ChargeNo").Value
            WS.Cells(Row,5).Value = RS.Fields("Event_From").Value
            WS.Cells(Row,6).Value = RS.Fields("Event_To").Value
            WS.Cells(Row,7).Value = RS.Fields("Temp_Set").Value
            WS.Cells(Row,8).Value = RS.Fields("Temp_Act").Value
            Row = Row + 1
            RS.MoveNext
        Loop

        WS.Rows(4).Font.Bold = True
        WS.Columns.AutoFit
        FileName = "D:\SQF_Report_" & Replace(ReportDate,"-","_") & ".xlsx"
        WB.SaveAs FileName
        MsgBox "Date-wise report created:" & vbCrLf & FileName
    End If

    RS.Close
    Conn.Close
    Set RS = Nothing
    Set Conn = Nothing
End If

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 27 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0