PLCs & EXCEL

Interacting with PLCs via Microsoft Excel: A Surprisingly Effective Approach

You might get a raised eyebrow or a smirk when talking about Microsoft Excel interacting with PLCs. After all, in an era of cutting-edge automation software, using Excel might sound like a throwback to the ’90s. But before you dismiss it as outdated, let’s delve into why Excel remains a surprisingly effective tool for PLC interactions, particularly for Allen-Bradley controllers. From simple setpoints to complex sequences, Excel still holds its ground in industrial automation.

The Nostalgic Charm of Excel and PLCs

Back in the day, Excel was the go-to tool for many tasks beyond just spreadsheets. Its ability to handle data, run macros, and interface with other software made it a versatile ally. When it came to industrial automation, Excel’s integration with PLCs offered a way to manage data without needing to dive deep into the often-intimidating world of PLC programming. Fast forward to today, and Excel is still around, doing its thing, proving that some classics never go out of style.

Simple Use Cases: Writing Setpoints

One of the most common uses of Excel with PLCs is writing setpoints. Whether it’s setting temperature ranges for a process or storing recipe parameters, Excel makes it easy. You can input the desired setpoints in a familiar spreadsheet format and, with a few clicks, send them off to your PLC. You can also read in current values in the PLC to help maintain a record set of critical parameters. No need to fumble through complex ladder logic or other programming environments.

Complex Use Cases: Reading/Writing Pinning Charts

Now, let’s get into the nitty-gritty—using Excel for more complex tasks like managing pinning charts for sequencers such as CIP (Clean-in-Place). And “pinning,” by the way, in this context harkens back to the ancient player-piano-like drum sequencers where “pins” on the drum triggered relays in a sequential, timed order to execute a process sequence. Excel has been used for decades to emulate the layout of the pins on the drum but instead in a row denoted with 1 or 0 to Energize or De-energize associated outputs. Adding read/write functionality to these sheets allows management of these sequences in a way that’s both intuitive and efficient without having to get into the details of the PLC code.

Advantages of Using Excel with PLCs

• Accessibility:
  • User-Friendly: Almost everyone knows how to use Excel, making it a low-barrier tool for managing PLC data.
  • Visualization: Excel’s tabular format is perfect for visualizing and managing data, from simple setpoints to complex sequences.
• Flexibility:
  • Data Manipulation: Excel’s powerful functions and formulas allow you to manipulate data before sending it to the PLC, ensuring accuracy and efficiency.
  • Automation: VBA (Visual Basic for Applications) enables automation of repetitive tasks, saving time and reducing errors.
• Ease of Configuration:
  • Quick Changes: Need to tweak a setpoint or adjust a sequence? Excel lets you do it quickly without diving into the PLC’s programming environment.
  • Bulk Operations: Configuring multiple devices or parameters simultaneously is a breeze with Excel.

Disadvantages of Using Excel with PLCs

• Troubleshooting Challenges:
  • Bit Masks: Writing pinning charts as bit masks can make troubleshooting tricky. The values lack context when viewed directly in the PLC, so without the Excel file code is extremely difficult to understand.
  • Debugging VBA: While VBA is powerful, troubleshooting scripts can be a headache, especially if you’re not a seasoned coder.
• Reliance on VBA:
  • Complexity: VBA can be complex for those who aren’t familiar with programming, potentially creating a steep learning curve.
  • Maintenance: Keeping VBA scripts up-to-date and error-free requires ongoing effort and expertise.
• Data Integrity:
  • Manual Errors: Manual data entry in Excel is prone to mistakes, which can lead to incorrect configurations or setpoints being sent to the PLC.
  • Version Control: Tracking changes and ensuring the correct version of a configuration is applied can be challenging.

Modern Solutions: Rockwell Automation PlantPAx

Just when you thought using Excel might be too old-school, along comes Rockwell Automation’s modern DCS, PlantPAx, to bring some modern flair to the game. PlantPAx includes an Excel-based Online Configuration Tool that streamlines the process of configuring devices in bulk. This tool standardizes the approach, making it easier and more reliable to manage large-scale operations, especially when implementing a project from scratch.

Advantages:

• Standardization:
  • Consistency: Ensures that configurations are applied consistently across all devices.
  • Documentation: Provides better documentation and traceability of changes.
• User-Friendly Interface:
  • Simplified Configuration: Reduces the need for complex VBA scripts by providing a more intuitive interface for configuration tasks.
  • Bulk Operations: Facilitates bulk operations without the need for custom scripting.
• Enhanced Troubleshooting:
  • Contextual Data: Provides more context for the data being written to the PLC, making it easier to troubleshoot issues.
  • Error Handling: Improved error handling mechanisms reduce the risk of misconfigurations.

Conclusion

While using Microsoft Excel to interact with PLCs might seem like an antiquated practice, it still has its place in today’s industrial automation landscape. Excel offers a user-friendly, flexible, and efficient way to manage setpoints, sequences, and other parameters without needing to become a PLC programming expert. Despite its limitations, such as troubleshooting challenges and reliance on VBA, Excel remains a valuable tool for many engineers and technicians.

Modern platforms like Rockwell Automation PlantPAx have taken this a step further by streamlining these interactions and reducing the need for custom scripting, making it even easier to manage complex industrial processes.

So, next time someone raises an eyebrow at your Excel-based PLC interactions, you can confidently explain that sometimes, the classics still get the job done—and quite effectively at that.