EXCEL example

This chapter shows some examples on the COM Manager and on the methods described earlier.

The examples were executed with a macro with the program Excel (the programming language was VBA) in order to illustrate the access to data in WinCC OA via the COM interface. In order to follow the examples execute the following steps:

  1. Start the WinCC OA with the project WinCC OA DemoApplication (you can of course also use another project for the test. The data points defined in the project have to be used in code.)

  2. Start MS Excel.

  3. Open a new sheet (a new sheet is created automatically when you open the program - you can see sheet1 in the title bar)

  4. Open the Microsoft Visual Basic editor in Excel via Extras > Macro > Visual Basic editor. A new window with the span Visual Basic application is opened.

  5. Refer to WCCOACom 1.0 Type Library in the project. Select the menu item Extras > References... and activate the library in the pop-up window that is displayed.

  6. Example

    Create a new module for the sheet1 in the VB project. Rename the module to ComMan and add the following code to the module:

    'Test environment for the COM manager
     
    Option Explicit
     Const CMDLINE = "-num 2 -proj DemoApplication_3.13"
    'Beachten sie, dass Sie den richtigen Projektpfad angeben.
    'Hier (DemoApplication_3.13)
     
    Private moComMan As ComManager
     
    'Controls the test progress
    Public Sub test()
    Dim iAnswer As Integer
    Dim ws As Worksheet
     On Error GoTo test_error
     iAnswer = MsgBox("Start test?", vbQuestion + vbYesNo, "COM Test")
     If iAnswer <> vbYes Then Exit Sub
     
     Set ws = ThisWorkbook.Worksheets(1)
     ws.Range("A:K").Value = ""
     Call Init(ws)
     Call dpGet(ws)
     Call dpSet
     Call dpGetAsynch(ws)
     Call dpQuery(ws)
     Call dpTypes(ws)
     Call dpNames(ws)
     Call dpElementType(ws)
     ws.Range("A1:K1").EntireColumn.AutoFit
     
    Exit Sub
    test_error:
     Call MsgBox(Err.Description, vbCritical + vbOKOnly, Err.Number)
    End Sub
     
    ' Initializes (starts) the COM manager - example init()
    Private Sub Init(ws As Worksheet)
    Dim sConfigFile As String
     sConfigFile = Environ("PVSS_II")
     
     If moComMan Is Nothing Then
     Set moComMan = New ComManager
     Call moComMan.Init(CMDLINE)
     End If
    End Sub
     
    'Example dpGet()
    Private Sub dpGet(ws As Worksheet)
    Dim asDpName(2) As String
    Dim avValue As Variant
    Dim rg As Range
    Dim n As Long
     asDpName(0) = "ExampleDP_Arg1.:_online.._value"
     asDpName(1) = "ExampleDP_Arg2.:_online.._value"
     asDpName(2) = "ExampleDP_Result.:_online.._value"
     
     Call moComMan.dpGet(asDpName, avValue)
     Set rg = ws.Range("A1")
     rg.Value = "Beispiel dpGet()"
     
     For n = 0 To UBound(avValue)
     rg.Offset(n + 1, 0).Value = asDpName(n)
     rg.Offset(n + 1, 1).Value = avValue(n)
     Next n
    End Sub
     
    'Example dpSet()
    Private Sub dpSet()
    Dim sDpName As String
    Dim vValue As Variant
     sDpName = "ExampleDP_AlertHdl1.:_original.._value"
     vValue = 1
     Call moComMan.dpSet(sDpName, vValue)
     sDpName = "ExampleDP_AlertHdl2.:_original.._value"
     vValue = 1
     Call moComMan.dpSet(sDpName, vValue)
    End Sub
     
    'Example dpGetAsynch()
    Private Sub dpGetAsynch(ws As Worksheet)
    Dim dtVon As Date
    Dim asDpName() As String
    Dim avValue As Variant
    Dim avTime As Variant
    Dim avType As Variant
    Dim rg As Range
    Dim n As Long
    Dim m As Long
     ReDim asDpName(0)
     asDpName(0) = "ApplicationProperties.demoDataStart:_online.._value"
     Call moComMan.dpGet(asDpName, avValue)
     dtVon = DateSerial(Year(avValue(0)), Month(avValue(0)), Day(avValue(0)) + 2)
     
     ReDim asDpName(1)
     asDpName(0) = "Reservoir_1_level.C3.AVG_WT0:_offline.._value"
     asDpName(1) = "Reservoir_2_level.C3.AVG_WT0:_offline.._value"
     Call moComMan.dpGetAsynch(dtVon, asDpName, avValue)
     
     Set rg = ws.Range("A6")
     rg.Value = "Beispiel dpGetAsynch()"
     
     For n = 0 To UBound(avValue)
     rg.Offset(n + 1, 0).Value = Left(asDpName(n), 17)
     rg.Offset(n + 1, 1).Value = avValue(n)
     Next n
    End Sub
     
    'Example dpQuery()
    Private Sub dpQuery(ws As Worksheet)
    Dim sSQL As String
    Dim avValue As Variant
    Dim rg As Range
    Dim n As Long
    Dim m As Long
     sSQL = "SELECT ALERT '_alert_hdl.._value','_alert_hdl.._text' FROM 'Reservoir_*.value'"
     Call moComMan.dpQuery(sSQL, avValue)
     Set rg = ws.Range("G1")
     rg.Offset(0, 1).Value = "Beispiel dpQuery()"
     For n = 0 To UBound(avValue)
     For m = 0 To UBound(avValue(n))
     If n > 0 And m = 2 Then
     rg.Offset(n + 1, m + 1).Value = CDbl(avValue(n)(m))
     Else
     rg.Offset(n + 1, m + 1).Value = avValue(n)(m)
     End If
     Next m
     Next n
     
    End Sub
     
    'Example dpTypes()
    Private Sub dpTypes(ws As Worksheet)
    Dim sPattern As String
    Dim asDpType() As String
    Dim rg As Range
    Dim n As Long
     sPattern = "*"
     Call moComMan.dpTypes(sPattern, asDpType)
     
     Set rg = ws.Range("A14")
     rg.Value = "Beispiel dpTypes()"
     For n = 1 To UBound(asDpType)
     If Left(asDpType(n), 1) <> "_" Then
     Set rg = rg.Offset(1, 0)
     rg.Value = asDpType(n)
     End If
     Next n
    End Sub
     
    'Example dpNames()
    Private Sub dpNames(ws As Worksheet)
    Dim sDpPattern As String
    Dim sDpType As String
    Dim asDpName() As String
    Dim n As Long
    Dim rg As Range
     sDpPattern = "*"
     sDpType = "ANALOG1"
     Call moComMan.dpNames(sDpPattern, sDpType, asDpName)
     Set rg = ws.Range("B14")
     rg.Value = "Beispiel dpNames()"
     For n = 1 To UBound(asDpName)
     Set rg = rg.Offset(1, 0)
     rg.Value = asDpName(n)
     Next n
    End Sub
     
    'Example dpElementType()
    Private Sub dpElementType(ws As Worksheet)
    Dim sDpPattern As String
    Dim sDpType As String
    Dim asDpName() As String
    Dim n As Long
    Dim rg As Range
     sDpPattern = "Reservoir_1_level.**"
     sDpType = "ANALOG1"
     Call moComMan.dpNames(sDpPattern, sDpType, asDpName)
     Set rg = ws.Range("C14")
     rg.Value = "Beispiel dpElementType()"
     For n = 1 To UBound(asDpName)
     If moComMan.dpElementType(asDpName(n)) = DPE_FLOAT Then
     Set rg = rg.Offset(1, 0)
     rg.Value = asDpName(n)
     End If
     Next n
    End Sub
  7. So that the macro containing the examples on the COM manager is executed after opening the document add the following lines to the worksheet (Excel object "ThisWorksheet") in the worksheet part.

    Private Sub Workbook_Open()
     Call ComMan.test 'ComMan ist der Name des Moduls; teste die Testfunktion
    End Sub
  8. Save and close the edited worksheet and open it again. Choose activate macros when you start the file. The macro is executed and fills over the COM interface the worksheet with the values from the WinCC OA project.