WINCC VBS (VBScript) to open Shortcut

This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim sheel
Dim MyFilePath
MyFilePath = "D:\Manual.lnk"
Set sheel = CreateObject("wscript.Shell")
sheel.Exec "RUNDLL32.EXE URL.DLL,FileProtocolHandler " & MyFilePath

Wincc VBS(VBScript) to open PDF files

This script is for opening PDF file in Simatic Wincc projects. 

This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim sheel
Set sheel = CreateObject("wscript.Shell")
sheel.Exec "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe" + " " + "D:\123.pdf"
Set sheel = Nothing

Wincc VBS(VBScript) for MS Access Data Report

MS Access table >> MS Access Data Report

This script is useful in generating report from MS Access table using MS Access Data Report. Here no other reporting tool(such as crystal reports) required. Softwares such as dopdf, cutepdf etc will help for printing the report in .PDF format.

This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim db
Dim rpt 
Dim ctl 
Set db = CreateObject("Access.Application")  
db.OpenCurrentDatabase "D:\DATENSRJ\Database1.mdb", True
db.Visible = True
db.DoCmd.OpenReport "Temperature_Report"
db.DoCmd.Maximize
Set rpt = Nothing
db.CloseCurrentDatabase

Wincc VBS(VBScript) for close an application(.exe)

This script is useful in closing an application(.exe) in taskmanager. This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim kei
Set kei = CreateObject("Wscript.Shell")
kei.Run "taskkill /im Datalog.exe", , True
Set kei= Nothing

