Nett DDE in VB

Jezz

Member
Join Date
Dec 2002
Location
Kendal
Posts
399
Just a quick question I've got a excell sheet pulling live date off a powder blending plant,what I'm wanting to do is log this data in diffrent cells eg:

A1 200.00 kg
A2 199.98 kg

Where A1 Was the value taken at say 2:30 pm and A2 was the value taken 30 seconds later?

Can some one please tell me if this is possible and if so point me in the right direction , it's a while since I've do any VB stuff so I'm trying to remember how it all works..

Tanks Jezz

banghead
 
Jezz

Firstly I suppose you have been able to get the values through DDE into an Excel Cell.

You will need to write a small macro or VB event program to take care of this.

I have enclosed a Spread sheet with the program in Module1, Barebones program....... hope this helps.
Cheers
 
Excel DDE

I've had a problem trying to get some macros written in xlm 4.0 to work correctly in excel 2000. I've worked on this off and on for several months now and am starting to resign myself to the the fact they either won't ever work or maybe I should try converting them to a VBA macro (which I have no experience in). Now of course Micro$oft stopped using the xlm macro langauge in excel 4.0 but everything is supposed to be backward compatible. Anway all the macros work fine except the ones which are using DDE.

Here's the situation: I'm trying get data out of Modicon PLC's into Excel spreadsheets. I'm connected with Wonderware Modbus Plus I/O server. When I step through the macro's sometimes I can get 2 or 3 values before it hangs up and sometimes I can't even get the first value. When I try a hot link directly to a spreadsheet cell (eg. =MBPLUS|DATACONC1!"40184" where MBPLUS is the application, DATACONC1 is the topic and 40184 is the item), it seems to work fine although I haven't tried the 80 or so links I'll need for each spreadsheet. The macros which don't work are written like this:

RequestMacro - Ctrl W
=ERROR(FALSE)
=APP.MOVE(0,768)
Channel=INITIATE("MBPLUS","DATACONC1")
L6TStamp=REQUEST(Channel,"40184")
L6GoodBHi=REQUEST(Channel,"40171")
L6GoodLo=REQUEST(Channel,"40172")
etc...
etc...
=TERMINATE(Channel)
=RETURN()

When I step through this macro I either get !VALUE error or most of the time !REF error for the register values. I've tried different commands like DDEInitiate and DDERequest but it doesn't help. I'm just not sure what's wrong. I know it opens a channel to MBPLUS but I can't seem to get the data. The fact that a hot link works makes me think I still have a chance to make this work. The server is supposed to support hot links and cold links.

After reading venkat2k's reply I thought maybe someone would have a suggestion on this one. Thanks
 
tomneth:

May I suggest you try your Excel macro code with a different DDE server?

Download KEPServer from www.kepware.com. This is a very popular OPC/DDE server capable of communicating with lots of PLCs, controllers etc. - including several flavors of Modbus of course. The demo version is fully functional, the only limitation being that it will shut communications down after 2 hours. It can be restarted immediately afterwards though.

The help for KEPServer is quite exhaustive and aplenty. The fact of the matter is, you may like it more than Wonderware's one. At least you will know where your problem lies...
 
tomneth:

First you need to establish if the DDE Link to your Excel Application is running without any Comms issues.

To verify that, I would put a paste a link to a cell in Excel 2000 where you have your macros running, pointing to a 5 sec timer for example.

Create a timer and one bit which has an ON and Off state of 5 secs.

I will monitor this like a heartbeat and if there is no change of state withing 3 heartbeats (you make the choice how critical this is going to be) you popup a message box or flag a cell RED "Comms Error"

Once you know this works fine and there is no loss of Comms now you have your DDE working AOK with the Modicon ....... The problem could be here like LADDERLOGIC suggested.... this should enable you to verify first hand.

Rewriting the Macros in VB is not that difficult.... changing over to VBA instead of trying to run in XLM is the preferred method.


Hope this helps.
 
Thanks

Thanks for the replies LadderLogic and venkat2k. Actually I have set up a link directly in a cell with a one second clock timer that works fine. That's the part that makes me think there is something wrong with the macros. The difference is directly in the cell is a hot DDE link and the macros are cold (one-time) DDE links. The MBPLUS I/O server from Wonderware states it can handle hot and cold links but not warm DDE links. It also uses its own SuiteLink protocol to communicate with InTouch. I looked on the KEPware website and I think I might try their server. Thanks again for the replies.
 
no cold DDE

