Excel and WinCC OA, still a love story?

Even though there are lots of great data processing tools out there, still Excel is very frequently used for lots of everyday tasks.

When you want to query data from WinCC OA via Excel you often will use COM manager or OleDB. This has major disadvantages, on one hand you need to have WinCC OA installed on your office machine where you probably just want to get some special values for some calculation. On the other hand these old techniques are not that firewall friendly than http(s).

Don't worry, there are other ways.

You can "simply" use functions like dpGet from within Excel macros to get data via a simple webservice, in this article I will show you how to do it.

Download the following xlsReport_server.ctl and run it with a CTRL manager.

It will start an http and https server on the ports 2425 and 2426 and provide some basic functionality like dpGet, dpCreate, dpGetPeriod and getVersionInfo.

The functions are not that complicated, here is the smallest one pasted from the CTL above as an example:

dyn_string xlsReport_dpGet(dyn_string names, dyn_string values, string user, string ip,
                           dyn_string headerNames, dyn_string headerValues)
{
  anytype val;
  DebugTN(names, values, headerNames, headerValues);
  int i=0;
  if ((i=dynContains(names, "dpe")) > 0)
  {
    dpGet(values[i], val);
    mapping result = makeMapping(values[i], val);
    return makeDynString(json_valToStr(result), "Status: 200 OK");
    
  }
  return makeDynString("", "Status: 400 Bad Request");
}

So if you need other functions, just implement them using my ones as template.

In Excel (at least from Excel 2010 onwards) you need to handle lots of the processing yourself. This includes:

• connection
• authentication
• URL and Base64 encoding
• JSON parsing
• Date-conversion (UTC-local)

Luckily I did all that already, just download httpJson.xlsm . Once you open it you need to enable macros and you should be read to go. Just press the buttons and see what happens.

The connection configuration is within the code, just adapt it to your needs:

Public Sub initHttpVars()
    strUrl = "https://localhost:2426/xlsReport/"
    user = "para"
    password = ""
    auth = "Basic " + Base64EncodeString(user + ":" + password)
End Sub

The functions defined on the server-side are already usable on client-side within Excel macros, just like in this example:

Public Sub dpGetTest()
    Dim fVal As Double
    If dpGet("ExampleDP_Arg1.", fVal) Then
        MsgBox "ExampleDP_Arg1. , " & fVal
    End If
End Sub

I will spare you any more details, if you are interested in the implementation, just look at the "winccoa" module in Excel macros (ALT-F11). With this examples you should be able to handle any additional functionality yourself.

Enjoy the possibilities of WinCC OA and have fun.

Best regards,

Andreas