Excel Web Query Toolkit - Build a Sample Report

For now let's try to build a very simple report on the "Report" page by linking a few data points from PLC1 and PLC2 sheets.

First, click on the "Report" tab and view the page as follow:

Next, click on the cell B4, then enter the "=" sign. You can now click on the "PLC1" tab and select cell "G7" on the PLC1 page and then press "Enter". as follow:

As you can probably guess, the cell G7 of "PLC1" sheet contains data that is to be captured from the PLC's "X" variable and the previous value was "1000". Since cell B4 of "Report" sheet is now linked to this cell, the same value "1000" appears on cell B4 on the report page. You can now do the same by linking cell B5 to cell G8 of "PLC2" sheet. This cell is configured to capture the variable "K" from PLC2.

Next, lets try to link a cell from "Report" page to a single output bit in PLC1.  Click on cell C4 on "Report" sheet. Enter "=" and then click on "PLC1" sheet and select cell "I17". This cell contains the On/OFF bit status of bit #1 of the OUTPUT[1] variable in PLC1.  This correspond to Digital Output #2 on PLC1. Its previous value was "0" so the same value will appear on cell "C4" of the "Report" sheet, as follow:

Let's now use the Excel's "Conditonal Formatting" feature to highlight this value.  Click on the Excel's "Format" menu and select "Conditional Formatting" and then enter the condition where value = "0" to change background to grey and when value = "1" to change background to yellow, as follow:

Next, link cell C5 on the "Report" sheet to cell J17 on PLC2. Since the previous reading of this bit was "1", the value is echoed at cell C5 on "Report" sheet and background would be highlighted in bright yellow, as shown below:

That's it! You have just created a report that link to data retrieve from different internal variables of different PLC. 

You can now test the program by clicking on the button. This button is linked to a macro that will call all the "refreshExternalData()" routine in each PLC sheet and therefore all the PLC data will be updated. It may take up to 15 seconds for all data retrieval to be completed, longer if there is any PLC not responding to the web query command (most likely it is not accessible via the network).

Click Here To View the PLC Sheet Configuration

*Microsoft (MS) Excel is a registered trademark of the Microsoft Corporation.