In the "Creating and Using DDE Links in Excel" section, we will draw upon the CX Server DDE Manager's capabilities to link with an Omron PLC—a type of Programmable Logic Controller. This connection will permit the retrieval of data onto an Excel spreadsheet, where it can be transformed into a visual representation. Particularly, we will establish links on an Excel spreadsheet connected to two different PLC models—CQM1H and CP1L. The showcased data will focus on the first ten memory areas, a term which will be elucidated later on. This data will be shown as a comparative bar graph. Remember, this guide simplifies the process of creating links and converting the raw data into a user-friendly bar graph in Excel.
What is DDE?
Dynamic Data Exchange (DDE) is a protocol that allows for active data exchange and application control within Microsoft Windows. For its relevance, the DDE manager becomes an important tool. With it, you can set up all the necessary data exchange connections, often referred to as 'links'.
This guide will help you understand how to use the DDE Manager effectively. From launching the application to setting up programming points and even integrating your data with Excel, we'll walk you through the entire process.
Starting the DDE Manager
Start the DDE Manager by clicking Start/Programs/Omron/CX-Server/DDE Manager.
This will start the DDE Manager and place an icon on your toolbar at the bottom of your screen. The application will run minimized.
Right-click on the minimized DDE Manager icon and select Project/New.
Assign a filename (ACC DDE.cdm) to the CX-Server project that you will create and save it.
The create project will minimize the taskbar—Right-click the minimized DDE Manager icon and select Project/Edit.
This will call up the DDE Manager Project Editor. We can now add the PLCs (Devices) and Points we want to display.
Select File/New/PLC, or hit the PLC Icon on the taskbar.
Under Change PLC, we can now enter the information about how we communicate with the programmable logic controller.
Device Name: – Name in which the DDE Manager will call the PLC
Device Type: – The model of PLC that will be connected
Network Type: – This is the communication method we will use with the device.
In our example, we will use the following two Omron PLCs.
- CQM1H – SYSMAC WAY (Omron Host Link Protocol) Communication Port 11, 9600, E, 7, 2. Unit #00
- CP1L – USB
Once we have the PLC information, we can add points containing the information we wish to retrieve from the units. Select File/New/Point or the point editor icon to start the point editor.
On the Logical tab, you can give a name (Or Change) to the point in the PLC to get. Select the Physical tab.
On the physical tab, we set the following information:
PLC: – This is the list of PLCs entered in the previous step
Data Location: – The memory location that we want to read from
Internal Data Type: – This is the interpretation of the data we read. (Example: Bit, Word, BCD, etc.)
Command Modifier: – This is used if we need to Force Set / Reset the data location. This can be used to override the PLC program. Use with caution.
Press OK when finished setting the address.
Our point is now programmed. You will notice the symbols before the name of the point. This tells us at a glance what type of data we are looking at. Please refer to the CX Server Runtime manual for all symbol meanings.
Please fill out the remaining points for our example.
CP1L – D0 to D9
CQM1H – DM0 to DM9
Start Microsoft Excel
Copy DDE Links to Excel
Go back to the DDE manager and the points under the Project Editor. We can now select the first ten items on the list. Click the first one and then move to the last item. Hold the shift while clicking the last item will select all of them.
Hit the DDE Link icon.
In Excel, we now paste the information onto the worksheet. (Ctrl + V) Right-click the cell and select paste.
The information will now be displayed and updated on your worksheet.
The update time will depend on the interval set in the DDE Manager. Right-click on the minimized DDE Manager icon and select Update Interval…
We can also set individual point update times in the DDE link item. The default update time is established via the DDE Manager, but we can also set the time in the individual points on the spreadsheet.
DDE Update Default Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0′
5 Second Update Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0,5′
In the Project Editor of the DDE Manager, you can see the first ten memory areas are being used. This is the yellow triangle with the lightning bolt symbol. (You cannot delete the point if it is in use.)
Calling up an excel file with the links embedded will give you a Security Alert – Links warning. Select ‘Enable the content’ and then hit OK.
Here is an example of the finished excel file. We have ten memory locations from each PLC listed. We have inserted a graph that will dynamically change when the values are read.
Download the excel file here.
In this article, we've walked through the process of setting up and employing the CX Server DDE Manager. You'll find it simple to adapt to this tool, designed to cater to your needs effectively. It offers capabilities like managing your PLCs, programming data points and smoothly transferring data to Excel. It's noteworthy that implementing CX Server DDE Manager is rather straightforward. So, with the CX Server DDE Manager, navigating and controlling your data turns hassle-free and efficient.
Additional Information:
Excel – Conditional Movement of Data
Watch on YouTube: Implementing the Omron CX Server DDE and Excel
If you have any questions or need further information, please get in touch with me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLCs are not challenging to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimals, Hexadecimal, ASCII, and Floating Points.
To get this free article, subscribe to my free email newsletter.
Use the information to inform other people how numbering systems work. Sign up now.
The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.
Hi Garry, how to make auto record data from DDE. I want to record data from DDE every time data is change. And move to next sheet if day is change..
Hi Fastabiq,
https://accautomation.ca/excel-conditional-movement-of-data/
The above link should help you out. You could add addtional code to compare the dates.
Regards,
Garry
Hi Garry
When i copied DDE link from DDE manager and paste in excel sheet, I got the error and appears as # Bad Project in the pasted cell instead of getting the PLC values.
Hi Abdul,
I have not seen this error before. Try to ensure that the cell you are pasting the information into is not formatted in another way. Example Time/Date. I would start with a new spreadsheet and try it again.
The other thought that I have is when you paste exclude the formatting. Example Paste text only.
I hope this helps you out.
Regards,
Garry
Hi Garry,
I can´t find the CX-Server/DDE Manager in my pc, and can’t also add additional controls in VBA forms, even after register NETComm.ocx successfully…
I´m running the cx-one v4.4 – cx-programmer 9.61. I have re-installed the software but it still the same..
Do you have any idea why this is happening?
Thank you.
Pedro Macedo
Hi Pedro,
Normally this is installed with CX-Programmer.
Start -> Programs -> Omron -> CX-Server -> DDE manager (The path may be different depend on your installation directory)
http://www.plctalk.net/qanda/showthread.php?t=117891
This may help you out.
Regards,
Garry
Hi Garry,
I encounter the same issue as Pedro.
CX-One 4.51 / Win10 x64.
May DDE Manager discontinued by Omron?
Which release of CX-one do you use?
Thank you.
K
Hi Krisztian Lamar,
You may be correct. Omron may have discontinued the DDE server on the CX-ONE.
I would contact Omron direct to enquire.
http://www.plctalk.net/qanda/showthread.php?t=117891
Check out other methods to retrieve data.
I would recommend using AdancedHMI.
https://accautomation.ca/omron-cp1h-advancedhmi-communication/
This is a free download, written in VB.Net, open-sourced, and runs on visual studio.
Regards,
Garry
https://accautomation.ca/series/omron-plc-cp1h-series/