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:
-
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.)
-
Start MS Excel.
-
Open a new sheet (a new sheet is created automatically when you open the program - you can see sheet1 in the title bar)
-
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.
-
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.
-
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
-
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
-
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.