1. Overview

This paper mainly introduces how to operate WINCC online data table control through VBS.

• Development environment: PCS7 V8.2 SP1 / WINCC 7.4 SP1
• Use restrictions:

1) The time interval is up to 1 minute

2) The time range is too long, and the data loading may be slow

3) The export file excel is not set to the top. After export, switch to the EXCEL program.

• Extensibility: it can extend the functions of selecting parameters, saving export files, generating PDF, etc.

In this example, the parameters are configured in the control in advance, and the selection is realized through the check box during operation. Parameter addition can also be implemented entirely using scripts.

• See additional information > adding parameters through script for details

2. Operation effect

Figure 1 query data according to selection

Figure 2 successfully exported data

Figure 3 viewing the exported data file in EXCEL

Figure 4 viewing the exported data file in EXCEL (select some columns)

3. Project configuration

3.1

Picture component description

Fig. 5 description of test screen components

3.1.1 add time control

Add WINCC Activex control Date and time picker, as shown below:

Figure 6 adding time control

3.1.2 setting export template

The excel file template path set in this example is: "\ \" + ServerName +"\Export\Export.xlsx". Other export paths and templates can be adopted according to the project situation. If you change the template or path, you must modify the path within the export script.

Font, data format, displayed decimal places and column width are mainly set in the template. Header, footer and LOGO can also be added to achieve better display effect.

3.2

Screen open

In the screen opening event, set the following script to initialize the controls in the screen:

Sub OnOpen() 
 Dim ioTimeFactor,tbl1,dtpStart,dtpEnd
 Set tbl1=ScreenItems("tbl1")
 Set ioTimeFactor = ScreenItems("ioTimeFactor")
 Set dtpStart = ScreenItems("dtpStart")
 Set dtpEnd = ScreenItems("dtpEnd")
 'The actually set coefficient is displayed on the set value
 ioTimeFactor. OutputValue = tbl1.TimeStepFactor
 'Set time factor setting IO Field type is input
 ioTimeFactor.BoxType =1 
 'Set table to start-End time range
 tbl1.TimeColumnRangeType=1
 'Format time control display
 dtpStart.Format=3
 dtpStart.CustomFormat= "yyyy/MM/dd HH:mm:ss"
 dtpEnd.Format=3
 dtpEnd.CustomFormat= "yyyy/MM/dd HH:mm:ss"
 'Set the default time range to the current day
 dtpStart.Value=FormatDateTime(Year(Now) & "/" &  Month(Now) & "/" & Day (Now),1)'0:00 of the day
 dtpEnd.Value=Now'current time 
End Sub

Figure 7 screen opening event script

3.3

Select numerical parameter version design

Figure 8 add parameter selection check box

Set the check box to add a script under the check box change event:

Sub Process_OnPropertyChanged(Byval Item, Byval value) 
  Dim i,j,tbl1
  Set tbl1 = ScreenItems("tbl1")
  For i = 0 To item.BoxCount-1
  tbl1.ValueColumnIndex= i
  'Judge whether the filter box is selected. If yes, set the curve to be visible
  If (value And 2^i) > 0 Then
    tbl1.ValueColumnVisible = 1
  Else
    tbl1.ValueColumnVisible = 0
  End If
 Next 
End Sub

Figure 9 data parameter display and hiding setting script

Figure 10 correspondence between script and control properties

3.4

Set time factor

Properties: TimeStepFactor

Figure 11 modify interval script

Sub InputValue_OnPropertyChanged(ByVal Item, ByVal value) 
 Dim tbl1
 Set tbl1 = screenitems("tbl1")
 tbl1.TimeStepFactor=value
End Sub

Figure 12 modification time factor

3.5

Set time range

Figure 13 adding time control

In the left key release event of query button:

Sub OnLButtonUp(Byval Item, Byval Flags, Byval x, Byval y) 
  Dim tbl1,dtpStart,dtpEnd
  Set tbl1 = ScreenItems("tbl1")
  Set dtpStart = ScreenItems("dtpStart")
  Set dtpEnd = ScreenItems("dtpEnd")
  'Set the time range of the table
  tbl1.TimeColumnIndex=0
  If dtpStart.Value < dtpEnd.Value Then
     tbl1.TimeColumnBeginTime=dtpStart.Value
     tbl1.TimeColumnEndTime =dtpEnd.Value
  Else
     Msgbox "The start time is less than the end time, please set it correctly!",vbOKonly+vbExclamation
  End If
End Sub

Figure 14 time range query button events

Figure 15 modification start time range

3.6

export

In the Export button release left button event:

Sub OnLButtonUp(Byval Item, Byval Flags, Byval x, Byval y) 
   Dim objExcelApp,objExcelSheet,sheetname,ServerName,DataFirstRow,TagValue
   TagValue=1
   DataFirstRow=1
   sheetname="sheet1"
   ServerName = HMIRuntime.Tags("@ServerName").Read
   
