Use an Excel macro to read and write PLC tags through DDE

What do you type in an Excel macro to read and write PLC tags?

Four VBA calls do the whole job. DDEInitiate opens a channel to the server and returns the channel number, DDERequest reads one item's value, DDEPoke sends a worksheet cell to an item, and DDETerminate closes the channel when the macro is done.

The VBA documentation that ships with Excel covers each call, including the argument forms used below: DDEInitiate opens a channel and returns the channel number, and every later DDE call carries that number; DDEPoke sends data as DDEPoke(Channel, Item, Data). A poke macro that sends cell (2, 2), which is cell B2, looks like this:

Sub PokeFromB2()
    Dim Data As Range      ' the cell to send
    Dim Channel As Long    ' the open channel number
    Set Data = Cells(2, 2) ' row 2, column 2, which is B2
    Channel = DDEInitiate("DSData", "Topic")
    DDEPoke Channel, "ItemV2000", Data
    DDETerminate Channel
End Sub

DSData, Topic and ItemV2000 are the sample's literal strings, and they are why copied macros fail. Your server has its own configured DDE application name, topic name and item path, and the macro must carry those three strings character for character. With an application or topic the server does not recognize, DDEInitiate fails and the macro stops with a run-time error.

The read side is DDERequest on the same channel:

Sub ReadOneTag()
    Dim Channel As Long    ' the open channel number
    Dim Result As Variant  ' what the server returns
    Channel = DDEInitiate("DSData", "Topic")
    Result = DDERequest(Channel, "ItemV2000")
    Cells(2, 3).Value = Result
    DDETerminate Channel
End Sub

Does an Excel DDE macro talk to an OPC server?

No: DDEInitiate, DDERequest and DDEPoke are DDE calls and make no OPC calls, so the macro reaches your tags only where the server also exposes a DDE interface, and the application, topic and item strings must match that interface's configuration exactly.

Check the server's own documentation before you write any VBA. A documented DDE interface comes with its application name, its topic names and its item path rules, and the printed values are what the macro carries. A server that exposes OPC only is out of reach for these calls, because VBA has no built-in OPC calls; the OPC route uses the server's documented client tooling or a client library. When DDE turns out to be the wrong interface, a Modbus client or an OPC server for a small HMI is the sturdier shape for the same read-and-write job.

What do you need before you run the macro?

Three strings and one harmless tag: the DDE application name, the topic name and the item path exactly as the server's DDE configuration prints them, plus a test tag you can change from the PLC side without touching the running process.

• The three strings, copied from the server's configuration or its client test tool. Item paths often build on the PLC address notation, so copy them exactly, prefix characters included.
• One test tag: a spare register, or a command tag the PLC logic validates. Nothing that drives a motor, valve or output.
• The PLC programming package open on that tag with the live monitor running, so the truth is visible from your desk.
• Excel with macros enabled and the Visual Basic editor open; its help carries the DDE method topics.

How do you test a read with DDERequest before any write?

Change one known tag at the PLC, read it into a cell with DDERequest, and confirm the cell follows. A link that cannot show you 1234 in raw register counts when the register holds 1234 is not ready to carry a write.

1. Put the read macro in a module and replace the three sample strings with your configured ones.
2. Set the test tag at the PLC to a value you chose, so the expected result is known.
3. Run the macro; the result cell, (2, 3) in the sample, shows what the server returned.
4. Change the tag at the PLC and run the macro again; the cell must show the new value.
5. Run a copy with one deliberately wrong character in the topic string; DDEInitiate fails with a trappable run-time error, and that error is the cue to add an error handler before trusting the link.

If you want the sheet to follow the tag over time, schedule the read macro on a timer. A 2-second interval suits a setpoint panel; a 30-second interval suits a slow log. Each run reuses the one open channel until your code closes it.

How do you poke a cell value into a PLC tag safely?

Send the cell with DDEPoke to a command tag that PLC logic validates, and keep the range checks, permissives and output coils in the PLC program, because Excel is an office client that can pause, crash or send a wrong value at any moment.

Safety note: a worksheet cell is never the last check before motion. PLC logic compares every command tag against the machine state and the accepted range and refuses what does not belong, so a typo in the cell costs a refused write, not a movement.

1. Copy the poke macro and point the item string at the command tag; keep the application and topic strings as configured.
2. Put a harmless value inside the command range in cell B2.
3. Run the macro and watch the command tag in the PLC's live monitor.
4. Let PLC logic act on the tag only once the monitor shows the sent value, then add further tags one at a time.

That is the same order as an operator panel PLC link setup that tests writes before motion: prove the path on a tag that cannot actuate anything, then widen it. For a complete worked listing, a DDE macro procedure guide for writing Excel values to a PLC shows the same calls in a full write procedure.

A worked example of the full path

One test tag carries this example, and the values are illustrative. Set 1234 in raw register counts at the PLC, read 1234 into the result cell, poke 1236 to a command tag, and the live monitor in the programming software shows 1236. The tags are a spare register and a validated command tag; the strings stay the sample's.

• At the PLC: write 1234 into the spare register. The live monitor reads 1234.
• The read: run the read macro. Cell (2, 3) shows 1234, and 1234 read against 1234 set means the path from Excel through the server to the PLC and back agrees.
• The change: set the register to 1235 at the PLC and run the read again. The cell shows 1235, so the link follows changes instead of freezing one value.
• The write: type 1236 in cell B2 and run the poke. The live monitor shows 1236 on the command tag, so 1236 sent matched 1236 received.
• The reject path: type 5000. Say this machine accepts setpoints from 0 to 1000 in machine units. PLC logic compares, finds 5000 above 1000, refuses the command, and the machine holds its last valid setpoint. Excel never needs to know.

How do you confirm the PLC really received the value?

Watch the tag in the programming package's live monitor and compare it with the worksheet cell. A macro that ran without an error message is not proof that the server mapped the item, reached the PLC and changed the value.

Check the boundaries in order: the worksheet cell, which holds what you believe you sent; the item path, which the server's client test tool reads directly if it ships one; the PLC connection, which the live monitor shows; and the PLC logic, which decides whether the command tag drives anything at all. Where a call fails tells you which string is wrong. A failure at DDEInitiate points at the application or topic; a failure at DDERequest on an open channel points at the item path. The reject path gets tested deliberately: a value outside the accepted range must end in a refused command, with the machine holding its last valid state.

When is Excel the wrong tool for this job?

Excel suits an office-side panel for reads, logs and the occasional setpoint. It is the wrong place for floor operators, for anything the line must react to quickly, and for safety, which stays in PLC logic and in hardware limits.

Put floor-facing values on an HMI or operator panel, and if the office needs the same tags, reading PLC tag values from an HMI panel web server serves them to a browser with no spreadsheet in the path. A wall display of process values for the crew is a read-only job on purpose; how to specify a read-only process display for a plant covers the brief for it. If the floor also wants a notice screen for shift targets, quality counts or downtime reasons, shop-floor signage players play images and video from a microSD card or USB stick. They are notice screens and no live tag link, so the Excel connection keeps doing the live work.

Recommendation: build the link read-first, one tag at a time: take the application, topic and item strings from the server's DDE configuration, prove DDERequest on a spare tag you can change at the PLC, and only then DDEPoke to a command tag the PLC validates, with range checks and interlocks in PLC logic. If the server exposes OPC only, skip DDE and use its documented OPC client route.

Questions