Read & write to CLX tags from Excel for position updates

[Toot Uncommon 0]

We have a machine that frequently has to have it's shuttle positions offset by a mm or 2. This happens at changeover. I believe there are 32 position that are not all used. We are now manually offsetting each position one at a time using the hmi and a sequence of "save", "are you sure" "ok" and such that is very time consuming. What I would like to do is mount an indicator and fixed flag to measure what offset we need (instead of eyeballing), and have an excel sheet where I can plug in that offset, hit a button and have it write the new position values to all the tags. Trouble is I'm new to the Excel to RSlinx thing and I'm having trouble with some of the generalized guidance I've found here and elsewhere on the web. What I have so far is roughly based on what I found here: http://www.plcdev.com/connecting_controllogix_excel His array is 10 values, mine is 32. I have 32, but for each of those 32 points there are 6 sub values and I only need the one of the six: And, the ones I need (as shown above) are nested 4 levels down in the tag tree (is it called a tree?) Here's the code I have for excel: Private Sub CommandButton1_Click() rslinx = OpenRSLinx() 'Loop through reading the CLX array tags and 'put the values into cell For i = 0 To 1 'First the array of REALs 'Get the value form the DDE link realdata = DDERequest(rslinx, "CURRENT_PART.CELL_B_SHUTTLE_POINTS(0).Position[" & i & "],L1,C1") 'If there is an error, display a message box If TypeName(data) = "Error" Then If MsgBox("Error reading tag CURRENT_PART.CELL_B_SHUTTLE_POINTS[0].POSITION[" & i & "]. " & _ "Continue with Read?", vbYesNo + vbExclamation, _ "Error") = vbNo Then Exit For Else 'No error, place data in cell Cells(2 + i, 4) = realdata End If 'Now the array of DINTs 'Gent the value from the DDE link dintdata = DDERequest(rslinx, "CURRENT_PART.CELL_B_SHUTTLE_POINTS[1].POSITION[" & i & "],L1,C1") 'If there is an error, display a message box If TypeName(data) = "Error" Then If MsgBox("Error reading tag CURRENT_PART.CELL_B_SHUTTLE_POINTS[1].POSITION[" & i & "]. " & _ "Continue with Read?", vbYesNo + vbExclamation, _ "Error") = vbNo Then Exit For Else 'No error, place data in cell Cells(2 + i, 5) = dintdata End If Next i 'Terminate the DDE connection DDETerminate rslinx End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub Private Function OpenRSLinx() On Error Resume Next 'Open the connection to RSLinx OpenRSLinx = DDEInitiate("RSLINX", "EXCEL_TEST") '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 When I hit the button, nothing happens. No error, no nothing. I do have rslinx pro and I setup the EXCEL_TEST topic as required. I can enter the hard code formulas and get the values that way, though not consistently. But that at least proves I have a connection. The hard code I'm talking about is from the link tutorial I mentioned earlier in the comments at the bottom: =RSLINX|EXCEL_TEST!'CURRENT_PART.CELL_B_SHUTTLE_POINTS[1].POSITION' That in a random cell will actually display the position from the tag. What am I doing wrong?

[Bob A. 1]

I have "played" with Excel, Linx and DDE off and on for 15 years, at least. And my experience has been that it can work but that it is undependable. Worst yet, in recent years, it has gotten increasingly difficult to turn off all the things in the OS, to let it work. I would suggest that you put a SCADA app in your PC and use Linx or get a simple MMI and network to the PLC. Right now, I'm using PanelView Component for MMIs and their development software is built into them. They have both Ethernet and USB ports, and a SD memory slot. You have a requirement that is about as simple as it gets, so a simple MMI will do the job. Best Regards, Bob A. Edited 11 Sep 2010 by Bob A.

[Toot Uncommon 0]

In my situation, added expense is not an option. I really need to learn what I can about the Excel to rslinx interface. If it really cannot be made reliable with Excel, maybe I could make an app with VB? I wonder how much different/more difficult that would be? I bought a copy of VB 6 from a thrift store for $20 or so. I haven't had time to mess with it yet, but maybe that's the route I should go???

[paulengr 44]