Table view in Simatic Comfort HMI Sample Project - (WINCC Comfort V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download

The same vbscript you can use for Wincc V13 Professional 

Excel Reports between two datetime Sample Project - (WINCC Advanced V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download

The same vbscript you can use for Wincc V13 Professional 

Write/Log Data to Database - (WINCC Advanced V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download


The same vbscript you can use for Wincc V13 Professional 

Read Data from Database - (WINCC Advanced V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download

The same vbscript you can use for Wincc V13 Professional 

WINCC VBScript - Write data into database

This script is useful in datalogging purpose. This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim objConnection
Dim strConnectionString
Dim FieldName
Dim Record
Dim strSQL
Dim objCommand
Dim strfile

strfile = "C:\Reports\Database.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

strSQL = "INSERT INTO TableName (" & FieldName & ") VALUES (" & Record & ");"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
    .ActiveConnection = objConnection
    .CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

WINCC VBScript - Read data from database

This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim objConnection
Dim objCommand
Dim objRecordset
Dim strConnectionString
Dim strSQL
Dim lngValue
Dim lngCount
Dim strfile

strfile = "C:\Reports\Database.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

strSQL = "select * from DQ_LOG where DATE_TIME >= CDATE('"& Start_Time &"') And  DATE_TIME <= CDATE('"& End_Time &"')"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objRecordset = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSQL
Set objRecordset = objCommand.Execute

Smarttags("Tagvalue1")  = objRecordset.Fields(0).Value
Smarttags("Tagvalue2")  = objRecordset.Fields(1).Value

Set objCommand = Nothing
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing

Steps:
1. Simply copy paste this script
2. Do minor changes as per your application.
Enough!! 

SQL Tutorial 2

** SQL is very important for SCADA Report generation**

INSERT INTO

The INSERT INTO statement will create new row and insert the value into the table.

Syntax:

INSERT INTO table1 (column1,column2,column3) VALUES (value1,value2,value3);

Example:

INSERT INTO Temperature_Log (Set_point1,Set_Point2,Set_Point3)
VALUES ('35.7', '37.9', '44.1');

UPDATE Statement

The INSERT INTO statement will replace the existing value with new value.

Syntax:

UPDATE table1 SET column1=value1,column2=value2 WHERE column3=value1;

Example:

UPDATE Temperature_Log SET Set_point1='35.7', Set_point2 ='37.9' WHERE Present_Value ='20';

SQL DELETE Statement
The DELETE statement is used to delete single row or multiple rows in a table. 

Syntax:
DELETE FROM table1 WHERE column1=value1;

Example:
To delete all rows in a table
  DELETE FROM table1;
To delete all rows in a table
  DELETE FROM Temperature_Log WHERE Channel_Name='Chamber_PID' AND Furnace_name='Hardening';

SQL LIKE Operator

The LIKE operator is used to fetch for a specified text in a column.

Syntax:

SELECT column1 FROM table1 WHERE column1 LIKE specific_text;

Example:
SELECT * FROM Temperature_Log WHERE Channel_Name LIKE 'PID%';

BETWEEN Operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or date. This is very important in SCADA projects because mostly we used to generate reports between two date and time.
Syntax:
SELECT column1 FROM table1 WHERE column1 BETWEEN value1 AND value2;

Example:
Using Number:
SELECT * FROM Temperature_Log WHERE Temperature BETWEEN 35 AND 66;
Using Date:
Select * FROM Temp_Log  where DATE_TIME >= CDATE('" & Start_Date1 & "')and DATE_TIME <=CDATE('" & End_Date1 & "') order by DATE_TIME;

INSERT INTO SELECT Statement
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. This will not delete any data in existing table, it will just append the data.
Syntax:
INSERT INTO table2 SELECT * FROM table1;

Example:
"INSERT INTO Temp_Report_Date_Trend (DATE_TIME ,HARD_TEMP1_SP) SELECT DATE_TIME ,HARD_TEMP1_SP from Temp_Log where Date_Time >= CDATE('" & Start_dates & "')and Date_Time <=CDATE('" & End_dates & "') order by Date_Time"

SQL Tutorial 1

** SQL is very important for SCADA Report generation**

SQL is a standard query language for accessing databases. It can be used in vbscripts while accessing databases.

For Example:

strfile = "D:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Datalog_Table"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!DateTime_Column

Text2.Text = rs!Data1_Column

rs.Close

cn.Close

from the above vbscript the green highlighted line is the SQL Query. There are different types of SQL queries for different types of database access. They are:

SELECT

Syntax:

SELECT column1,column2 FROM table1;

or

SELECT * FROM table1;

Example:

SELECT SupplierName,Country FROM Suppliers;

SELECT DISTINCT

Syntax:

SELECT DISTINCT column1,column2 FROM table1;

Example:

SELECT DISTINCT City FROM Suppliers;

WHERE Clause

Syntax:

SELECT column1,column2 FROM table1 WHERE column3 operator value;

Example:

SELECT * FROM Suppliers WHERE SupplierID=5;

AND & OR Operators

These operators are used in where clause.

The AND operator is used to display a record if both the condition1 AND the condition2 are true.

The OR operator is used to displays a record if either the condition1 OR condition2 is true.

Example:

SELECT * FROM Suppliers WHERE Country='India' AND City='Japan';

SELECT * FROM Suppliers WHERE Country='India' OR City='Japan';

ORDER BY

Syntax:

SELECT column1,column2 FROM table1 ORDER BY column3 ASC/DESC,column4 ASC/DESC;

ASC - Order(sort) by Ascending order

DESC - Order(sort) by Descending order

Example:

1. SELECT * FROM Suppliers ORDER BY City;

2. SELECT * FROM Suppliers ORDER BY City DESC;
3. SELECT * FROM Suppliers ORDER BY City ASC, Suppliername DESC;

VB Script for Write into database Table

Write data to MS ACCESS Database

Free code, example code, sample code

Step 1: Open new VB6 project.
Step 2: Select Standard EXE
Step 3:

Step 3: Select the following references

Step 4: Create MS ACCESS Database

Step 5: Add text-box and button

Step 5: Paste this VBScript

Private Sub Command1_Click()

Dim strfile As String

Dim Strcon As String

Dim sqlstr As String

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

strfile = "D:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Datalog_Table"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!DateTime_Column

Text2.Text = rs!Data1_Column

rs.Close

cn.Close

End Sub

Enjoy! Now you can able to write present value and setpoint to database

Vb6 Script for Database connection

Read data from MS ACCESS Database

Free code, example code, sample code

Step 1: Open new VB6 project.
Step 2: Select Standard EXE
Step 3:

Step 3: Select the following references

Step 4: Create MS ACCESS Database

Step 5: Add text-box and button

Step 5: Paste this VBScript

Private Sub Command1_Click()

Dim strfile As String

Dim Strcon As String

Dim sqlstr As String

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

strfile = "C:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Temperature_Data"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!Present_Value

Text2.Text = rs!Set_Point

rs.Close

cn.Close

End Sub

Enjoy! Now you can able to read present value and setpoint from database