WinCC OA data in Excel, why bother about macros?

In one of my last entries I have showed you how to get data from WinCC OA into Excel. With a bit of additional work you can easily call whatever functions you like (like changing values) with the way it was done in that example.

What if you do not want to use macros or a security policy prevents you from doing so. There might be a solution for you. Excel 2016 (I know it does not work in 2007) is able to process data in JSON format, what you will get looks as followed:

Just follow a few simple steps to get data like this.
  • Download the following xlsReport_server.ctl and run it with a CTRL manager (it is the same than in the last example).
  • Make sure have a valid certificate for your webserver, otherwise Excel prevents the connection. If this is not possible, you need to use http.
This is the error you will see in IE if there is an issue with your certificate
  • In Excel choose to get data via "New Query" and navigate to "From Web"
  • Enter the URL to get the data. If you use the same CTRL manager than in the example with the macros you can paste a URL like in the following example:
  • Enter the credentials for your server:
  • You will see an error message, which is a bit confusing, as there is no problem with the credentials, Excel just wants to know the correct format of the response. Press "Edit":
  • Now choose "Json" as format and press "OK":
  • Excel will now show the result from the query. In this simple example, just press "into table":
  • There are no further settings necessary, press "Close & Load":
  • Now the data is shown in Excel

Do not worry, these steps are necessary only once per query. This query is saved in the Workbook. So you can simply refresh the value as you like, you just need to press the "Refresh" button you can see in the screenshot above. Note, there is also a "Refresh All" option.

Enjoy the possibilities of WinCC OA and have fun.

Best regards,

No comments:

Post a Comment