Rslinx to excel DDE Poke

[controlconcepts]

I know this has been covered many times and I have tried EVERY code snip-it that I have found to no avail...

First off I have data already setup in an excel sheet that I can receive. It is microsoft excel 2003... I am thinking that was the cause of the code I have tried from this site and others...

and Rslinx classic 2.57.00.14

All I need is some direction as how to set up a command button to toggle a bit and get and integer from a sheet and cell I would like to do it in VB not by a cell...

any other info needed just ask..

Thanks yet again,
Matt

 

[Ron Beaufort]

first things first ...

you said RSLinx Classic - but that's still not enough information ... look at the RSLinx title bar (top of the RSLinx window) ... does it happen to mention the word "Lite" up there? ... if it does then you need to understand that the Lite version of RSLinx doesn't do DDE ...

yes, there is usually a menu selection that makes you THINK that it will do DDE - but that's just a cruel prank ...

 

[controlconcepts]

Thanks for the quick response Ron. Not the lite version. I already have topics configured and am reading from mutiple controlers just can't get the DDEpoke or a write to the plc....
Thanks,
Matt

 

[Ron Beaufort]

it's been about 4 years ago since I tinkered around with VB and DDE ... here's a link to what I came up with ...

http://forums.mrplc.com/index.php?showtopic=14747&view=findpost&p=72355

I'm sure that there are other members who are more knowledgeable than I am when it comes to this type of stuff - but maybe the basic techniques in the material I've linked will give you something to play around with until the gurus come along ... note that I was using Kepware instead of RSLinx - but maybe the VB part of the puzzle will be helpful ... that's all I've got for you ...

 

[controlconcepts]

Ron you are the best again!!!! Quick Quick.. will have a look and a try
Thanks,
Matt

 

[controlconcepts]

I have even tried the RFM approach.. And the examples In Rslinx will not work!!

 

[Ron Beaufort]

you may already be past this stage – but if NOTHING is working I'd try this as a first step ...

set up a new DDE/OPC topic in RSLinx – and name it MATT ... aim the topic at your PLC (you haven't said that you have one – so I'm just assuming that you do) ...

now go into Excel and pick a cell – any cell ...

type in the following formula:

=RSLinx|MATT!'N7:0'

for clarity, that boils down to:

EQUALS (the equal character)
RSLinx (the name of the DDE application)
PIPE CHARACTER (straight line - the SHIFTED-BACKSLASH key)
MATT (the name of the DDE topic)
EXCLAMATION POINT (geeks call this a "bang")
SINGLE QUOTE (an apostrophe)
N7:0 (the address of a valid PLC integer location – use a COLON)
SINGLE QUOTE (another apostrophe)

if all is well, you should have a zero or some other number pop up in the cell ... changing the value of the integer in the PLC should change the cell's value ...

if something like #N/A pops up instead, then the link isn't working ...

I know that this isn't where you want to end up – but at least it might help to show that your communication path/cable, etc. is valid ...

 

[controlconcepts]

Thanks again Ron... Yes I have a multiple PLC setup that I have a log of events and temperature data already working. The problem is that I can not get the proper format to SEND data [ DDEPOKE ]to the PLC. And the log is setup on a touchscreen and only shows 10 events at a time. Until a button is pressed and you can look up history.... So my problem is I need the same Button In EXCEL to reference the history.... I can't get a write to the PLC so I am very stuck at this point... What I get when I try any of the sample code I get Excel Vb errors or no error and no change in the plc.... Any more thoughts would be much appreciated.... As I have been working on this for days and now really need help
Thanks,
Matt

 

[dcooper33]

Hi,
Are you using a DDEInitiate command to open the topic on the button-click code? Here's a template of how I've always used DDEPoke:

Private Sub MyButton_Click()
    rslinx = OpenRSLinx()
 
    DDEPoke rslinx, "N7:0", Cells(1, 1)
 
 
End Sub
 
Private Function OpenRSLinx()
    On Error Resume Next
 
    'Open the connection to RSLinx
    OpenRSLinx = DDEInitiate("RSLINX", "My_Topic")
 
    'Check if the connection was made
    If Err.Number <> 0 Then
        MsgBox "Error Connecting to topic", vbExclamation, "Error"
        OpenRSLinx = 0 'Return false if there was an error
    End If
 
End Function

For your application, just replace "My_Topic" with your topic name, and "N7:0" with the PLC address you are writing to, and "Cells(1, 1)" with the Cell in your worksheet that contains the value you are wanting to load in your PLC.

If this doesn't help, or you can't get it to work, post your vba code, and a screen-shot of your topic config in RSLinx would be helpful, and hopefully I or someone else can straighten it out for you.

Cheers,
Dustin

 

[Ron Beaufort]

I banged together a little demo that might help ... it's definitely down and dirty - but it works to do what I *THINK* you're trying to get accomplished ...

keep in mind that the cell being used in Excel should be named as in ROW-COLUMN fashion ... so R7C2 in my example is Row 7 – Column 2 – or in simpler terms cell B7 ...

also if I remember correctly (it's been years since I've really worked with this stuff) you need to SAVE the Excel file before you can count on the links to work ...

basically when I push the top two buttons, I can make the value of N7:0 in the PLC change between 1 and 2 ...

when I push the bottom button, I take whatever value happens to be stored in cell B7 and write that over into N7:0 ...

it ain't pretty – but all of the pieces work ... maybe the basic ideas will help you out ...

I'm sure Dustin can help you make it more elegant ... I'm just trying to show the nuts and bolts of how it COULD be done ...

.

 

[Ron Beaufort]

and here are some files that might help – if you can get them to open ... it's basically just the project that I used for the screen shot shown above - so don't waste any time if they won't open for you ...

