Excel remote referencing


Thread Starter

Anthony Kerstens

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.

Darold Woodward

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]

Darold Woodward

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]

Pierre Desrochers

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

We have the same thing happening 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

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

Hope this helps

[email protected]

Glass, Philip

> 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.


Butler, Lawrence

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

Glass, Philip

> <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:

In your module, enter the following:

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

Sub ShowForm()
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.

[email protected]

Anthony Kerstens

Sorry. 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.

Anthony Kerstens

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 simple 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.

Exactly - copy the column of the AB's N7 integer range after the concatenation and "paste special" as "values" only to the new row. Perform some creative "search and replace" commands to rebuild the DDE reference via the RS-LINX server to the AB PLC.

Don't get hung-up on creating the concatenated "string" column, b/c it's only an intermediary step.

Brett Haas

John G. Boland

Hi, Anthony,

It is an interesting problem. You don't want to fiddle with macros, so this *may* allow you to use the nifty Excel &lt;copy> and &lt;paste> to generate the links, like you want. Try it, anyway.

&lt;copy> the Excel 97 VBA below and &lt;paste> it into a new macro in a workbook. Follow the instructions in the text and use the Excel Tools|Macro|Macros menu item to start it. I do not have RSLinx, but (as I tried to document) I
think it will "fire up" your links.

Please let me know how it goes.


John G. Boland, president
VisiBit Corporation
One Parker Square Suite 408
2525 Kell Boulevard
Wichita Falls, Texas 76308
940.723.1478 fax

Public Sub PasteFormulas()
' Macro recorded 12/14/00 by John G. Boland
' This macro combines the contents of columns
' A, B, and C in column D, converts the formula
' strings to text, and activates the cells. A
' typical result is:
' Manually enter the desired contents of
' columns A, B, and C, for example:
' Column A: N
' Column B: 7
' Column C1: 0, C2: 1, C3: 2, C4: 3, ...
' Run this macro, which overwrites column D.
' If columns A, B, *and* C are blank in a row,
' the corresponding row of column D will be blank.
' "MaxRow" is a variable that limits how many rows
' the macro can activate. The initial value is "1".
' The maximum possible value is 65536, the Excel
' maximum number of rows. Experiment with a SMALL
' value of MaxRow, initially.
ActiveCell.FormulaR1C1 = _

' The string above has no line breaks in it.
' It is the literal contents of the cell.
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False 'equivalent to &lt;esc>
' The following loop activates RSLinx.exe
' It may cause a Microsoft Excel dialog:
' "Remote data not accessible. Start
' Application 'RSLINX.EXE'?"
' <click> "No" to loop through to the end
' of column D
' I do not know what happens if RSLinx.exe
' is available and is allowed to start. The
' loop may run without any further prompts.
MaxRow = 1
' 65536 is the largest value in Excel.
If MaxRow &lt; 1 Then MaxRow = 1
If MaxRow > 65536 Then MaxRow = 65536
' OK, I didn't trust you.
For Row = 1 To MaxRow
Range("D" & Row).Select
A = ActiveCell.Formula
If A &lt;> "" Then ActiveCell.Formula = A
Next Row
End Sub

Anthony Kerstens

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
data = ActiveCell.Value
'Return to original cell and insert remote reference
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.

Dean Bickerton

If your data items are contiguous you can use the Length and/or Column delimeters in Excel. RSLinx understands Excel table format. "=RSLINX|TOPICNAME!'N7:0',L10,C2 (For N7:0-9 in 2 columns). If this doesn't solve your problem you will probably need a to resort to a macro. Macros will also be needed to poke values down to the PLC. Maybe the next version of Excel will have an OPC interface or maybe some third party makes an add on. This would solve all your problems.
I have been using a same Excel Sheet template for monitoring all the PLC values till I found the following feature in the new RSLinx (V2.1). If data monitoring is all you need (you won't be able to write the data anyway using DDE on Excel), goto RSLinx, select the correct node from the WHO-ACTIVE screen, and click 'DATA MONITOR' from the STATION drop-down menu on the menubar. You can monitor all the data in the PLC.