Connecting Excel to ControlLogix
by John Schop
Have you ever lost data in a CLX processor, because you downloaded new code? Unfortunately, when you donwload a program to a ControlLogix processor, you also download the values of the tags (variables).
A solution to this problem that could be useful, is an Excel sheet that reads and writes values to the ControlLogix processor using the DDE/OPC capabilities of RSLinx.
In this article, I will show you how to create one of these sheets for your projects.
Here's what you'll need:
- Microsoft Excel, with some basic knowledge about programming macro's in Visual Basic
- RSLinx (not the 'Lite' version, because that does not have DDE/OPC capabilities)
- A ControlLogix processor of course
Let's fire up RSLogix first, and create a bunch of tags with values. In this example, I created 2 arrays, of the types DINT and REAL, each with a length of [10] tags. These arrays I filled with some values:
I'm not going to do anything with the PLC program, I just need some data in a number of tags.
Next, we're going to set up a DDE/OPC Topic in RSLinx. Depending on the version of RSLinx you use, it might look slightly different, but you should be able to follw this with the screenshots.
Assuming that you know how to setup RSLinx initally to get online with your controller, I've skipped some steps. The setup I use looks like this in RSLinx:
As you can see, I have a 10 slot CLX rack, with a 1756-ENBT card in slot 1 (address 134.200.211.16), and two processors, one in slot 0, and one in slot 2. The one in slot 2 is the processor we are going to use for this exercise.
Now, open up the DDE/OPC topic configuration by clicking 'DDE/OPC' and then 'Topic Configuration' in the top menu of RSLinx.
I'm going to create a new DDE/OPC topic called 'EXCEL_TEST', and use the Logix5550 processor in slot 2 as the data source. In order to do this, you have to click the 'New' button, give the topic the desired name, and make sure the processor in slot 2 is selected as the source before you click 'Done'
To test if your setup is working, at this point you can use the OPC test client provided with RSLinx. I'm not going into detail about that, but I did make sure this worked before continuing with the next step, creating the Excel sheet.
Let's start up good old Excel, and create a new workbook. On this workbook, place a new command button. You can find the Command Button control in the 'Control Toolbox' toolbar in Excel. When you have the button, right click on it and choose 'View Code'. This will take you to the Visual Basic Editor:
First, create a function that will open the DDE topic to Excel:
Now, if I call this function from the CommandButton1_Click event, it will open the link to RSLinx:
The variable 'rslinx' will hold the number of the open channel. All subsequent DDE functions use this number to specify the channel.
To save you all the steps to program the rest of the code, here is the final code to get the array of REALs out of the controller, and put them in cells D2 – D11, and the array of DINTs in cells E2-E11.
Now we know how to read, it would of course be a lot of fun if we could write values as well. I would like to be able to change the values in the cells, and then hit a 'Write Data' button.
First, make another button on the sheet (mine looks like below now)
And then write some code for the button:
The way this is implemented is of course very rudimentary, but once you get the concept, the sky is the limit.
To make this easier on everybody, I've included the Excel file with the code already in it. The only thing you have to do to make this Excel sheet work, is make sure there is an DDE/OPC topic in your RSLinx setup called 'EXCEL_TEST', and the arrays REAL_Array and DINT_Array in your controller (of at least length 10).
It's worth noting that if you set up as above then you can also make a hard reference in a cell with the following format.
=APPLICATION|TOPIC!'TAG_NAME'
So in your example, to read the first element in the DINT_Array would be
=RSLINX|EXCEL_TEST!'DINT_Array[0]'
You'll get the number in the cell if successful otherwise it will say #REF! if there is a problem. This is great as a quick test or if you just need a simple value. The downside to this method is very slow data transfer rates. I beleive most of this is due to Excel, becuase when using Visual Basic with RSLinx Topic the transfer rates are much faster. There are also problems related to RSLinx Topic configuration. If there are many Topics created or some with similar names the Excel program can have troubles connecting. I have had to delete Topics in RSLinx when this happens.
There's a small bug in the code. On the read error detection it should be:
If TypeName(realdata) = "Error" Then
...
If TypeName(dintdata) = "Error" Then
instead of:
If TypeName(data) = "Error" Then
Otherwise the code won't ever alert the user to an error.
Below is a small macro that could be expanded for larger projects. In excel, column D should contain the exact PLC tagname that you wish to write to, and column F should contain the value that you wish to write into the PLC tag. Works well for me and so simple. Oh, you also need to setup a topic called "EXCEL" in RSLINX that connects to the PLC you wish to write to.
Sub WRITE_TO_PLC()
'open dde link: testsol=DDE Topic
RSIchan = DDEInitiate("RSLinx", "Excel")
'write data thru channel
DDEPoke RSIchan, Range("D5"), Range("F5")
DDEPoke RSIchan, Range("D6"), Range("F6")
DDEPoke RSIchan, Range("D7"), Range("F7")
'close dde channel
DDETerminate (RSIchan)
End Sub Example: read out a 2Dimensional Array?
I want to read out: REAL-Data[0,0]
.
.
.
REAL_Data[1,3]
For a = 0 to 1
For i = 0 to 3
Data = DDERequest(rslinx, "REAL_DATA[" & a & "," & i & "],L1,C1")
*error check
*move to cell
next i
next a
| 
