Script for logging machine data (variables) from WinCC TIA Runtime (SCADA) to Excel file (CSV).
This is a simple script which shows how we can log data coming from our machine or some important information that is visualized on the SCADA in a data file, like Excel table or some or simple text file (*.txt).
In the first step we need to generate PC-Station (runtime application) in TIA.
Figure 1. PC Station project in TIA.
Then we create the variables which are going to be logged into the file. For this example, I have generated internal variables (they are not connected to PLC). I have generated variables of type real, but you can do it with integer, bool, etc.
Figure 2. Internal variables in TIA WinCC.
Because the variables are not coming from any PLC, I am going to need input fields from which to change their values. For this I generated three input/output fields and one button to trigger the logging script.
Figure 3. Input fields and log button.
The most essential part is of course is the script. Under scripts we generate a test script which in my case I called tmpExport_1.
Figure 4. Adding new VB script.
Sub tmpExport_1()
Dim FolderPath, ObjectPath, Filename, File, FileExist, Columns, Row
FolderPath = "C:\_DOC\"
Filename = "test_log.csv"
Set ObjectPath = CreateObject("Scripting.FilesystemObject")
'Check if folder exists
If Not ObjectPath.FolderExists(FolderPath) Then
ObjectPath.CreateFolder FolderPath
End If
' Check if file exists
Set File = CreateObject("Scripting.FilesystemObject")
FileExist = File.FileExists(FolderPath & "\" & Filename)
If FileExist = False Then
File.CreateTextFile(FolderPath & "\" & Filename)
Set Columns = File.OpenTextFile(FolderPath & "\" & Filename, 8)
Columns.WriteLine("Data1 ; Data2 ; Data3")
Columns.Close
Set File = Nothing
End If
'Write data to file
Set File = CreateObject("Scripting.FilesystemObject")
Set Row = File.OpenTextFile(FolderPath & "\" & Filename, 8)
Row.WriteLine(SmartTags("zLogReal1") & ";" & SmartTags("zLogReal2") & ";" & SmartTags("zLogReal3") & ";")
Row.Close
Set File = Nothing ‘Here we are releasing the file.
End Sub
Script elements explained:
- Dim - here we are declaring the variable which are used in the script.
- Variable FolderPath – define the folder on you hard disk where the lo file is going to be saved.
- Filename – the name + extension of the file
- ObjectPath – creates object for the folder and after that we are checking if the Path exists, if not it will be created.
- FileExist – check if file exits, if not we create it and define the structure of the columns.
- In the next steps we set the file for writing.
Set Row = File.OpenTextFile(FolderPath & "\" & Filename, 8)
The 8 at the end tells the script to write at the end of the file, so we don’t overwrite the existing information.
- WriteLine(SmartTags("zLogReal1") & ";" & SmartTags("zLogReal2") & ";" & SmartTags("zLogReal3") & ";")
Whit this command we start to write the variable values in the file. - Row.Close ans Set File = Nothing are used to close the file and release it so it can be opened from other application after we finish the data logging in it.
Figure 5. WinCC TIA button Click event.
To test the script we start the runtime, enter values and press the logging button.
Figure 6. Variables to log.
When we press the button the script is going to be executed and the log file is created in the predefined folder. All the values that were in the input/output fields are written to the file. To log new values we have to press the button and execute the script again. Zou can connect the execution of the script to a variable event. For example, if variable changes from 0 to 1.
Figure 7. Log file.
Figure 8. Variable logged in excel file.
9 COMMENTS //
Join the discussion
Kundan Lade 17 Oct 2021
Dear Sir/ Madam,
I try this script in TIA portal with comfort HMI,
With successfully generate excel file into excel but I have one
problem in excel sheet -data read only in one column not move into
another column.
Nikolay Bozov 01 Nov 2021
Dear Kundan Lade ,
As far as I can understand, you have problems copying the data from the excel CSV file.
Yousually there should not be a problem with the exportet data and copying it from the CSV file.
I suggest you try some of these solutions:
1. Remove Read-only Attribute from Excel File Properties
... if this does not help try:
2. Disable Protected-View in Excel File
File > Options > Trust Center > Trust Center Settings…> Protected View.
3. Location
Is the excel file located on network drive or on your local computer?
If on network drive, move it to your local hard disk
I hope this works for you.
Regard,
Nikolay
Dhaval Vora 22 Jan 2022
Thank you for your valuable information.
I ama facing problem is that every when I click on HMI/SCADA for data
log then only it will comes in Excel. But I what Automatically data
will comes in Excel at interval of 1min. Every 1min data will
available in excel sheet.
Please support us for the same.
Thank you, regards
Dhaval Vora
Nikolay Bozov 29 Jan 2021
Hello Dhaval Vora,
To be able to log data automatically, instead of calling the script over button you can call it over event (1) or when varible changes its value (2).
1) Schedule tasks -> Events -> VB Functions (here you select your VB Script).
As Event Trigger: Daily, Weekly, Once a minute etc.
2) In the PLC define a variaable that is going to change each second. For example Bool variable that changes fron 0 to 1 and from 1 to 0.
That conncet this variable to your visualisation projet and open the HMI Tags.
Select the variable and in Properties -> Events -> VB Functions - select your script
That's it!
Regards
Nikolay
Nasmie 18 Feb 2021
Thanks for this. It really works well. Would you perhaps know how to
read the created CSV file back onto the HMI for displaying the history
logged?
Title: WinCC TIA VB Script data logging to excel (CSV)
Susmit Prajapati 28 Oct 2022
hello,
I would like to record bool,integer and real data to csv file. Per
hour,per shift and per day data i would like to do print as well as
save.
Nikolay Bozov 09 Nov 2022
Hello Susmit,
to be able to record data per hour,per shift or per day all you need to do is set triggers. See up in the comments. I already answered a similar question.
As for the printing I will recomment setting Reports in TIA.
Regard
Nikolay
Karan 23 Dec 2022
How to learn scripting in TIA i know the basic but want to learn step
by step any guidelines??
Thanks in advance
Karan
Nikolay Bozov 30 Dec 2022
Hello Karan,
I would suggest first getting familiar with the basics of VB. Just to see how it works. There are lots of manual, tutorial and videos in Internet.
As for TIA, check this link:
Which VBS information and VBS programming tools are there in WinCC (TIA Portal)?
Regards
Nikolay