As has been mentioned...getting DDE to work at all is usually an exercise in frustration. Periodically (once every couple weeks with continuous use), it will just magically break for no reason as well. Excel is also part of Microsoft Office which is generally speaking, an island stranded in the middle of the Pacific Ocean. There are of course a FEW airlines that serve it (Microsoft offers SQL query access) but that's about it. Contrary to the popularity in the business world of using it as a glorified forms system, it is highly unfriendly to anyone else, and Microsoft of course has declared the one and only general interface (DDE) to be deprecated and has even taken steps to stop installing it in their operating systems over the last few years. VB is similarly an island...or more correctly, it's the glue that holds the archipeligo that Office lives on together. You can of course interface to it with no problems. Just open your wallet and buy the appropriate ActiveX module. The most popular ones are OPC which is more or less "universal" as far as the API goes, and I believe Ingear. What's going on here is that so far everything you named is part of the COM/DCOM object model. Jumping to dotNET (CLR) isn't any better...it's just another self-contained object model world. In addition, you cannot use just any version of RS-Linx. The free version supports communications between Rockwell software products and PLC's, but NO ONE ELSE. You MUST buy at least RS-Linx OEM to support any sort of communication. The price is higher than that of all the competitors who sell OPC servers. The solution here is to get to an open solution. The OPC Foundation has fought with this problem for years and recently released the OPC-UA spec which finally bypasses the limitations of the COM/DCOM object model. They still control access to the documents (just as the ODVA controls access to the Ethernet/IP specification) but fortunately, Inductive Automation has already released a 100% free OPC-UA server. It is easily accessible from the Java object model which is also entirely open and fully documented with free documentation (JSR's). Now that being said if you don't want to build the interface into this system, Inductive Automation also gives away a single user license of their HMI/Scada software which is probably exactly what you need. You can easily program it in Jython which is a Python system (another free, open system) built within the Java executable that the HMI runs on. Java has been infected by the language police so it is somewhat difficult to just "pick up and use", but not Python. Most folks can learn to use Python and knock out programs in it within a few hours at most.

[Toot Uncommon 0]

That sounds really good and I'll bet AB wishes it could kill IA right now. Yet, in my situation I don't think I can use it. I'm just a tech and I don't have much pull with engineering or IT around here. If I do this, it's going to have to be with what I already have. Excel and RSlinx pro are already installed. The only reason I thought VB might work is by making a small executable that doesn't have to be installed, yet would do the trick. When you say it breaks, what do you mean by that? Just doesn't work? What would be the worst case with the project I envision? We're not talking about writing garbage to the tags and having the shuttle moving warp speed off the rails and into the operator, are we?

[paulengr 44]

No. The buffer code that Microsoft wrote is extremely buggy. Occasionally it just stops communicating with the software stack, whether it is RS-Linx or any other DDE-connected system. There isn't an error message or anything else to indicate you have a problem. The software either just hangs or simply acts like nothing is wrong (but you get nothing reading or writing). The only way out of it is that you have to close and reopen the DDE connection. The only effective way that I know of to detect this is to monitor something on the remote end (a heart beat such as a clock) and actively check whether the connection is alive or not. So you don't get garbage...just something unreliable that you will have to explain to any potential users why you have to stop/reboot their stuff periodically. This (and the deprecation threats) is the #1 reason that the OPC Foundation developed the OPC-UA standard and is getting out of the COM/DCOM world. The new standard is entirely based on Internet (TCP/IP) protocols. Among others, I believe that the ABB 800 series PLC's actually implement OPC within the PLC itself! Inductive Automation is a great example of what the capabilities of OPC-UA really are. I haven't seen version 8 but I believe that GE Fanuc isn't too far behind with something very similar. Their Cimplicity platform is very close to the OPC-UA model in terms of implementation already. Rockwell's FactoryTalk platform is also similar except that they don't publish any details on it. Since Inductive Automation's software is all Java and runs pretty much anywhere, there are a lot of possibilities here to avoid the "install" problem. You can easily fit an entire operating system such as Linux right onto a USB stick and boot off the memory stick for instance. You never have to touch the local hard drive at all if you take this approach.

[Gene 0]

After looking at your code I dont think that the DDE connection is the problem. The problem is how your VB code is written. Your index reference was in the wrong position. You also only need to get the data from the array of REALS. Your For loop should look like this: For i = 0 To 1 'First the array of REALs 'Get the value form the DDE link realdata = DDERequest(rslinx, "CURRENT_PART.CELL_B_SHUTTLE_POINTS[" & i & "].Position,L1,C1") 'If there is an error, display a message box If TypeName(data) = "Error" Then If MsgBox("Error reading tag CURRENT_PART.CELL_B_SHUTTLE_POINTS[" & i & "].POSITION" & _ "Continue with Read?", vbYesNo + vbExclamation, _ "Error") = vbNo Then Exit For Else 'No error, place data in cell Cells(2 + i, 4) = realdata End If Next i The rest of the code should be the same and this should give you the correct data. Gene