đź“š

Had a chance to work on this again today, starting to think the server IS the problem. I copied a small VBA program and modified it for one of our PLCs'. The three registers I'm grabbing contain the hour, minute and second. The program is:

Sub RequestData()
Dim ChannelNumber
Dim Data1
Dim Data2
Dim Data3

ChannelNumber = Application.DDEInitiate("MBPLUS", "DATACONC1")
Data1 = Application.DDERequest(ChannelNumber, "41915")
Data2 = Application.DDERequest(ChannelNumber, "41916")
Data3 = Application.DDERequest(ChannelNumber, "41917")
Cells(2, 1).Value = Data1
Cells(2, 2).Value = Data2
Cells(2, 3).Value = Data3
Application.DDETerminate ChannelNumber
End Sub

When I run this it gets hung up on the Data1 line and if I wait long enough will get an error in the spreadsheet cells. The I/O server connects but just won't grab the data (item). When I use a Hot Link directly in the cells it works fine and I can see the values changing:

=MBPLUS|DATACONC1!'41915'
=MBPLUS|DATACONC1!'41916'
=MBPLUS|DATACONC1!'41917'

:confused:
Now here's the best part, when I leave the spreadsheet with the hot links open the VBA macro works perfectly. I think that's what was happening earlier when I stated that I could sometimes get a few values. So before I try a different I/O server does anyone see anything wrong with the VBA macro?
 
tomneth:

Your code looks ok, and looking at what you just said, just leave one Hotlink in a cell in your spreadsheet.
This keeps the NETDDE going......

If you wanted your macros to work.......ie the XLM ones this could be a way to do it. The data traffic is so small but continous to the Hotlink, that other initiates are almost transparent .......
 
I've bumped into something similiar within the Excel VBA. I think there is a problem within Excel VBA parsing variant data. The DDE call returns a variant value. Which is what you have defined i.e. you didn't define its type so it is a variant type. What worked for me was converting the returned value to a string value then parsing to the appropriate cell. i.e.
Sub RequestData()
Dim ChannelNumber
Dim Data1
Dim Data2
Dim Data3
Data1$ as string, Data2$ as string, Data3$ as string

ChannelNumber = Application.DDEInitiate("MBPLUS", "DATACONC1")
Data1 = Application.DDERequest(ChannelNumber, "41915")
Data1$=Data1
Data2 = Application.DDERequest(ChannelNumber, "41916")
Data2$=Data2
Data3 = Application.DDERequest(ChannelNumber, "41917")
Data3$=Data3
Cells(2, 1).Value = Data1$
Cells(2, 2).Value = Data2$
Cells(2, 3).Value = Data3$
Application.DDETerminate ChannelNumber
End Sub
banghead
 
🔨
Thank you for the replies vankat2k and msdooley. I have tried leaving a hotlink open and then running the .xlm macros but in that case the only values returned were the ones where the hotlink was opened. It got hung up on the rest of the variables and returned errors. I have not tried it with VBA yet but I'll give it a try tomorrow. msdooley, I was sort of thinking along the same lines but didn't think about trying strings. I did try As Variant, As Integer and As Long. The data being polled from the PLC registers are 16 bit binary. I will try your suggestion tomorrow morning. Thanks!
 
Good Luck.
Another thought--Have you considered abandoning the macro and simply putting the DDE calls in their appropriate cells?
 
🔨

No luck with trying different data types... it's looking more and more like an I/O server problem. Either that or maybe some files are corrupt. I'm off until Monday (y) then I think I'll try a different I/O server, a different version of MBPLUS if available and Kepware if not. Thanks for the help, I'll get this eventually.
 

Similar Topics

Hey, I am looking for a program that can convert a text to speech phrase into a .wav file. The .wav will then be used for a Barnett Link callout...
Replies
2
Views
1,815
Calling all the AB gurus. I am doing my first large AB project. I am using RSLogix 5000, but only have ladder logic. I am getting the hang of...
Replies
2
Views
2,124
I need to support some old data collection that is running on Excel, but I need to get it running on LibreOffice. The following statement works...
Replies
0
Views
58
Hey Friends. I am currently trying to write approximately 700 tags from FtView13 to an Logix Emulate 5000. I am using rslinx DDE/OPC to create an...
Replies
0
Views
380
I am upgrading an old RSView32 v7.60 HMI to FactoryTalk View SE version 12.0 and I have an issue. There are some excel reports generated daily...
Replies
2
Views
981
Back
Top Bottom