|
| Since all TRiLOGI PLCs that have built-in Ethernet (such as
the Nano-10 or F-series PLCs) support direct *Excel Web query, you can write an Excel
spreadsheet macro program that sends HTTP web queries to your PLCs to retrieve any
internal data over a LAN or the Internet. However, many users may not have the luxury
of time to learn Excel Macro programming language to a proficient level to handle this
with ease. The Excel Web Query Toolkit tackles
this problem by enabling any novice Excel user to rapidly create a report based on data
retrieved from up to 8 PLCs connected to the network, without writing a single line of
macro program code!
Excel WebQuery Toolkit is developed on MS Excel 2000 and should work with newer version
of MS Excel program. |
 |
The Excel Web Query Toolkit Excel file comprises 1 Report sheet and 8 individual,
"PLC1" to "PLC8" sheets, as can be seen from the tabs along the bottom
row of the spreadsheet window:
- The "PLCx" sheets are where all the
heavy liftings are already done for you. An Excel macro program has already been written
to enable the user to configure which I/O bit data, 16-bit word, 32-bit word or
String data (including string displayed on the LCD display) to retrieve simply by clicking
the corresponding "define" button. Click
here to view PLC Sheet Configuration
- The "Report" sheet is where you
create your own Excel report by linking directly to the cells in each individual PLC sheet
that contain data it retrieved. In Excel program it is extremely easy to link a cell
value to another cell value in another sheet simply by entering the "=" sign and
then pick the target cell from another worksheet. Click here to view Creation of a Sample Report. A macro has been
written that is linked to a "Refresh All Data" button which allows a
single-click method to retrieve the desired data from all the active PLCs configured in
the "PLCx" sheets.
| 1. Methodology |
Excellink program is standalone software that
act as a middleman between the PLCs and the Excel spreadsheet. ExcelLink retrieve data
from the PLCs all over the places and then use the Excel DDE method to store data into the
Excel spreadsheet cells. As such Excellink must be run on the same PC as the MS Excel
program. Excel Web Query Toolkit does not use another program. It uses the Excel
built-in Webquery capability to send HTTP (just like requesting a webpage) command to the
PLC to retrieve some data. Since the PLC has the ability to respond to HTTP form of
hostlink command it will return the response in the form of a webpage to the Excel
program. The data received are in raw hostlink response format and the excel macro program
have to extract the data and convert to the numeric or string form. |
| 2. Performance |
ExcelLink maintains a persistent TCP/IP
connection with TRiLOGI PLCs either directly (with Ethernet interface) or via the
TLServer. As such Excellink is able to retrieve data rapidly and store them into the
spreadsheet cells for data logging purpose. Excel Web Query, on the other hand, treat
the PLCs as if they are web servers serving up webpages. Excel program first open a socket
and connect to the FServer on the PLC . It then send a HTTP command packet and wait to
receive a HTTP coded response from the PLC's server. Once the transaction is completed the
socket is closed for use by other queries. Excel web query therefore works much slower
than ExcelLink. |
| 3. Data Logging |
ExcelLink is suitable for application that needs
to log data into Excel spreadsheet continuously. Excel Web Query toolkit is suitable
for retrieving a snap shot of the internal variables from the connected PLCs on user
command. However, it is NOT a suitable tool for unattended data logging
purpose. This is because of the single-threaded nature of Excel spreadsheet program, such
that whenever there is any communciation error (e.g. no response due to lost TCP/IP
packets), MS Excel will open a dialog box to prompt the user and simply pause everything
to wait for the user to click on the OK button. Unfortunately all communication activities
are also stopped until the user has clicked the OK button. For this reason the Excel
Query toolkit is not recommended for unattended data logging purpose. |
| 4. Data Write & TLServer/XServer support. |
ExcelLink can write data to the PLC and can work
with M-series PLC via TLServer Excel Web Query Toolkit is a READ ONLY tool. It does not
support writing data to the PLCs. Excel Web Query DOES NOT work TRiLOGI PLC that have no
built-in Ethernet but rely on TLServer or XServer for network connectivity (e.g. T100MD+
or T100MX+ PLCs). |
*Microsoft (MS) Excel is a registered trademark of the
Microsoft Corporation. |