IMDHMIControls LibraryShop main store

How-to

Getting CompactLogix PLC data into Excel over EtherNet/IP

SCADA and data logging Published 9 min read

Short answer

Excel has no EtherNet/IP client, so a CompactLogix reaches a workbook only through a driver layer: an RSLinx edition that documents OPC or DDE support, or a driver library used within its license — RSLinx Classic Lite does neither. Put the PC and controller on one subnet, prove a single controller-scoped tag read, map that one cell, and add writes only against a dedicated test tag the logic validates.

On this page
  1. Why doesn't my CompactLogix data show up in Excel over EtherNet/IP?
  2. Which RSLinx edition can put PLC data into Excel?
  3. What do I check on the PC and controller network first?
  4. Which route should I choose: RSLinx OPC and DDE, or a driver library?
  5. How do I prove one tag read before mapping anything?
  6. How do I add writes without risking the running process?
  7. What do I verify before the workbook goes into service?
  8. Which computer should run the workbook at the machine?
  9. Questions asked

Your workbook is not broken and the 1769-L27ERM is not the problem: Excel has no EtherNet/IP client, so nothing in a spreadsheet can address CompactLogix tags by itself. A driver layer has to sit between the cells and the controller — an RSLinx edition that documents OPC and DDE support, or a driver library used inside its own project and within its license terms; RSLinx Classic Lite cannot fill that role. This article gives the network check with real addresses, the route decision, the one-tag-read proof, and the write-back rules that keep the controller in charge — in that order, because that order finds the fault instead of guessing at it.

Why doesn't my CompactLogix data show up in Excel over EtherNet/IP? #

Excel has no EtherNet/IP client built in, so a workbook never reaches a CompactLogix by itself: something that speaks CIP tag messaging and exposes OPC items or DDE topics must sit between the cells and the controller, and that layer is where every failed sample workbook breaks.

The path that works has three layers, and a cell updates only when all three exist: the controller serving its tags over its Ethernet port, a driver that speaks the tag protocol and exposes them as OPC items or DDE topics, and Excel referencing that driver. Studio 5000 programs the controller but contributes nothing to Excel; owning every vendor seat does not create the missing layer.

A ControlLogix sample failing on your CompactLogix says nothing about the controller family, because the same EtherNet/IP drivers list ControlLogix and CompactLogix together. The sample fails on the layer it shares with your project: its topic points at a different address, a different slot, or tag names that do not exist in your program.

Decision: stop editing formulas and prove the driver layer first — that is where this fault lives.

Which RSLinx edition can put PLC data into Excel? #

Only an edition whose vendor documentation lists OPC and DDE support can serve a workbook, because the RSLinx Classic getting-results guide states that RSLinx Classic Lite does not support OPC, DDE or the published C API — the free Lite download is legitimate, it just cannot do this job.

Check the vendor's edition table before you plan anything around a seat: the documentation shows which revisions support the DDE and OPC interfaces Excel uses, and Lite is never on that list. Lite keeps real value for browsing and programming access; it simply cannot serve a workbook. If the bench PC runs Lite on Windows 10 and devices show globe icons, fix that separately with the RSLinx Classic Lite globe icons on Windows 10 fix before you judge the network.

With a capable edition installed, prove the topic before Excel opens: define a topic for the controller in RSLinx, read the test tag in an OPC test client, and Excel inherits a connection that is already proven.

Decision: a DDE/OPC-dependent workbook needs an edition that documents DDE and OPC support; no amount of configuring Lite changes that.

What do I check on the PC and controller network first? #

Put the PC and the controller on the same subnet first: with the controller at 192.168.1.10 and mask 255.255.255.0, a PC adapter at 192.168.1.50 lands in network 192.168.1.0 and reaches it directly, while 192.168.2.50 does not without a router.

Worked example. The controller sits at 192.168.1.10 with mask 255.255.255.0 and the PC adapter at 192.168.1.50 with the same mask. AND each address with the mask: 192.168.1.10 AND 255.255.255.0 = 192.168.1.0, and 192.168.1.50 AND 255.255.255.0 = 192.168.1.0 — one network, so the PC reaches the controller directly. Move the adapter to 192.168.2.50 and the same AND gives 192.168.2.0: a different network, every packet needs a gateway, and most driver connections fail exactly there. That mismatch, not the controller family, is the usual reason a downloaded workbook does nothing.

