WinCC TIA VB Script data logging to excel (CSV)

Posted 18 Apr 2020 by Nikolay with 5 Comments
Image credit: SIEMENS.
Image credit: SIEMENS.

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.

PC Station project 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.

Internal variables in TIA WinCC
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.

Input fields and log button
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.
Adding new VB script.
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.
The script is ready and now to trigger it I will add event to the button that I created before.

FiWinCC TIA button Click event
Figure 5. WinCC TIA button Click event.

To test the script we start the runtime, enter values and press the logging button.
Variables to log
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.

 Log file.

Figure 7. Log file.


Variable logged in excel file

Figure 8. Variable logged in excel file.

 

 

 

5 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

  • Right-click on the particular Excel (xls/xlsx) document and select 'Properties'.
  • Uncheck the 'Read-only' attribute and then click the 'OK' button.

... 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)