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.
Until next time, Peace ✌️
If you enjoy this episode please give it a Like, and consider Sharing as this is the best way for us to find new guests to come on the show.
Shawn M Tierney
Technology Enthusiast & Content Creator
Eliminate commercials and gain access to my weekly full length hands-on, news, and Q&A sessions by becoming a member at The Automation Blog or on YouTube. You'll also find all of my affordable PLC, HMI, and SCADA courses at TheAutomationSchool.com.
- Siemens TIA Portal v20 (P227) - November 13, 2024
- Automation Q & A for November 8, 2024 (QA022) - November 8, 2024
- PRTG’s OPC UA Server: IT & OT Monitoring for your SCADA, HMI, or PLC (P226) - November 6, 2024
Discover more from The Automation Blog
Subscribe to get the latest posts sent to your email.
I had this working on my current computer about 7 years ago with a PLC-5.
Since that time, our IT Department has upgraded our Office suite to Microsoft 365 for business. I now cannot seem to find a way to get a valid connection – just a #REF! message.
I assume this has something to do with the Trust Center settings. I’ve tried messing around with many of the settings (loosening the security) and have, so far, not been successful.
Do you have any ideas or suggestions?
My first guess would be the problem is elimination of DCOM has broken the connection.
I haven’t tried any workarounds myself, so you may want to check out the RA Knowbase.
PS – FTLinx now has a similar feature, so you may want to consider upgrading to the newest Linx:
– https://theautomationblog.com/factorytalk-linx-podcast/
Shawn
So I had gotten this working on my controls laptop using a single-license copy of RSLinx, but that license is shared and it needed to go back to the operations workstation. I have a gateway license on the server, so I loaded up Excel there and tried the same file that was already working. My problem: On the laptop, it was reporting the boolean data as 1 or 0, but when I run it on the server it shows the word value. Any idea why it would work differently between the 2 machines?
Thanks for your comments Stephen, and no, I haven’t run into that?
Are both versions of RSLinx the same? I know some of Rockwell’s software likes periods for bits, but others want slashes…
If you figure it out please let us know!
Shawn
Join my free community to follow along! You can also become a member and support our work at: Automation.Locals.com
Very good article, worked perfect fine!
For my application though, I need the date to only update with a push of a button in excel. Any help would be greatly appreciated
Good morning Oleg,
If the time in the PLC is correct you could also grab it at the same time, otherwise you’ll want to use an Excel time function.
Sincerely,
Shawn Tierney
sorry, I miss-typed. I need the DATA to only update at the push of a button. I do NOT want it to update every second or so, but only at the push of a button.
Good afternoon Oleg,
By default the display updates at the update rate, not based on an action.
You may want to try triggering a ViewME macro with a button, the macro can be used to set internal memory tags to the value of PLC tags.
Then you display the memory tags on your graphic.
Sincerely,
Shawn Tierney
Join my free community to follow along! You can also become a member and support our work at: Automation.Locals.com
Hey Shawn – like a challenge? Ok, maybe not for you. Here’s what I have.
Wanting to upgrade from a SLC 500 CAT1747-L30C, Ser C (18 24DC inputs; 12 outputs) to a MicroLogix something. I picked a 1200 (20 24DC inputs; 16 outputs) and went by the article you sent me, but doesn’t have to be. I bring up the SLC 500 program in RS Logix500 Starter, and open Controller Properties. I select the Micro 1200 Ser C. It says I may lose data or I/O configuration. I can check/uncheck Resize Data Tables and Clear I/O. No matter what I do, when I select Apply, my Memory Used is a hash mark, whereas it previously showed Instruction words used and data table words. What am I doing wrong? Next, I change all the OSR to ONS. When I verify the project, I get a Compiler Error 7003 in a rung with a MCR in it. Will the program now work in a 1200? Can I get it to work in any Micro? Cheers and God bless
Good Evening Mastenbrook,
The 1200 does support the MCR, so I’m not sure why you are getting an error? Are you using the latest conveter from Rockwell? (I believe they are on v3 now.)
If you would like to hire me for an hour or two I would be more than happy to help with the conversion, otherwise my hands are full recording addition PLC training courses,
Sincerely,
Shawn Tierney
Hello Shawn – been awhile, hope you are well, I have to upgrade an AB SLC 500 CAT#1747-L30C, Ser C and I have to interface it with a Pro-Face QPKSTDN0000 touch screen. How would you do it to minimize program rewrite? Many thanks and God bless
Good morning mastenbrook,
Good to hear from you again.
I wrote an article about just that here:
https://theautomationblog.com/replace-a-fixed-slc-500-with-a-micrologix/
If you go the ML1200 route, I suggest getting the unit with two ports as well as a 1761-NET-AIC to convert the RS-232 comm 0 port to 485. (unless your Proface also has a 232 serial port)
Hope this helps and God bless,
Shawn Tierney
Join my free community to follow along! You can also become a member and support our work at: Automation.Locals.com
it is an error message that says “RSLINX.EXE” is damaged or some parts are missing. Could you help me?
Good morning Miguel,
If I received that error message I would first search Rockwell’s knowledgebase at http://www.ab.com, and if I didn’t turn up anything I would reinstall RSLinx Classic.
By the way, what version of Windows, RSLinx, and Excel are you using? And is your RSLinx the OEM version, or something different?
Sincerely,
Shawn Tierney
Im working with Excel 2007 and Rslinx Classic OEM. I followed instructions of many sources but at the end I get the same message when I try to run.
Good morning Miguel,
Windows 7 64bit? RSLinx 2.59? Did you try the OPC test app to see if RSLinx is serving data at all?
I found this in Rockwell’s knowbase which may help:
https://rockwellautomation.custhelp.com/app/answers/detail/a_id/42787
RSLinx Classic : Monitor Data in Excel using DDE
https://rockwellautomation.custhelp.com/app/answers/detail/a_id/533000
Microsoft no longer supports DDE with Windows 7 and beyond First verify RSLinx is running and communicating over OPC by using the OPC Test Client.
Best of luck!
Shawn Tierney
So far I’ve had no luck getting this working on Excel 2013. Any ideas?
Good afternoon CR,
What version # and package of RSLinx Classic are you using? And on what OS?
Sincerely,
Shawn Tierney
Hai shawn i did these experiment but last section in paste option not working in excel it takes more time and showing program not responding. Can u tell me the solution..
I am also having this problem. As you as you paste and hit “enter” in excel, the program just hangs and crashes.
Good morning Anu and Matt,
I have not seen that issue myself, and the feature has been in RSLinx for many years.
That said, there are so many different versions of Excel, RSLinx, and Windows that I’m not sure anyone could test all the different combinations.
PS – Just a reminder that this feature is not part of the free RSLinx Classic Lite, but only part of one of the paid packages like Single Node, OEM, Pro, etc.
Hope this helps,
Shawn Tierney
The excel macro had worked fine on a different computer. I finally got it to work after ending all instances of rundll32.exe using the processes tab in task manager.
Thanks for the update Matt!
Shawn Tierney
Hi Shawn, finally i solved the problem in excel. Problem occurs due to rslinix version. Now its working perfectly. Thanks Shawn.
I have another one doubt how to write the data (excel to plc)?
Good afternoon Anu,
Thanks for the update! Glad to hear you got it working!
PS – There is an example “Excel marco” in the RSLinx Classic help that shows how to perform a single or block write.
Shawn Tierney
Join my free community to follow along! You can also become a member and support our work at: Automation.Locals.com
used this today worked awesome just wish i could write to a different cell each time it updates
Good morning Jesse,
You can, but you would need to write vba code to do it, or add an “offset” tag in the PLC to represent the row.
Another way to do it is to record all the data in a PLC data file like N7:0-255, and then read the entire data file into Excel.
But I have to admit those who want really detailed Excel reports usually go with something like SyTech’s XLReporter or Ocean Data’s Dream Report.
Sincerely,
Shawn Tierney
Join my free community to follow along! You can also become a member and support our work at: Automation.Locals.com
This rudimentary code may help as a seed for customizing your own. This example will log 7 tags every 3 seconds until a stop trigger is activated. The DDE/OPC links are on Sheet 2/Row 2 for live monitoring. The log data will be on sheet 1 with headers in the first row. Copy from “Public” to the end and paste it into the editor. I assigned macro buttons to the Start_Logging and End_Logging macros. Another option is to assign a tag to a cell and assign the cell value to the stop_log Integer.
Public eTime As Date
Dim stop_log As Integer
Dim count As Integer
Sub Logging()
eTime = Now + TimeValue(“00:00:03”)
Application.OnTime eTime, “Logging”, , True
Sheets(“Sheet1”).Range(“A2:F2”).Offset(count).Value = Sheets(“Sheet2”).Range(“A2:F2”).Value
count = count + 1
‘stop the procedure:
If stop_log = 1 Then
Application.OnTime eTime, “Logging”, , False
count = 0
End If
End Sub
Sub Start_Logging()
stop_log = 0
count = 0
‘ Clear data for next cycle
Range(“A2:I30000”).ClearContents
‘ Jump to Logging macro
Call Logging
End Sub
Sub End_Logging()
stop_log = 1
End Sub
You may have to replace the ” and ‘ characters using the standard keyboard characters.
[…] NOTE: The newest version of this article is located at our sister site, The Automation Blog […]
[…] Getting Allen-Bradley (A-B) Programmable Controller data into Excel using RSLinx Classic […]