Then work in order. Take the controller's IP address from the running project, never from the sample workbook. Ping the controller from the PC that will run Excel, not from another bench machine. Open the driver software and browse to the controller. If the ping fails, fix address, mask, cable or gateway before configuring any topic.

Decision: a driver cannot rescue an unreachable controller, and the subnet check above is pass or fail before anything else is worth configuring.

Which route should I choose: RSLinx OPC and DDE, or a driver library? #

Choose one route and build the whole workbook on it: an OPC/DDE-capable RSLinx edition when the seats already exist, or a driver library such as AdvancedHMI and its EtherNet/IP driver used inside its own project and under GPL terms — never DLLs copied into a bare workbook.

Route What you install How Excel gets values License obligation
RSLinx edition with DDE and OPC That licensed edition on the runtime PC Cell references to a defined DDE or OPC topic Covered by the vendor seats you already hold
AdvancedHMI project The AdvancedHMI download and its EtherNet/IP driver, which lists SLC, MicroLogix, PLC-5, ControlLogix and CompactLogix Values exchanged through the AdvancedHMI project as its documentation describes GPL: distributed projects must make source code available to end users; the DLLs stay inside AdvancedHMI projects, and the base components may not be used in other packages
Third-party OPC server A server whose vendor documents CompactLogix tag access Cell references to OPC items Whatever the vendor's license grants, read before deployment

The AdvancedHMI route is genuinely usable in a plant — the driver list above covers your controller — but free to download does not mean embed anywhere: those terms are the project's own published licensing, and they are the reason its DLLs must not be copied into a bare workbook. Mixing parts of two routes produces an installation nobody can support.

Decision: seats exist, take the RSLinx DDE/OPC route; no seats, choose between AdvancedHMI's GPL source obligation and a commercial server license.

How do I prove one tag read before mapping anything? #

Prove a single controller-scoped scalar tag first, because one live cell separates driver faults from workbook faults: read a tag such as Test_Pump_Hours whose live value you can watch in the programming software, confirm it updates for a minute without reconnecting, and only then add more cells.

  1. Choose or create a controller-scoped scalar tag whose live value you can watch in the engineering software — a DINT such as Test_Pump_Hours is ideal.
  2. Record the controller's IP address and the slot its CPU occupies: an EtherNet/IP driver connection asks for both, and the slot comes from your project's configuration, not from a guess.
  3. Configure the driver or topic with that address and browse to the controller. To rehearse the whole flow first, point the same driver at a simulated controller on a virtual PLC test bench for protocol practice.
  4. Read the tag in the driver's own test client, change the value through an authorized method, and confirm the client tracks it.
  5. Map one cell — a DDE or OPC topic reference in the RSLinx route, the project's documented interface in the AdvancedHMI route — then change the tag three or four times and confirm the cell follows each time without a reconnect.
  6. Only now add more reads: one live cell has proved network, driver and mapping separately, so any later fault is a per-cell fault.

If the driver connects but the tag will not resolve, the reference is wrong: check the spelling, the scope — a program-scoped tag needs its program name in the reference — and the array or structure syntax, all in the form your driver's documentation defines rather than a copied ControlLogix example.

Decision: no cell gets mapped until one known tag has updated repeatedly through the driver alone.

How do I add writes without risking the running process? #

Write to a dedicated noncritical test tag only: a value from Excel lands in the controller with the same authority as an HMI entry, so outputs, mode bits, permissives and active setpoints stay out of the workbook until the PLC-side validation is proven.

Safety note: a write path into a running machine is a hazard path, so treat every Excel-driven write like an operator command and leave the rejecting logic in the controller, never in the spreadsheet.

Prove writes on a tag that touches nothing. Make Test_Setpoint a REAL with a permitted window of 0.0 to 12.5 bar, and the controller check passes only values inside it: 0.0 through 12.5 bar is accepted, and a cell holding 15.0 is refused before it can reach anything real. Write 0.0, 6.25 and 12.5 and confirm each lands; write 15.0 and confirm the logic refuses it and flags the fault.

Add bounds checking and an operator confirmation in the application layer too — the controller validation is the authority, the workbook checks are the courtesy.

Decision: outputs, permissives, mode bits, interlocks and live setpoints stay out of the workbook; a dedicated validated test tag is the only write target until the whole path is proven.