'Get data in the table
   Dim tbl1,col,RowCount,row
   Dim Value()
   Dim i,j,k
   Set tbl1 = ScreenItems("tbl1")
   Set row = tbl1.GetRowCollection'that 's ok
   RowCount=row.count
   Redim Value(RowCount+1,tbl1.ValueColumnCount+2)'Redefine the numeric array to store time and numeric values. Line: Note
 Record, column: parameter
   Set col = tbl1.GetValueColumnCollection'Numeric column
  'Serial number
   Value(0,0)="Serial number"
   For j = 1 To RowCount
      Value(j,0)=j
   Next
  'Time column name
   tbl1.TimeColumnIndex=0
   Value(0,1)=tbl1.TimeColumnName
   
   k=1
   For i = 1 To tbl1.ValueColumnCount + 1
     'Number of writes when the column is visible,Or jump out
     tbl1.ValueColumnIndex= i-2
     If tbl1.ValueColumnVisible Or (i = 1) Then 'Time column, or value column display
      'Numeric column name
      If i > 1 Then
         tbl1.ValueColumnIndex= i-2
         Value(0,k)=tbl1.ValueColumnName
      End If
      
      For j = 1 To RowCount
         Value(j,k)=tbl1.GetRow(j).celltext(i)'be careful: celltext()Point to the data column (regardless of whether it is hidden),
When celltext()When pointing to a hidden column, the data is empty. cell
      Next
      k=k+1
   End If
 Next
 
'open Excel Template
   Set objExcelApp = CreateObject("Excel.Application")
     objExcelApp.Visible = True
   Set objExcelSheet = objExcelApp.Workbooks.Open("\\"+ServerName +"\Export\Export.xlsx") 
'Template file storage location
     objExcelSheet.Activate
     
'Write data to EXCEL
  With objExcelApp.Worksheets(sheetname)
   .Range(.Cells(1,1),.Cells(RowCount+1,tbl1.ValueColumnCount+2))= Value
   .Cells(RowCount+2,1)="Exporter:"
   .Cells(RowCount+2,2)=HMIRuntime.Tags("@CurrentUserName").Read
   .Cells(RowCount+3,1)="Export location:"
   .Cells(RowCount+3,2)=HMIRuntime.Tags("@LocalMachineName").Read
   .Cells(RowCount+4,1)="Export time:"
   .Cells(RowCount+4,2)=Now
   .Cells(RowCount+5,1)="database:"
   .Cells(RowCount+5,2)=HMIRuntime.Tags("@DatasourceNameRT").Read
   .Cells(RowCount+6,1)="Software version:"
   .Cells(RowCount+6,2)=HMIRuntime.Tags("@ServerVersion").Read
  End With
  
  'Msgbox "Export succeeded, please click EXCEL View data in",vbOKonly+vbInformation
  
'Generate a new file,close Excel
   Dim path1,path2,fso,filename
   Set fso=CreateObject("Scripting.FileSystemObject")
   filename= CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now))& CStr(Hour(Now))& 
CStr(Minute(Now))& CStr(Second(Now))
   path1= "C:\Users\wq_07\Documents\Export\"&filename&".xlsx"
   If fso.FileExists(path1) Then 'Delete duplicate files
       fso.DeleteFile(path1)
       End If
   'objExcelSheet.ExportAsFixedFormat 0,path1 '0-xlTypePDF ,1-xlTypeXPS，Save discovery directly
PDF The software cannot open the file, so export is used instead PDF
   'CreateReport_RH=path1
   objExcelApp.Worksheets(sheetname).SaveAs path1
  objExcelSheet.close False'close EXCEL，Do not save
  objExcelApp.Quit
  'Open the folder where the exported file is located
  Dim objShell,strFolder
  strFolder=fso.GetParentFolderName(path1)
  Set objShell=CreateObject("Wscript.Shell")
  objshell.Run strFolder
End Sub

Figure 16 export button script

Figure 17 automatically open folder after export

4. Additional information

4.1

Add parameters through script

Sub OnLButtonUp(ByVal Item, ByVal Flags, ByVal x, ByVal y) 
  Dim tbl1,col
  Set tbl1 = ScreenItems("tbl1")
  'New parameter
  Set col = tbl1.GetValueColumnCollection.AddItem("R1-Script")
  'Parameter column name
  col.Caption = "R1-Script"
  'Archive variable
  col.Provider = 1
  'Archive variable name
  col.TagName = "TBL\R1"
  'time axis
  col.TimeColumn = "time"
End Sub

Figure 18 new parameter script

Figure 19 operation effect of new parameters

Wang Qi