Getting Data into Microsoft Excel from PLC Automatically

R

Thread Starter

RE

I need help getting data into Microsoft Excel from an Allen-Bradley PLC automatically each time an "event" happens. For example, each time an alarm for a steam valve turns on, I need to record date, time, and duration of the problem. I can write the PLC code to gather the information. However, I am not sure how do get the information from the PLC to Microsoft Excel.

Any help is greatly appreciated!

Thank you!
 
Look at the SMRX ActiveX control at http://www.sapia-inc.com . It contains a detailed Excel example. You will need to add a timer control or by some other means periodically poll your Modbus device for the event. When the event occurs, further read the appropriate data registers using SMRX and do whatever with the data. Hope this helps.

This assumes that your PLC can speak Modbus/RTU. If not, please disregard.
 
R

Raymond van der Tas

A little work to be done to get PLC data "automatically" in Excel.

1)Expose the PLC Data
You would first install a driver on your computer that is capable to expose the PLC data in a standard way. This would be an OPC Server that talks your preferred communication method (serial, ethernet,DH, profibus,...) You can purchase OPC servers from AB (RSLinx), Kepware, Software Toolbox and ICONICS.

2) Check for "event"
You could "poll" (synchronously or asynchronously) the OPC items (Tags) on a regular basis and check for the change in the process that needs to signal Excel.
VB(A) programs can use the OPC OLE Automation interfaces to the OPC Server to do this.
(C++ can use the OPC Custom interfaces.)

3) publish data to Excel
Excel has automation interfaces which allows you to control the Excel object in your favorite way. Details can be obtained from microsoft.com, but to get you started some VBA/VS Script example code that pumps the excel sheet with process data::

' connect to running MS-Excel
Set xl = GetObject(, "Excel.Application")
' connect to available MS-Excel Sheet
Set sh = xl.Worksheets(1)
For i = 1 to 5000
sht.Cells(i, 1) = "my result"
Next i

Good luck!

Regards,
ICONICS EUROPE BV

Raymond van der Tas
www.iconics.com
 
One solution is to install AB's OPC server on the PC, I think it's free with RSLinx lite. Then use an activeX OPC client (I like the one from SoftwareToolbox.com) and your favorite programming language (I use VB) to bring the data into an Excel file. You'll have to install some type of KTX card in the PC to get on the DH+ or DH485 bus.
 
You will need RSVIEW32 or RSLINX -OEM edition and UP.

Both of these programs have OPC/DDE capabilities and you could easily bring tags from PLC to a excel file.
 
Hello,

Look at TAL Technologies. They make a software package like WinTal that I've used before and automatically send the data to Excel or by a hot button. Cost is about $100 to 200 depending on system. It is a serial device to Excel application.

Robert Pauley
Automation & Control
KB Electronics, Inc.
 
I would just use and I/O server, like Wonderware's ABTCP. You can set it to run as a service, and set it up simply.

Once you add the topic (tell the I/O server which PLC to get data from and how), you simply add something like this in an Excel cell:

=abtcp|abplc2!'f32:48'
 
P

Paul Cunningham

If you have RSLinx Pro, or Gateway, you can set up a DDE/OPC topic that will easily allow you to enter addresses in Excel fields that will reach out through Linx to the network to the SLC's or PLC's. This IS NOT so easy with RSLinx Lite. I can send you an example if you email me.
PC ( paul_cunningham @goodyear.com )
 
Raymond,
I just ran across this short tutorial while searching for an answer to a problem I'm experiencing. You see, I have a client running RSLogix 500, and they want to have an excel workbook with 21 sheets, each with 4 columns and 50 rows of data. In addition, there needs to be Run Start/Total Run Times, and the date of the run on each sheet. Sheets 2-50 are "past runs," and will not require updating constantly.

I don't know if there's a way to avoid polling all the "past run" data, or if all this data retrieval will put an excessive load on the client PC running Excel. I'm not a whiz @ VB, but I did do some simple programming using it in college. Can you give me a little tutorial on how to achieve all this?

In the end, I don't want to have 4400 lines of
[A1] = ItemValues(1)(0) etc.

That's how the RSLogix Trainer CD teaches us how to do data retrieval like this. I'm sure you realize just how inefficient and cumbersome this would end up being.

Please reply at your earliest convenience.
 
C

Chandan suthar

Dear,
It's very simple to communicate Allen Bradley PLC and excel communication.

just open a new excel sheet with any name like "RAM", then open RSLINX and communicate the Allen Bradley PLC then open RsWho and right click on PLC then select Data Monitor then double click on any on Data file then double click on any Bit then save and ok close it.

Then In Excel Right click where you want to fetch data then select Paste special click on URL and then ok
 
Since the original post in 2002 there's a lot more options - plenty of off-the-shelf products have been developed since then that already have communication drivers embedded to just about all the branded PLCs or other I/O out there and can do all the calcs that Excel does and have set up for report generation all built in. Not sure I know all the names but I know of 3 at least - XlReportManager, XLReporter and more recently Dream Report and probably more out there as well. I'm a firm believer in off-the-shelf products if it means I'm going to reduce the tools needed to one, especially when it comes to having to troubleshoot it when something stops working. I guess maybe it's just a comparison of engineering time versus product cost.
 
Top