How to Configure Real-time Microsoft Excel READ Communication from Rockwell Controllers (DDE)
ฝัง
- เผยแพร่เมื่อ 23 ส.ค. 2024
- ▶ C'mon over to realpars.com where you can learn PLC programming faster and easier than you ever thought possible!
=============================
▶ Check out the full blog post over at
realpars.com/dde
=============================
Do you require monitoring, controlling or testing, using real-time manufacturing or process plant data from an Excel spreadsheet?
This data could be helpful to you as an automation professional, or your management, research and other department personnel requiring real-time data for decision making or accounting purposes.
With this video, we will show you how to achieve this and more using Microsoft Excel and communication called Dynamic Data Exchange or DDE, a method of Microsoft windows interprocess communication so that one program can communicate with or control another program.
In this lesson, we will be using Microsoft Excel, Rockwell Automation Studio 5000, RSLinx Classic, and Studio 5000 Logix Emulate software.
We will be using Studio 5000 Logix Designer programming software to create a sample program with floating-point and integers arrays, timers, and other simulated process values to display in Excel spreadsheets.
The RSLogix PLC or emulator will communicate with RSLinx Classic and in turn, RSLinx will communicate with Studio 5000 Logix Designer and Excel.
DDE is a Windows mechanism that enables applications to communicate with each other and automates the manual copying and pasting of data via the clipboard.
A DDE communication is known as a conversation and the application, which initiates the conversation, is the client.
The other (responding) application is known as the server.
Normally, the client initiates the conversation in order to get some data from the server. The client can send data to the server too, known as poking.
To initiate a DDE conversation, a client specifies the three items:
The Application: the name of the application it wants to talk to. Usually, this is the application’s executable filename, for example, RSLinx.
The Topic: which is the subject of the conversation created by the user and should be something which makes sense, for example, RealParsExcel.
The Item: Any number of different Items may be referred to identify data to be passed between the applications, for example, Excel recognizes cell references as items, Word recognizes bookmarks as items and RSLinx recognizes program tag names as items.
We will begin with the sample PLC program. We’ve created a Studio 5000 Logix Designer program called Realpars_Excel.
You can download this PLC program here:
bit.ly/DDE_Logix
We will set up the DDE configuration for Excel. You can download the Excel file here:
bit.ly/DDE_Excel
=============================
Missed our most recent videos? Watch them here:
realpars.com/fds/
realpars.com/a...
realpars.com/s...
=============================
To stay up to date with our last videos and more lessons, make sure to subscribe to this TH-cam channel:
goo.gl/Y6DRiN
=============================
TWEET THIS VIDEO ctt.ac/8LV39
=============================
Like us on Facebook: / therealpars
Follow us on Twitter: / realpars
Follow us on LinkedIn / realpars
Follow us on Instagram / realparsdotcom
#RealPars #DDE #Automation
You guys are awesome. Always posting informative, useful, easy-to-understand videos. I am a professional engineer and I am still learning from you guys.
Great to hear that! Happy learning.
Thanks for this video. Please make one with Siemens S7 or TIA
Hey!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
Outstanding technical video which improves our skills. A massive Thank you for your knowledge sharing
Thanks a lot for your kind compliment, Thales! We are always extremely happy to hear such positive feedback! If you ever have any questions, feel free to reach out to us. Happy learning!
Great video, thank you. Is it possible to get PLC tag to a PC application where RSLinx is not installed?
Hello, thanks for the guidelines. Just 2 questions:
What are the two arrays REAL[10] and DINT[10] created for?
Also, what are the variables L1 and C1 stands for in the Excel tab at 10:30?
L1 = block size (number of elements to read); C1 = use one column per row to display the data. L5 would be "read 5 elements" and C5 would be "use 5 columns to display the data".
The two arrays are not used in this example.
This video is very helpful. Please try to make one with Siemens TIA portal.
Thanks for your feedback!
Thanks for detailing this process. I have used DDE to read plc data and have created many interactive forms and checklists. Until now I have never used DDE to write to the PLC. I would like to see an example showing how to read and write to a User Data Type (UDT ). In my PLC I have a UDT named Part_To_Be_Configured. Inside the UDT I have 25 Tagnames which all are part of the configuration data for a "part tp be configured" and each of the Tagnames of various types Strings, INT, DINT, REAL are each arrays allowing up tp 300 elements. The "Item" would look something like this "part_to_be_configured.Part_Name[0]". the next UDT entry would be part_to_be_configured.Robot_01_Program[0]. I believe the process would be similar to reading and filling an array like Sample_array[25,300]. I am not certain how to handle the tagnames in the for next loops as opposed to numbers.
Hi Chris,
Thank you sharing your experience! I will happily go ahead and shared this with our content team, hopefully they are able to create a video with the example you suggested.
Thank you again for sharing!
Yup, I have been doing it for year's now. Full HMI & Reporting.
Its old tech and very in-expensive and you don't need to add any special software, lol.
Sir,
How to make report in excel of specific time duration?
That's awesome we are very grateful and waiting for training
Happy to hear that!
中文字幕有吗
Fantastic. Can you explain about the L1, C1 being used in the excel. Do we need to use this for specific data types or even for bool, real?
L1 = block size (number of elements to read); C1 = use one column per row to display the data. L5 would be "read 5 elements" and C5 would be "use 5 columns to display the data".
@@realpars Thank you. Please keep the videos coming.
Can we use the Studio emulate as a Virtual PLC for running various process and monitoring LT and operating motors?
Yes. Studio5000 Logix Emulate acts as a "virtual software" PLC. It does have limitations, so it may not be able to simulate everything you need to test, such as special communication interface cards or communication between multiple PLCs on different computers. Since Emulate does not support communication modules, there is no way to send a MSG between these two emulated controllers. In this case, you would need to use SoftLogix instead, which does support communication modules. (SoftLogix is also more expensive than Emulate).
Wow this is great! So the data displayed in excel is just realtime? Just wondering how one would configure this to perform datalogging or if datalogging would even be worth it. Curious to know your thoughts.
That isn't simple. There are entire suites of software dedicated to this.
For short-term data storage you might be able to write a separate macro in Excel to periodically "sample" the live values and place them in a separate tab or worksheet in incremental fashion.
THANKS FOR THE EFFORTS. could u explain the same application with siemens controller?
Hi Husam!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
We are developing a machine with StudioLogix 5000 and our end user would like to use excel to log the machine data in real time. Am I correct in assuming that we will use RSLinx Classic to configure the DDE before we deliver the system, and that they will only need to create their own Excel file to access the data, or do they also need RSLinx Classic to establish a connection? Thanks
Outstanding Content I Like your video...
Thank you!
Great! I love it
Great video, have you guys tried on connecting plc data with Google sheets instead of excel? If so please provide some information
Google Sheets cannot be used in the same manner as shown in this video. There is no embedded DDE functionality, no way to attached function add-ins to add DDE functionality, and no facility for running macros if an add-in could be added. Perhaps one day it will.
Hello Realpars I’m wondering what is the use of this procedure? Sorry I’m a beginner PLC programmer and my other question is this is only exclusive for studio 5000 software ? Or it’s also available for Siemens TIA portal?
The use of Excel and DDE to read data from a PLC is typically used by engineers to gather data for analysis or troubleshooting. There are better methods for doing this now and better protocols (OPC), but this method still works, is easy to configure, and provides data that can be used in Excel charts and graphs. In order to connect Excel to a PLC using DDE, the PLC communications interface needs to support DDE. RSLinx does (Rockwell), but I do not believe Siemens supports this method. Both Siemens and Rockwell PLCs support OPC, which is the preferred way to connect a PC to a PLC for data collection and analysis.
Thanks a lot, a very useful content. Just wandering if we can do it without the use of RSLinx. Is it possible to export data directly over Ethernet into Excel without using any software?
There are a few PLC's that have OPC UA embedded (or via an interface card) that allow for data to be exchanged without any intermediate software application, These CPU's have an OPC server built-in, with Excel acting as a client. For Siemens S7 and Rockwell Logix CPU's you need an OPC server (like RSLinx for Rockwell or OPC Scout for Siemens) that can connect to the CPU and act as a server. This will become easier in the future, but for now, using an intermediate software application is necessary.
Hi, thanks for the tutorial! I am just wondering whether wps spreadsheet would be able to do the same thing?
Excel uses an add-in for DDE and OPC support. I do not believe an add-in exists for WPS Spreadsheet.
Great content
Thank you, Dennis!
Hello. Your videos are great. Please also make a clip about scripting in hmi and scada systems. For example, in Siemens hmi panels and siemense scada (wincc scada). Thank you.
Hey Farzad!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
Great video. Can we connect the data with the VB.net forms?
Yes, but with VB.NET you would likely want to use OPC instead of DDE to communicate with the PLC to gather data, although a DDE connection can be made as shown in the video by using the proper VB methods.
@@realpars Thank you for the reply. Can you list any resources on this topic? Those will be very helpful.
Hi thank for video. But can you make a video to access data from Siemens PLC ?and collecting the data into excel by internet access
Hey!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
Is it possible to import data to Excel using another RSLinx? Thanks
Hello Nilson. Are you referring to RSLinx running on another server? There is only one install of RSLinx for each server. We cannot use the RSLinx Lite version by the way. Check out RSLinx Gateway, it's another license and costs a bit more. RSLinx Gateway features are: Multiple RSView32 clients accessing data through one RSLinx Gateway (remote OPC), and Remote Microsoft Office applications displaying plant floor data such as Excel.
Looooovveeee itttt thnx sooo much.
Happy learning!
how can you record the displayed data in Excel or make trend charts?
You would need to write a macro to read a data value and then copy the received data to another location, incrementing the row number every time a new data point is read. This would allow you to build out a list of the data received. I would also write the current time and date in a cell in an adjacent column so that you have a time reference for each data point. Once you have a data range identified for the values, creating a trend chart is done the same way as it is normally done. You can also write VB code to dynamically expand the range of the chart as new data is saved.
Thenks for all videos could you make another video about how to connect 2d laser scanner through ethercat sisteym !
Hey Oyatullo!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
@@realpars Hi
If I wanted to display the Accumulator value on an HMI panel (Say, a PanelView+1000) how would I go about doing that?
Simply create an HMI tag that points to Timer01.ACC.
Is it possible to write from Microsoft Excel to Rockwell Controllers using DDE? ie if we wanted to feed in data to simulate a plant
Yes. You will a licensed version of RSLinx (not RSLinx Lite) or another DDE server like Kepware. Just as in the video that demonstrated how to read, you can also write to the CPU. You will need to create a macro to open the DDE channel and then use the DDEPoke method to write to the PLC. There are plenty of examples on the internet showing how to do this.
Hello, can I know what software you are using in making your videos
Hi Ali,
I am not sure about this as this is done by our graphic and animation department.
Keep going
Will this work with RSLinx Classic Lite that comes with Studio 5000 or would you need the full versions
No, RSLinx Classic Lite does not have the DDE interface DLL's required. You will need one of the full-featured versions.
Hi! I have a problem to do this, when I try to paste de DDE link in the cell of Excel it doesn't show the "Paste link" in the Special paste window. I tried to type the formula too like you do, but it doesn't work either.
I'm using a virtual machine VMware where i have the Rockwell programs and my Excel program is in the Local machine
Hello Victor. DDE, provides a way of transferring data between client and server applications running on the same machine. YOu will need to install all programs on the virtual or local machine. Best of Luck, Wally
Hallo realpars team, can you make similar tutorial with mitsubishi plc? Thx
Hey!
Thanks for your comment and your suggestion. I will pass this on to our course developers!
Thanks for sharing and happy learning!
MOST EXLNT vid!
Thanks, Chris!
@@realpars Also, if I wanted to display the Accumulator value on an HMI panel (Say, a PanelView+1000) how would I go about doing that?
In the download .acd file, what is the purpose of the simulated I/O card in the Logix Emulator?
The I/O SIM Card is not required for this example.
Tnks
Can this configuration be done using AB micrologix 1100B plc as well??
Yes, but not in the same way. The Micrologix 1100 is programmed with RSLogix 500, so you will need it plus RSLogix 500 Emulate to replicate the exercise in the video. I have not tried to connect to a Micrologix 1100 with the 'free' version of the programming software (Logix Micro).
@@realpars Thank you for your response.
It possible to do this with S7-300 PLC?
Yes. And in almost the same way. Just as you need a DDE server with the A-B PLC's (Kepware or RSLinx), you will need a DDE server for S7 (Kepware, Matrikon, etc.). Each of these DDE servers requires a license to be purchased.
Hey hi, I have tried the exact your procedure, but i am getting error in Excel. It shows There's error in the formula. Kindly help it. thanks
Hi Seshasai,
Thanks for your comment!
Could you specify on the error you are receiving? That way our technical team might be able to help you out more efficiently.
Thank you!
got same error for me
error in Excel shows,
1. Make sure included all required parentheses and arguments.
2. Verify any reference to another sheet or workbook.
RealPars , I was using Rslinx classic lite, don't think so this works on lite, as you have used Rslinx classic gateway.
How to read data in table from ..Mans No. Of samples like 100 samples 😊
if i meet you someday i will buy you a drink, Jesuscrist you are excellent. Thnks
Thank you for the offer.