Example: how to query data using MS Excel

This example shows how you to query data by using Microsoft Excel. The following configuration option is used:

Consumer, provider (EXE and DLL), DM and archives on one common computer

All use the WinCC OA project config file and are installed with the WinCC OA installation. Communication between the provider and the consumer (Provider-DLL) is realized through COM.

Proceed as follows:

  1. Add theWCCOAoledb managerentry to the progs file underWinCC_OA_Project_Path/config

    windows/WCCOAoledb | manual | 30 | 2 | 2 |

    Note: The WCCOAoledb manager entry has to end with the|sign. Take care that there is no "blank" at the end of the line!
  2. Register the following components as an Administrator user by using the Command Prompt. Before registering the components, switch to the WinCC_OA_Installation_Path/bin/windows directory. Note that you register the components in the given order:
    • WCCOAoledb.exe /regserver
    • regsvr32WCCOAOleDbExeps.dll
    • regsvr32WCCOAoledb.dll

    Note that the name of the provider is WCCOAoledb OLE DB Provider and that the provider is 32 bit.Therefore, you have to add the manager to the progs file of your project under Windows 64 bit.

  3. Start your WinCC OA project and theWCCOAoledbmanager in the console:
    Figure 1. WinCC OA Console and the WCCOAoledb Manager
  4. OpenMicrosoft Exceland the menu "Data". From theDatamenu, select the option "From other sources".
    Figure 2. Microsoft Excel: Data menu -> Option "From other Sources"
  5. In the data connection wizard, select the option "Other/Advanced". Click onNext >
    Figure 3. Microsoft Excel: Data Connection Wizard-> Option "Other/Advanced"
  6. Select the option "WCCOA oledb OLE DB Provider" and click onNext>
    Figure 4. Microsoft Excel: Data Connection Wizard-> Option "'Other/Advanced"-> "Provider"
  7. On theConnectiontab click onOK. You don't have to enter the data source.
    Figure 5. Microsoft Excel: Data Connection Wizard-> Data Link Properties -> Connection tab
  8. Select either values (HISTVAL) or alerts (ALERTS) table.
    Figure 6. Select the database table
  9. Select how and where the data should be shown and click on OK.
    Figure 7. Microsoft Excel, Import data Option

The data is shown in Excel.

Figure 8. Query Values in Microsoft Excel

SampleHistDB.xls Example

You can find another example under WinCC_OA_path/source/. The SampleHistDB.xls file contains three buttons. You can query the current and historical values of the data point type "ExampleDP*" as well as execute a user-defined query by adding an SQL statement to the cell A1 (see figure below):

Figure 9. SampleHistDB.xls example