What do I verify before the workbook goes into service? #

Test behavior, not the first success: restart the PC and Excel, pull the Ethernet cable for ten seconds, and confirm the connection recovers, stale values become visibly wrong and writes block while the link is down.

A silent link drop reaches Excel as frozen or error values — the same comms timeouts that make HMI values show question marks when PLC comms time out. Make silence impossible instead: create a SCADA PLC communication alarm for link loss so a break is announced rather than discovered.

Then run this list:

  • Restart the PC and reopen Excel: the connection must return with no engineering software running.
  • Pull the Ethernet cable briefly and restore it: confirm recovery, visible stale-value handling and writes blocked while the link is down.
  • Compare every data type the workbook uses — REAL decimal places, DINT magnitude, BOOL states — against the controller's own values.
  • Write minimum, normal and maximum values to the dedicated test tag and confirm the controller's validation handles each one.
  • Deploy to a PC without the vendor's engineering suite and confirm the route still runs and stays inside its license; a workbook that reads only while engineering software is open carries an undeclared dependency.
  • Record the driver edition, topic or connection configuration, tag mappings, deployment files and license obligations before release.

Decision: the workbook goes into service only after a cold start and a cable pull both recover cleanly on the deployment PC.

Which computer should run the workbook at the machine? #

The workbook host is industrial hardware, not an office desktop: a compact industrial mini PC mounted next to the controller in the cabinet runs the driver and the logging workbook around the clock, and it is the right fit when the machine's data logger lives in the cabinet.

Give that host a fixed address in the controller's subnet, load the driver and workbook under an account that signs in at boot, and apply the license duties you recorded for the route you chose. Restart it once on purpose before the first shift that depends on it.

Decision: same cabinet, same subnet, fixed address, driver launched at boot — an office desktop on another subnet is where these workbooks die.

Questions asked

Does the controller need any logic changes to serve Excel?

No. The controller already serves its controller-scoped tags to any authorized EtherNet/IP client, so the workbook only reads or writes tags that already exist. Add one scratch tag as a write target if you want one, but the production logic, its timing and the controller scan stay exactly as they are.

How fast can Excel update values from the controller?

Set the update interval in the driver topic or project, not in the spreadsheet: the driver polls at the rate you configure and Excel displays what arrives. Cells in one topic share a single poll, so widen the interval instead of multiplying topics. Keep the rate modest when the same controller also carries I/O and HMI connections.

Can several workbooks read the same controller at the same time?

Yes. The controller serves multiple client connections, so each workbook holds its own topic or project connection to the same IP address. Watch the total: several Excel hosts, HMIs and engineering sessions on one CompactLogix add up, so keep poll rates modest and close connections you no longer use.

Can I keep my workbook source closed if I build on AdvancedHMI?

No. The project is GPL, so anything you distribute that uses it must make its source code available to your end users, and its DLLs may not be split out into other applications. When the machine's program must stay closed, the route is a licensed edition or server whose terms sell that right; the protocol behavior is the same, only the license differs.

Keep reading

  • How-to

    How to learn PLC, SCADA and DAQ from scratch

    Learn the three as one project, not three courses: pick one route, wire one real input, program a three-state sequence, exchange one Modbus register, bind SCADA tags and store a timestamped value. Pass the five checkpoint tests on the way; simulation alone never exercises terminals, polarity or reconnection.

    SCADA and data logging

  • Troubleshooting

    How a SCADA client reads a small PLC over EtherNet/IP

    An Ethernet link to a MicroLogix is not a working SCADA read: the controller serves data through PCCC commands carried over EtherNet/IP, and a client without that application service gets every read rejected. Check the driver's documented controller list for PCCC over EtherNet/IP or use the controller's documented Modbus TCP server, then prove one read-only value end to end.

    Industrial networks

  • How-to

    How to create a SCADA PLC communication alarm for link loss

    Create the SCADA-to-PLC communication alarm from the data server's diagnostic item, not from a PLC bit. Browse to IsPresent under the PLC shortcut's Diagnostic Items folder, create a digital alarm in FactoryTalk Alarms and Events with IsPresent = 0 as the active condition, and it activates about 20 seconds after the link drops; IsPresent(10) shortens that to about 10 seconds. A heartbeat bit cannot do this because it travels over the link it monitors and goes unreadable exactly when the link fails.

    SCADA and data logging