How to create PLC ladder logic code with Excel

Programmable logic controller (PLC) insights

• PLC ladder logic code can be mass produced with Microsoft Excel, which can help with any code that is repetitive in nature.
• It is another way to automate processes that would otherwise take an enormous amount of time for a task that, while valuable, keeps engineers from performing other important tasks.

The automation industry is always looking to make processes more efficient such as implementing batch control logic for a food and beverage manufacturer or designing reports to track system downtime in a pharmaceutical facility, efficiency is key. It is not just limited to the solutions in the field, and there are means to simplify and automate the process of writing programmable logic controller (PLC) code.

Rockwell PLC ladder logic code can be mass produced with Microsoft Excel. This is useful for generating repetitive logic, like input/output (I/O) mapping and alarms but can be applied to any code that is repetitive in nature. In the example below, an I/O buffer routine is generated for the analog inputs.

Prepare the I/O list

The I/O list specifies the input tag name as well as the rack/slot/channel for each instrument in the field.

Figure 1: The I/O list specifies the input tag name as well as the Rack/Slot/Channel for each instrument in the field. Courtesy: ACE

There is an additional column (Column H) added to the I/O list to account for an Alarm AOI block to which the scaled process value (or simulated process value) is passed. There is more on this alarm tag below. Include the “T” in the name of the buffering tag to indicate this value is from the field transmitter, while the internal process value tag ends in an “I” to contain the process variable that is indicated on the screen. With this convention in mind it is easy to use Excel’s Substitute() function or the copy and replace menu options to quickly create the data for this column using the I/O Tagname data as a basis.

Create the first rung

Begin by manually creating the first rung of the I/O mapping routine—in this case, the I/O mapping of Compressor 1 Inlet Pressure transmitter, shown below— and then double click on the rung number to reveal the underlying ASCII text for that rung of code. This will be the code to base the rest of the mass-produced ladder logic from.

In Figure 2 the text reads:

“bufferAIInt PIT_L6_502 Rack02:1:I.Ch0Data SimEnable PI_L6_502”

Figure 2: The text reads “bufferAIInt PIT_L6_502 Rack02:1:I.Ch0Data SimEnable PI_L6_502” Courtesy: ACE

The syntax gives Add-On Instruction (AOI) and its associated arguments.

Figure 3: The Alarm AOIs are processed in another routine and only referenced by the block shown here. The code for creating alarms is also a good candidate for Excel generated code. Courtesy: ACE

Copy and paste the text from Logix Designer into Excel.

Break the text into cells

In the figure below, it shows what the rung looks like once it is copied into Excel. The current format is not useful.

Figure 4: Here it shows what the rung looks like once it is copied into Excel. The current format is not useful. Courtesy: ACE

The Text to Columns Data Tool under Data menu can be used to quickly delimit the string. Multiple iterations allows for separating the arguments (spaces) and the input locations (colons and periods). The result of this is shown in the figure below.

Figure 5: The Text to Columns Data Tool under Data menu can be used to quickly delimit the string. Multiple iterations allows for separating the arguments (spaces) and the input locations (colons and periods). Courtesy: ACE

Create the new rungs

With the code broken into different components, the cells can be linked to represent rack number, slot number, channel number, and tag names to the corresponding cells in the I/O list. The above diagram shows the Formula Bar referencing the rack number from the I/O list.

Once the references have been entered for each cell, highlight the row of cells and drag the fill handle down the sheet to populate the sheet with the new rungs of code.

Figure 6: Once the references have been entered for each cell, highlight the row of cells and drag the fill handle down the sheet to populate the sheet with the new rungs of code. Courtesy: ACE

Using the Concatenate function in Excel, rebuild the data into a single string per row—remembering to add back in the delimiters, which were spaces, colons, and periods in this example.

Figure 7: Using the Concatenate function in Excel, rebuild the data into a single string per row—remembering to add back in the delimiters, which were spaces, colons, and periods in this example. Courtesy: ACE

Put the new rungs into the routine

With the newly created string for each rung, the data can be copied from the cells directly into Logix Designer. This can be done in bulk and does not need to be done cell-by-cell.  Select all of the cells and copy the data. Then paste it into the ASCII text display copied in from earlier.

Figure 8: With the newly created string for each rung, the data can be copied from the cells directly into Logix Designer. Courtesy: ACE

This will quickly add the I/O mappings as new rungs of code. If this is new code where the tags have not yet been created the new rungs will show in red.

Figure 9: This will quickly add the I/O mappings as new rungs of code. If this is new code where the tags have not yet been created the new rungs will show in red. Courtesy: ACE

To remedy this, define the new tags using the I/O list and create an import CSV. Rockwell has documentation about their Import utility in their knowledgebase. Excel formulas were used in this example to create an import file.

Figure 10: Excel formulas were used in this example to create an import file. Courtesy: ACE

Importing this CSV file will resolve the errors and get rid of the red lines in our routine so the code can be compiled and tested.

This is a useful tool to save time when writing repetitive PLC code, such as I/O mapping and alarms. Please note that the amount of time saved is directly correlated to the size of the system you are working with (i.e. larger systems have more I/O). A method like this may not be useful for a smaller system. However, for larger systems the time and cost savings, not to mention the elimination of transcription errors, will make a big difference in a project.

– Applied Control Engineering is a CFE Media content partner.

Original content can be found at Applied Control Engineering.

Do you have experience and expertise with the topics mentioned in this content? You should consider contributing to our WTWH Media editorial team and getting the recognition you and your company deserve. Click here to start this process.