If you would like to display data from your Allen-Bradley PLC or PAC in Microsoft Excel, and have a licensed copy RSLinx Classic (aka RSLinx,) it can be accomplished using a few simple steps we'll cover in this article.
But first, lets identify which copies of RSLinx (aka RSLinx Classic) are licensed by reviewing all the versions below:
- RSLinx Lite (free download, will not work:) This is the free unlicensed version of RSLinx which comes packaged with RSLogix and PanelBuilder. It is used as a software driver to connect your PC to your Allen-Bradley devices. NOTE: This version is not an OPC or DDE server and can not be used to connect to Microsoft Excel.
- RSLinx Single Node ($541:) This version is a local OPC and DDE server for one “device” (one PLC or PAC,) and also has all the features of the Lite package
- RSLinx OEM ($1090:) This version is a local OPC and DDE server for unlimited “devices” (PLC, PAC,) and also has all the features of the Lite package
- RSLinx Professional ($n/a:) This version was a local OPC and DDE server like OEM, but also included the RSLadder 5 and 500 ActiveX's. This package is no longer available.
- RSLinx Gateway ($2,290:) This version is a Local and Network OPC and DDE server, but also includes the ability to use a PC as a network bridge. In the past it was common to have a single PC on both Ethernet (office network) and DH+ (PLC network) and use RSLinx Gateway as a bridge between the two.
- RSLinx SDK ($n/a:) This version of RSLinx was for the development community and included documentation and examples of connecting to RSLinx from development environments like Microsoft's VisualStudio. It also included a license for RSLinx OEM so users could test the code they wrote.
With the above in mind, you'll need a version of RSLinx (Classic) other than the free “Lite” version for the following steps to work:
Getting A-B PLC data into Excel using RSLinx
Step 1: Connect your PC to your PLC and setup RSLinx to communicate to it. In the below picture you can see my setup where I've connected my laptop to an Ethernet network which also has a MicroLogix 1100 on it.
Step 2: In the right pane of the RSWho window right click on your PLC and select “Configure New DDE/OPC Topic.”
Step 3: In the DDE/OPC Topic Configuration popup window find your topic on the left side highlighted in blue.
Step 4: Type in a new unique name for your new topic. Here I've typed in “MyTopic” as the name of my new topic.
Step 5: Now click on the “Data Collection” tab. Note – If you're asked if you would like to update your topic, select “Yes.”
Step 6: Change the “Polled Message (mSec)” rate to the rate you would like data from the PLC to update at. Here I've chosen 100 milliseconds.
Step 7: Press “Apply,” and then select “Yes” on the confirmation popup.
Step 8: Click “Done” to close the DDE/OPC Topic Configuration popup window.
Step 9: Still inside of RSLinx, select “Edit” from the top menu and then select “Copy DDE/OPC Link…”
Step 10: Browse to a PLC address or tag by expanding your new topic, then the online folder, then select a tag or element and press OK. In the below picture I've selected T4:0.ACC.
Step 11: Open a new workbook in Microsoft Excel. Right click on a cell and select “Paste Special.”
Step 12: From the “Paste Special” menu choose “Paste Link”
At this point in the process you should see live data from your PLC (or PAC) in your Excel workbook.
If you would like to explore additional ways to connect RSLinx and Excel, including button macros to upload and download multiple cells at once, search RSLinx's help system for “EXCEL.”
I hope the above information on getting data from your A-B Programmable Controller into Microsoft Excel was helpful. If you have any comments or questions on the above please feel free to leave them by filling out the “post a comment or question” link below.
Click HERE to scroll down to view or leave comments
- Hey Shawn,
First, if I need to capture data (counting parts and tracking downtime) from 18 PLC's, would it be better to use a single spreadsheet and move the data from a live spreadsheet to a different sheet (same workbook) titled the machine name, or just use multiple workbooks, each with a livedata sheet and a captured data sheet?
Second, is it possible to use a log data trigger from the PLC? When the machine is stopped/Faulted I start a timer to determine downtime and what I want to do is log downtime once the machine has started. Is there a way to set up handshakes?
Thanks for your timeGood morning ,
Q1) If it were me I would use one sheet to collect all the data, and then display that data in other sheets in the same workbook because this way I would only have one sheet to configure to read PLC tags.
Q2) There's a couple of different ways to do this.
- One way is to have Excel read from an intermediate data table instead of a raw data table. Then you only copy the data you want into the intermediate data table. In other words, only copy the data to the intermediate data table when you have a downtime so that's all excel sees.
- The other way is to use SyTech's XLReporter which has too many features to count (including recording data base on triggers.) If you want to learn more about what their product can do just give SyTech a call at 508 520 9957 and tell them Shawn said XLReporter sounded like a good fit for your application.
Hope this helps,
Shawn Tierney, TheAutomationBlog.comHi Shawn
Is it possible the opposite to happen? From an excel sheet
into the program
ThanksGood morning Dimitris,
Yes. Check out the RSLinx Help for VBA code for Excel - it includes examples for a single register write as well as a block write.
Instructor, The Automation School