Program a plc from an Excel spreadsheet?

[Guest LuckyHack]

I'm a long-time rung monkey, and ladder logic is the only tool I've ever used to program a plc. I've heard on several separate occasions that a plc can be programmed from code developed solely in an Excel spreadsheet. I've only ever heard this from someone who did not do it himself, but knows somebody who did it. My question is, can it be done? And if so, just how does one go about it? And can it been done on AB plc's? LuckyHack

[glaverty 1]

Find out for yourself. Assuming your using RSLogix 500 copy a rung of your choice, then go to Excel and hit paste. See what happens.

[Guest]

Yo, Lucky, The short answer is “yes - it can be done”. The bad news is that you won’t be able to do much with your “home brew” code after it’s been written. Let me guess - you’re hoping that you can program a PLC without having to buy the “expensive” official software package. If that’s what you’re up to, I’d suggest that you go ahead and spend the money and buy the software. But to answer your question ... the screen shot below shows a sample rung in an Allen-Bradley PLC-5 program - and the “ASCII text” representation of the same rung. The string shown inside the Notepad window is what (I think) you’re trying to come up with. So the good news is that once you’ve figured out how to interpret the ladder logic “code” - you COULD conceivably write the ladder logic in Excel - or even in something as simple as Notepad. Hints: BST = “Branch start” XIC = “Examine If Closed” NXB = “Next Branch” and so on. If you have RSLogix available, then you can easily see how this “code” stuff works by double-clicking the rung number (example: 0000) until the editor shifts into the ASCII edit mode. Once the “magic text” lights up on the RSLogix screen, you can use the Windows Clipboard to Copy and then Paste the code into something like Excel. Then you could make changes to the code in Excel - and then Copy and Paste the altered code back into a rung in RSLogix. But that’s the easy part. Now for the bad news. Once you’ve got your ladders written, how do you plan to download them into the processor? That’s the hard part of what (I think) you’re trying to accomplish. If you don’t have the official programming software, then how do you intend to do the download? Bummer! That’s going to be EXTREMELY hard to do. And worse yet, there’s a LOT more to a PLC program than just the structure of the rungs. Don’t forget about the memory contents - the I/O configuration - the channel configuration - etc. - etc. Finally, it’s entirely possible that I have totally misjudged what you’re trying to accomplish. If so, then please post again with some more detail as to just why you’re asking these questions. Then I (or someone else) might be able to come up with a better answer for you. Best regards, Ron

[Guest LuckyHack]

Thanks for the detailed and well composed reply, I guess I was a little vague on my original posting. It was my first visit to MrPLC and wasn't sure I'd get a reply at all. I have Rockwell's software, and I am aware of what you get when you double-click on a rung I realize the complexity of the programs I have wriiten, including data files, comm configs, etc. That's why it sounded so strange to me when I heard the claim that it could be done in Excel. The fact that I heard it from more than 1 person, on more than 1 occasion, made me wonder why I hadn't heard of it more widely, and I wanted to ask someone else in the industry, since the people I'd heard it from were not the ones who had allegedly done this successfully. My first reaction was that it was plc urban legend. So is the concensus of the group that this claim is bogus?

[Guest]

Well, maybe not "bogus" - rather it was probably just a "failure to communicate". Check out this link: Getting Results with RSLogix5 Manual Look at Adobe Reader page 86 of 115. This describes "Editing Databases with Excel" - now this IS possible. Just keep in mind that the term "database" is used to indicate the DOCUMENTATION portion of your program - specifically, things like instruction comments, rung comments, etc. These CAN be handled in Excel - and then imported back into RSLogix. Maybe that's what your sources were talking about. Common source of confusion: "Database" indicates documentation (example: rung comments, instruction comments, etc.) "Data Table" indicates the processor's memory locations (example: T4 files, C5 files, etc.) Best regards, Ron

[Gerry 13]

Just about any program that will enable you to manipulate text could be used to generate the mnemonic codes for importing into RSLogix. However, its hardly worth the effort to set it up unless you have a lot of similar, repetitive rungs to enter, or you have some particular application that you have to implement many times with small variations. There was a company in the late 1980's selling a system for generating process control (PID, etc.) programs for the PLC5 using AutoCad. You created an instrument drawing using blocks from their custom library, then ran their code-generating function (AutoLisp??) which churned out the text file for importing into 6200 s/w (no RSLogix then). Of course, there was no way of going backwards - getting PLC edits into the AutoCad drawing.

[Elcan 1]

It seems that it can be done: 

https://www.reddit.com/r/PLC/comments/54vxqu/autogenerate_plc_code/?st=iza2xr4g&sh=a35af8e9

[JRoss 84]

I use Excel to generate repetitive logic, such as alarms or I/O mapping. I create one rung in Logix, copy into Excel, then use a variety of Excel tricks (formulas, auto-increment) to create as many duplicate rungs as I like. You could probably use the method to write an entire program, but that seems silly. It's fantastic for repetitive stuff, but it can't do syntax checking or anything like that. I have worked with a company that starts large projects by using software to auto-generate an L5K file with boiler plate code for things like sequences, but even they do the real work in Logix.

[Michael Lloyd 85]

I use Excel to create a structured text file for mapping IO. I'm on my Mac right now or I would post an example. Basically I create a table for types of IO (motors, valves, etc) and when I fill in the blanks it creates the structured text for mapping the IO point to the tag array created from a User Defined Data Type. Consistency in tag creation is the key to using the spreadsheet. It used to take days to map the IO on a large project. Now it's the last thing that I do and it takes less than 10 minutes for a large project.

I also use it to create the documentation for an analog User Defined Data Type that I made a long time ago and use frequently. It's got 14 (? I think) points and when there are a couple of hundred analog IO points it's handy.  14 x 200 = 2,800 distinct lines of documentation that I don't have to type but one time and that is done in my IO sheet so it was going to be done anyway. This also used to take hours and it's now copy, paste, set the number of IO points, run... then copy and paste the result into the tag database dump (CSV file) and reimport. A friend of mine used his VB skills to make the spreadsheet work.