DDE and excel


Thread Starter


I have a mitsubishi PLC and we a using the MELLDDE driver for it. This allows excel to retreive data from the plc just like any DDE application (i think). We have a test that our plc controls and we use excel to monitor the test process. Is there a way to have excel log data every minute and move the prevous data to down to the next row and fill the top row with the most recient data and so on? this way i would end up with a log of what happened every minute. This would be my poor mans version of a scada system!
I have seen similar applications in waste water facilities. Basically, it involves writing either macros or VBA code. Should be fairly straightforward. Main part of code would be a loop driven from a timer (form timer?) that would execute once a minute or whatever your sample time. It would insert a row at the top of the table (far faster than moving data down) and then paste in the DDE formulas to get the DDE items from the PLC. I believe pasting the formulas in the cells would trigger them to execute, fetching the values. Good luck!
Yes it can be done. It's fairly straightforward if you have some VBA skills. All the DDE functions are described with example code in the VBA on-line help. Unfortunately, Excel doesn't load the on-line help for VBA by default. I've written a similar program using Excel that gets data out of a Modicon PLC (through Wonderware's IO Server) at midnight each day, calculates the difference (to get a daily total) and moves the result into the next column. On Monday, it starts a new report. The only trick is to get it to run each minute. If this is the only thing running on this PC, using the Timer function might work. I have a Wonderware InTouch Event run the Excel script each night. But since mine only runs once a day, the overhead of starting and stopping Excel is minimal. Feel free to e-mail me off-list if you want me to send you the script I run for ideas. Andrew Ward [email protected]
I think I have some idea how you can do this. Create cell "NOW()" in worksheet, use CONCATNATE to fetch for seconds string and assign a macro.IF the cells Value ="00.0", then COPY the range of Values which you had transferred from PLC .Change the worksheet ,INSERT a new row PASTE .SAVE the the values and RETURN to the original worksheet. You should use EXCELL VISUAL BASIC functions of recording macros.First, do all the functions manually; the EXCEL VB recorder will create the macro most of which you can use to run it later automatically. I am not a VB wizard, but I could read the help files and I am able to create macro for AUTO-OPEN functions and achieve these types of requirements. I hope this should give you some hints. Kamath RL [email protected]

Johan Bengtsson

Sounds correct except for one tiny detail, I think the value is what should be pasted, not the formula. Reason: at the end every cell in the table would contain the same formula, getting the value from the same place (the DDE-link), contstantly updating. Have the formula in one cell at the top, insert a row below that one
copy the formula-cell and paste it as value in the new empty cell.

(I have not tried this, but it works in theory)

/Johan Bengtsson

P&L, Innovation in training
Box 252, S-281 23 H{ssleholm SWEDEN
Tel: +46 451 49 460, Fax: +46 451 89 833
E-mail: [email protected]
Internet: http://www.pol.se/
There's always another way, right? Set your sampling time in MELDDE to 1 minute, then use the SetLinkOnData method to run the macro that you use to FIFO the data. This method runs your specified macro everytime the DDE link is updated. Handy! VB Help has an example you can use. The SetLinkOnData method is a member of the Excel.Workbook class. Just do a search for SetLinkOnData.

Bob Pace
[email protected]

Glenn Sahlin

All the above are great suggestions, however, having done similair things in the past, I would suggest kickin this up a notch. The problem your gonna run into is that applications written in vb are event driven. The VB scripting support that comes with Excel is somewhat limited. It will not support timer based events, so the problem becomes what is gonna start the dde exchange between your plc and Excel??? Granted there are clever ways to get aound this, but a better solution might be to pick up a full version of Microsoft VB. You can write your own application that will give you much better control over the data transfer.

[email protected]

Calta Computer Systems Limited

Event driven processing in Excel is not as limited as you suggest. The Excel visual basic supports an "ontime" method which can be used to delay events or schedule events at regular intervals such as once per day. There is an explanation and example in the Excel vb help. e-mail me directly if you require more information.

Lorne R. Schneider
We need to use excel to monitor the test process recorded on a HP Data acquisition system. This Data acquisition tool is connected to our PC with a RS232. Where could i find EXCEL vba code to read the serial port? Regards