this has been fun – but now I've got work to do ... good luck with your project ...

.

 

[OkiePC]

For your application, just replace "My_Topic" with your topic name,

Or better yet, make it a field on the spreadsheet, so the user can edit the DDE/OPC Topic name at runtime.

My generic sequencer makes use of a xls with some poking going on with just those features. It isn't polished yet to match my latest upload, and needs the addition of a read Cmd_button.

But it has working examples of DDE Poke, and you can see the VBA code I used.

Hi,
Are you using a DDEInitiate command to open the topic on the button-click code? Here's a template of how I've always used DDEPoke:

Private Sub MyButton_Click()
    rslinx = OpenRSLinx()
 
    DDEPoke rslinx, "N7:0", Cells(1, 1)
 
 
End Sub
 
Private Function OpenRSLinx()
    On Error Resume Next
 
    'Open the connection to RSLinx
    OpenRSLinx = DDEInitiate("RSLINX", "My_Topic")
 
    'Check if the connection was made
    If Err.Number <> 0 Then
        MsgBox "Error Connecting to topic", vbExclamation, "Error"
        OpenRSLinx = 0 'Return false if there was an error
    End If
 
End Function

It doesn't have the error checking like yours that would be useful if the DDE topic fails I promise to steal that little snippet for mine, thanks. In my file, you should look at the topic list in RSLinx and make sure it matches that field on the sheet before using the buttons. It also has a useful formula to go from binary to RSLogix5/500 INTs...

http://forums.mrplc.com/index.php?app=downloads&showfile=854

Paul

 

[controlconcepts]

Dustin And Ron!!! you are the best......

Many thanks for both you're efforts I have it POKING lol..
But I have a few questions about it...
Not going to post them yet AS I want to see (now with the proper format) if I can get the same results to just trigger a BIT instead of an integer. I have 3 buttons working as of now.....

So my hat is off to both of you as usual!!!!!
Thanks So much, Both of you
Matt

 

[dcooper33]

Dustin And Ron!!! you are the best......

Many thanks for both you're efforts I have it POKING lol..
But I have a few questions about it...
Not going to post them yet AS I want to see (now with the proper format) if I can get the same results to just trigger a BIT instead of an integer. I have 3 buttons working as of now.....

So my hat is off to both of you as usual!!!!!
Thanks So much, Both of you
Matt

Matt,
Glad to help, and glad to hear that you've got it working. You shouldn't have any problems "POKING" a bit either using the same method. However, if you are going to set a bit high then you can't just do DDEPoke rslinx, "B3:0/0", 1. You have to use a cell value that CONTAINS a one. I've learned that through experience. Seems stupid that you can't just directly code a "1" or "0" for a bit reference, but VBA is an object-oriented language...

Or better yet, make it a field on the spreadsheet, so the user can edit the DDE/OPC Topic name at runtime.

Good idea, Paul. I'll definitely be using that one next time I've got to do any DDE work.

It doesn't have the error checking like yours that would be useful if the DDE topic fails I promise to steal that little snippet for mine, thanks.

Hey feel free, brother. That piece of code is just copied from either a KB article or an excel forum, can't remember which, but I will see if I can dig up the link. I was just trying to illustrate for the OP the basic concepts involved in writing data into a PLC register using DDE/OPC on RSLinx. I would add that if writing into a PLC register from an Excel cell, adding some code to make sure the cell value is numeric, and fits within the bounds of signed 16-bit architecture is probably a good idea. Such as:

If IsNumeric(Range("A1")) Then
        If Range("A1") >= -32768 And Range("A1") <= 32767 Then
            DDEPoke rslinx, "N7:0", Range("A1")
        Else
            MsgBox "Error Cell Value must be >= -32768 and <= 32767", vbExclamation, "Error"
        End If
    Else
        MsgBox "Error attempting to write non-numeric value to PLC Integer Register", vbExclamation, "Error"
    End If

But it has working examples of DDE Poke, and you can see the VBA code I used.

Very nice work, Paul. I will definitely be keeping that guy in my toolbag. I really like how you totalized all the bit columns into an integer value for the write. Didn't know about the "ISBLANK" function, nice way to do an MVM in excel.
Might take a lot of work, but one thing I would suggest is to have some error checking on the "PLC Base Address" field. You'd want to make sure that the value is not only in valid PLC/SLC addressing format, but that it is an existing data table in the target PLC. Also you'd want to ensure that DataFile + DataLength <= the highest address in the data file.
I have no idea if all that information can be extracted from RSLinx into VBA, and then again it may be overkill, but it would be handy to have in there...

 

[OkiePC]

I really like how you totalized all the bit columns into an integer value for the write. Didn't know about the "ISBLANK" function, nice way to do an MVM in excel.

I remember doing it more elegantly at one time, but that formula always "smart copies" nicely.

Yes, having the input pattern completely filled with ones and zeroes is a brain pain, but I do that for display purposes in printed form. I can take credit for that visual format for input sequencing. Only the ones and zeros matter, leave everything else blank...so you can follow the sequence of the inputs in your head, reading only what matters for each step.

You needn't even worry about the MASK file being correct, but don't forget to send it! I need to have the send button on the "Inputs" tab write the masks afterward automatically.

Might take a lot of work, but one thing I would suggest is to have some error checking on the "PLC Base Address" field. You'd want to make sure that the value is not only in valid PLC/SLC addressing format, but that it is an existing data table in the target PLC. Also you'd want to ensure that DataFile + DataLength <= the highest address in the data file.
I have no idea if all that information can be extracted from RSLinx into VBA, and then again it may be overkill, but it would be handy to have in there...

I believe that an address error can be trapped in OPC system variable somewhere.

Just knowing that the DDEInitiate failed would be enough for me. I do need to name some fields/ranges, lock them.