Using Excel and DDE to collect data

I was wondering if anyone could me started on using Excel and DDE to collect data, plot it, and then archive some of the data. I know this is a broad subject and I will be getting some specifications at a later point but I am new to this application of Excel and am trying to shorten the learning curve. Is it necessary to purchase a third-party product to do these things with Excel or can it just be done with VBA and/or DDE scripting?

Thank you!

This is assuming that the 'data' is coming from some PLC system (else why post at this site?) Each PLC system has their method of communication (protocol) using various media (RS-232, Ethernet etc). There must be some program on the PC which knows how to 'speak' to the PLC using its protocol and media.

This program can be supplied by the PLC maker (as in the advanced levels of RSLinx for Allen-Bradley) or by a 'third party'. This program must be DDE capable (DDE being an older method of communication between Windows programs). Once it is in place and connected appropriately to the PLC system then Excel can make DDE calls to this program and get the needed information.

So, once you know the PLC system and the method of communication you can staart looking for this in-between program. I hope this little bit of information helps.

Yes, this will be a system in which an Allen Bradley plc is sending data to a pc. So in that way I'm sure that RSLinx will be the I/O server. I am mainly wondering what to do from the Excel side of things in terms of pointing it at the data that is coming in real-time.

If you are using DDE then the Excel script itself will set the frequency of data aquisition (and won't be incredibly fast, DDE is not known for its speed). RSLinx will just react to each request, get the data, and send it back to Excel. Your script will have to send the information to various cells and/or to files as appropriate. I can't help you with that part though.

Thank you! I will continue to search for examples of such scripts with Google. At this point I do know that someone else will write the plc code to send the necessary data out to the pc so I will be working with the data once it is recieved.

Maybe this is just a misunderstanding in terms but normally the PLC ladder program will only make the data available within its registers. The ladder program will not actively SEND data anywhere. The PLC's operating system will respond to requests from the DDE program (RSLinx) with information. This is totally transparent to the ladder program.

In a more interactive system the Excel program, by way of the DDE interface, could write information into the PLC which the ladder program then reacts to in triggering data collection routines. Is this possibly what you mean?

bernie_carlton said:

Maybe this is just a misunderstanding in terms but normally the PLC ladder program will only make the data available within its registers. The ladder program will not actively SEND data anywhere. The PLC's operating system will respond to requests from the DDE program (RSLinx) with information. This is totally transparent to the ladder program.

In a more interactive system the Excel program, by way of the DDE interface, could write information into the PLC which the ladder program then reacts to in triggering data collection routines. Is this possibly what you mean?

Click to expand...

Yes...what I mean is that the application on the pc will be responsible for initiating the request for data. Since RSLinx handles this then it seems like I need to find out how to "talk" to RSLinx from Excel. Apparently this involves a great deal of overhead so perhaps I should look into using RSSql. The real goal here is to just be able to plot real-time data and store it. The storing part suggests using a "real database", but the plotting of the data suggested to me the use of Excel.

dde data aquisition

if you go to your project tree there should be a branch labled DATA Memory if you right click onit it will come up "new data memory table" click on this and give it a significant name and in the boxes enter the word data required. you will now have a new branch. right click on this and click on "from plc" a box will appear on the screen that will say data transfer succsesfull click ok. then right click on yuor data memory table again and click on "edit data memory" when this box is open you will need to choose decimal and then click copy grid. when you have done this open a new page in excel and press contol V and it will then open all the data from the plc that you have specified in your data table. hope this is of some use to you. from shaun

Another thing to care about after getting data using RSlinx succsessfully, which is the DDE addressing.

DDE addressing based on defining three main terms:

1- Application: the name of the DDE server where the data resides. (The name of the exe file of the server)

2- Topic: the name of the group of data on the DDE Server.

3- Item: the individual piece of data to transfer. (a bit, a word .. etc) in your PLC memory registers.

Iam sure that you can find many example in the web, RSlinx manual and even in MS-Excel help.

There's a very good help file in RSLinx that explains exactly how to do this with examples (RSLinx Gateway/Pro). Go to help then the search tab and search for "Excel".

Paul C

home-rolled or store-bought

I've been using rslinx, dde and excell for a while now and it works well enough. If I were more competent with VB I would probably stay with the home-rolled approach. As it is I am shifting over to Webdock for the improved archival capabilities that come with using M$Access.

Just my $0.02

Thomas

Using DDE in Excel requires only rudimentary programming skills.

Here is a code snippet that will read 100 Integers.
Tie this sub to a button. When you click the button run this sub and read data.

Sub read_data()



Icomchan = DDEInitiate("RSLinx", "Coating") 'open dde link



Data = DDERequest(Icomchan, "N26:10,L100,C1") 'get data and store in data variable

Range("calc_sheet!A10:A109").Formula = Data 'Paste data into selected range





DDETerminate (Icomchan) 'close dde link





End Sub



calc_sheet is a name of a worksheet.
Create topic called "Coating" in the RSLinx.
RSLinx lite will not do! You need the full blown version.

DDE Quick and Dirty

Use the following steps. Applications needed are Excel and RSLinx Pro. [rslinx lite with key disk]. Plan what the spreadsheet will look like. All one column or two or three columns. Get your numbers from one PLC. Reserve a consecutive block of registers. [data files, n12:0 to n12:49]. Start RSlinx click on DDE/OPC and create a TOPIC. Enter a name, any name. On the right side there are three tabs, Fill these out, they are basic communication parameters. The PLC port number you are using, the driver, DH+ or Ethernet, etc. When done click on done. You are now back to the regular RSLinx screen. Click on edit and click on copy link to clipboard. Minimize Linx, start excel, select a cell, click on edit and paste special, link, In this menu, you select the Data file, in my example n12:0, and block size. Say you want 2 columns of 24 numbers, then this block is 24. Go now to the cell that is the top of your next column. Click again edit, paste special, etc etc... block size is again 24. After completing this the numbers should be there. I use a macro to save the file as an HTM file. I have a small VB script that executes this save and updates the links every min. This is a cheap HMI. I do this on a server on the buisness network, and anyone with IE can open and see the spreadsheet like a web page. This created a lot of unwanted work here. They now want this saved to an Access database at the end of shift. AAAARRRGGGG!!! The more you do, the more they want. Bruce

Bruce's instructions are very good. One other thing I do with my PLC spreadsheets, is to bring in the raw data on a protected sheet. The main sheet then contains references that point to this "Raw Data" sheet. The raw data should keep the PLC addresses in order for comm. efficiency (now I think RSLinx will sort them for you anyway). The main sheet can then present the data in whatever format is desired without disturbing the structure of the communication.