Does anyone know how make a dynamic remote reference in Excel without having to do a macro?

For example, I have the fixed remote reference "=RSLINX|TOPICNAME!'N7:0'" in a cell. To see a different register I have to edit the reference.

What I would like to do is build an excel formula where the 'N7:0' part is the contents of a cell, and I can simply copy and paste the function down a whole column of register references. This way I won't need to bother with messy excel macros. I have tried and the normal excel cell referencing rules don't seem to apply to DDE items.

The other thing is how to poke values from excel to rslinx without macros. I have looked at the examples in the RSLinx help and only want to consider macros as a last resort.

Anthony Kerstens P.Eng.

 

I think you can do this with RSView32 if I recall correctly.

 

Writing a value out of Excel using DDE requires a "macro." You must open and close DDE connections before sending data and the only way to do it is a macro. There are good examples included with InTouch Wonderware. I had to figure it out from scratch and it only took a few hours. As I recall the only trick is that due to a limitation in VBA (Visual Basic for Applications) you must send data using a reference rather than hard-coding the data in the
macro. To test I tried to put the value in the macro and didn't get it working until I did some more reading. If you follow the example
carefully, you should be able to get it running.

Darold Woodward PE
SEL Inc.
[email protected]

 

Anthony wrote:
<<the normal excel cell referencing rules don't seem to
apply to DDE items.>>

We have the same thing hapenning with other DDE drivers... the way we did it is that we write some code to move the desire reg. value in the register we read. We write trough an index and the index value comes from a cell in Xls.

Ex: The reg. we read is # 1000 + (index value), so to read reg 2500 we poke 1500 in the index value register...

Not Kosher but it works when we need to read fast some values... In other situation, when the update rate is not important we simply write an array in Xls and have 3000 registers shown on the page... update time is around 5 seconds.

Hope this helps

Pierre
[email protected]

 

I forgot the first question you asked about the "referencing." The reference of "N7:0" is the memory reference inside the AB PLC, not a
spreadsheet value. So, you can copy and edit each one to collect the proper data or you can use concatenation functions to build up the
references and do a final paste to get them working.

Darold Woodward PE
SEL Inc.
[email protected]

 

> Does anyone know how make a dynamic remote reference in Excel
> without having to do a macro?

First let me state for the record "UGGHH! DDE". Having said that..
You can't do dynamic referencing in Excel at the cell formula level. I attempted this a couple years ago and I was told by Microsoft that this
cannot be done. Your only hope is to use VBA. Doing this in VBA is actually very simple. Using VBA, you can construct your own cell formulas based on operator input of configured forms.
If you want to consider doing this in VBA, email me off-list and I can send you some samples.

> The other thing is how to poke values from excel to rslinx
> without macros. I have looked at the examples in the RSLinx help
> and only want to consider macros as a last resort.

Having not used RSLinx in quite some time, I can't be of much help on that side except to say that RSLinx does act as a DDE Server or Client so there should be plenty of documentation available, with examples.

>From the Excel side, I don't believe you can write values from Excel to
another application without using a poke macro. Reason being, this requires
multiple steps. Open the channel, send the data and close the channel.
Hopefully some RS experts will expound.

HTH,
Phil

 

These people maybe able to help. They've got some slick and inexpensive software. I've used their OPC Office Link. www.ris.co.nz

Good luck,

Kirk S. Hegwood
President
Signing for Hegwood Electric Service, Inc.
[email protected]

 

Anthony,
Go to the Microsoft support web site and do a search on INDIRECT, they show an example that looks like it might accomplish what you are trying to do.

Lawrence Butler

 

> <snip> So, you can copy and edit each one to collect the
> proper data or you can use concatenation functions to build up the
> references and do a final paste to get them working.
>

That would be a multi-step process involving a macro or requiring the person to do these steps by hand every time. Using VBA, you could automate this process very easily. Go to the VBA editor and insert a module and a form. Here's a quick and dirty example:

Build a form. Add a label called "Enter address:" and lay down a textbox next to it called txtAddress. Then add a pushbutton. Double-click the pushbutton and enter the following code:
OpEnteredDDERef.

In your module, enter the following:

Sub OpEnteredDDERef()
Worksheets("Sheet1").Range("A1").Formula = "=RSLINX|TOPICNAME!'" &
text1.text & "'"
end sub

Sub ShowForm()
form1.show
end sub

Then in Excel, go to Tools->Macro->Macros and click and run ShowForm.

I haven't tested this but I've used Excel VBA so much, I dream about it in my sleep. If it doesn't do what you want or you want to add/modify, send me an email and I'll look closer.

Phil
[email protected]

 

Sorry. The my hope was for something quick and dirty that required a minimum of effort. It's not to be an HMI or SCADA app, just a tool for my own uses.

Anthony Kerstens P.Eng.

 

Thanks for the many responses.

No, you can't use the concatenation function.
It results in a string and not a remote reference.
N7:0 is the DDE item name in the syntax that
Excel uses for remote referencing. My hope was that I would be able to take my descriptor spreadsheet, and through some DDE values in there, and simply build the reference based on the column of addresses I already had collected together.

Then I could pull together a quick and dirty list
for my own purposes, and be able to store it for reference.

Anthony Kerstens P.Eng.

 

Thanks to all who responded. I've decided to bite the bullet, so here's the macro. Essentially, what I wanted was to have a column of addresses with the next column of DDE values from the PLC. This automates the entry of references a little bit by allowing you to start at the top of the column and run down by manually repeating the
macro. Of course, the macro can be assigned to a hot key.

Bearing in mind I'm onsite and don't have the VBA help installed on my laptop, if anyone has refinements to simplify the code or make it more useable, I'd be glad to hear it.

Sub Insert_Rem_Ref()
'Store current cell address
Add = ActiveCell.Address
'Get left-hand cell contents
Range(Add).Previous.Select
data = ActiveCell.Value
'Return to original cell and insert remote reference
Range(Add).Select
ActiveCell.Value = "=rslinx|b1fill!'" & data & "'"

'Get current row and column, increment row number, and go.
dataa = (ActiveCell.Row + 1)
datab = Choose(ActiveCell.Column, "a", "b", "c", "d", "e", "f")
Range((datab & dataa)).Activate
End Sub


Anthony Kerstens P.Eng.

 

I found your Excel Macro very helpful in solving a similar problem. I have included a bit more code to automate the process of filling in the references to the right as long as the left adjacent cell is not empty. I hope you consider this an improvement to your original macro - cheers - lm: Sub Insert_Rem_Ref() On Error GoTo ErrorHandler j = ActiveCell.Column i = ActiveCell.Row Do While Not IsEmpty(ActiveSheet.Columns(j - 1).Cells(i)) 'Store current cell address Add = ActiveCell.Address 'Get left-hand cell contents Range(Add).Previous.Select data = ActiveCell.Value 'Return to original cell and insert remote reference Range(Add).Select ActiveCell.Value = "='F9'|Dyn5!'" & data 'Get current row and column, increment row number, and go. dataa = (ActiveCell.Row + 1) datab = (ActiveCell.Column) Cells((dataa), (datab)).Activate Loop Exit Sub ErrorHandler: End Sub