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 is the module name; test the testfunction 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.