RSLINX, Excel, OPC/DDE HELP!

I’ve been put in charge of designing an excel spreadsheet for one of our customers (running RSLogix software).

This spreadsheet is meant to get information from an oven that bakes toner, and display it in a series of 4 columns and 51 rows. Each cell will contain a data value from the controller, ranging from F136:0 to F136:253 (on the first sheet... #2 is 137, #3 is 138, etc). They want 21 identical sheets in the Excel workbook… with sheets 2~21 containing the last 20 runs of the process. If it makes a difference, in the controller, every 5th bit (5,10,15, etc) is empty. I didn't do the coding, but I was told that this approach is normal. I think it sounds inefficient and difficult... but I'm new here. haha

I have access to the RSTrainer disks, and I watched the tutorial… it was next to no help. I did, however, create some VB code for each of the 51 rows, and I submitted it to our client. The client reported that only 5 rows were populated, at which point an error occurred. (Runtime error '9' Subscript out of range) Thinking that I was not able to import any more than 25 points per client handle, I split all the data into groups of 25 points. This resulted in the same 5 rows being populated, but with a slightly different error. (Run time error 13 Type mismatch). This is where I currently stand… in need of your help.

The guy before me did cell-by-cell DDE code, and it took him forever. It worked when we had everything in our shop, but once we took it to the customer, we couldn't get all the data to populate. I know there's a way to import whole blocks of data... but I'm a little hesitant to use DDE. What I remember from the RSTrainer disk (when I had a full license to use) is this: highlight the cells you want the data in... then put in the formula. When I try to do this now, I only get 1 #REF instead of one in each cell. Does anybody know the exact syntax of this approach?

This is all very complicated, since I am not able to access the client's computer, network, or PLC controller.

Does anyone have knowledge of this subject, and might be able to help me out?

I will entertain all suggestions: VB, OPC, DDE, etc. Just make it as easy and quick as possible. I probably have to do all the programming while on-site, and not in the comfort of my own office.

~Ryan
IAC

If you want the data to be constantly updated, you should get good results by using the copy/paste dde link functions built into RSLinx. You may want to slow down the polling rate for the topic since there are hundreds of addresses to prevent overloading the network. If your links are individual or in groups, as long as they are in a reasonable order, RSLinx will build the optimal packets and handle organizing the data pretty efficiently.

It is easy to do this on site. I usually create a protected sheet in a workbook that contains the actual remote references to the PLC/SLC in order as found in the processor with an adjacent column which contains descriptions for each address. I password protect this sheet and name it "raw_data" and move it to the end of the workbook. On other sheet(s) that actually display the information in a user friendly/configurable format, I will put references to the "raw_data" worksheet. This way, the end user can move things, change colors, fonts, sizes, add charts, etc. without disturbing the DDE links and most importantly, upsetting the communication efficiency.

If you want to have a button on the worksheet that the operator uses to retrieve the data "one time per button press", then a simple VBA macro can get the data for you. This is a subject where I have seen some excellent examples posted both here and at MrPLC.com

Thank you so much for replying so fast.

What is the syntax for importing blocks of data? I'll help you out a little. The info I need is in a topic called "OCE_CYCLE_DATA" and the data I need starts at F136:0. There are 253 data points, and I need it arranged in 4 columns.

The reason I ask this question is because I had access to the RSTrainer disks, and following an example on the disk, I was able to highlight a group of cells (51Rows x 4 Columns), and just type in ONE FORMULA which would fill in every cell. Unfortunately, I had to do this off-site and all I got in every box was #REF. I want to be able to do this at the client's machine, so that I can make sure it links up correctly.

Also, I really like your raw_data idea. Unfortunately, I have 21 sheets of information I need to display (one current run of a process, and the last 20 runs), so having individual raw_data sheets isn't feasible. Now... since only the first sheet is actually changing... is there a way to store (and resave w/ every run) the last 20 runs of the process in separate sheets? I just think it's inefficient for the spreadsheet to be constantly polling all that data, just to report that less than 5% of it (1/21 sheets) actually changed.

One last question... how do I slow down the polling rate? I like that idea, since I think that could have been causing the issue the last guy on the project had. He told me I'd have to use OPC, since DDE had "windows security issues." Are you aware of anything like that? Do I have to change and security permissions? Is this a Windows thing?


Thanks again for all your help. You're really helping me out, here!

~Ryan

RyanG said:

What is the syntax for importing blocks of data?

Click to expand...

The topic must be configured first. DO that under DDE/OPC..>Configure Topic.

Then on the RSLinx Menu bar, click Edit...>...Copy DDE/OPC Link.
A dialog will open up that will allow you to browse to the topic and drill down into the actual starting address. There is a space to enter the total number of consecutive data items, and the number of columns per row...

Then go to excel and right click the cell where you want your table to start (upper leftmost cell). CLick Paste Special, and change the radio button to Paste Link before hitting okay.

This is an indirect answer to your question about syntax. Instead of trying to remember the syntax, I use the above method, and then look at the resulting cell contents for the actual syntax used.

RyanG said:

... since only the first sheet is actually changing... is there a way to store (and resave w/ every run) the last 20 runs of the process in separate sheets? I just think it's inefficient for the spreadsheet to be constantly polling all that data, just to report that less than 5% of it (1/21 sheets) actually changed.

Click to expand...

There are probably a dozen different ways to make that happen. I would recommend you do some more searching here and at MrPLC.com to find a better answer from someone more experienced with VBA.

RyanG said:

how do I slow down the polling rate?

Click to expand...

Under RSLinx menubar item "DDE/OPC...> Topic Configuration" dialog box the middle tab called "Data Collection" contains settings for polled data rates in milliseconds. This setting will affect all of the data in the selected topic, and the topic may be lockedd (preventing changes) if there are items being serviced (i.e. an open excel workbook with DDE links active).

PLC & Touchscreen

hey guys

I'am looking for an easy software to control the plc via a touchscreen I hope you can help me

Thanks for everything. I'll see if it works on Thurs. when I go to the client to test.

OkiePC said:

There are probably a dozen different ways to make that happen. I would recommend you do some more searching here and at MrPLC.com to find a better answer from someone more experienced with VBA.

Click to expand...

Any suggestions on search terms?

Hi:

I've done this in the past with a package called XLReporter, you can try it free from www.sytech.com it runs for two hours and after that you have to reset the PC, but try it if you want it, may be it is the solution you're looking for.
You have to use Rslinx ans an OPC server in order to comunicate
With this software you can also generate, customized reports based on events or periodically.

http://forums.mrplc.com/index.php?dlcategory=3


There are several RSLinx > Excel DDE downloadable samples here...

Well, I went to the client's place today... and it didn't work.

I tried a bunch of times, but still every cell had #NAME or #N/A. I do have more information about the issue now, though...

When viewing the communication errors, I get something to the effect of "the word size is too long," as shown in the snippit below:
____________________________________________________________

RSLinx Classic v2.50.20.00 started Thursday, March 29, 2007 10:51:14 AM
Event Log cleared last at Thursday, March 29, 2007 11:16:46 AM
Log written Thursday, March 29, 2007 11:31:43 AM

AB_ETHIP-1\0.(192.168.27.64) : F151:190,L1,C1 : 03/29/07 - 11:16:49 : 61450 (F00Ah)
Items: F151:190,L1,C1, F151:191,L1,C1, F151:192,L1,C1, F151:193,L1,C1, F151:195,L1,C1, F151:196,L1,C1, F151:197,L1,C1, F151:198,L1,C1, F151:2,L1,C1, F151:20,L1,C1, F151:200,L1,C1, F151:201,L1,C1, F151:202,L1,C1, F151:203,L1,C1, F151:205,L1,C1, F151:206,L1,C1, F151:207,L1,C1, F151:208,L1,C1, F151:21,L1,C1, F151:210,L1,C1, F151:211,L1,C1, F151:212,L1,C1, F151:213,L1,C1, F151:215,L1,C1, F151:216,L1,C1, F151:217,L1,C1, F151:218,L1,C1, F151:22,L1,C1, F151:220,L1,C1, F151:221,L1,C1, F151:222,L1,C1, F151:223,L1,C1, F151:225,L1,C1, F151:226,L1,C1, F151:227,L1,C1, F151:228,L1,C1, F151:23,L1,C1, F151:230,L1,C1, F151:231,L1,C1, F151:232,L1,C1, F151:233,L1,C1, F151:235,L1,C1, F151:236,L1,C1, F151:237,L1,C1, F151:238,L1,C1, F151:240,L1,C1, F151:241,L1,C1, F151:242,L1,C1, F151:243,L1,C1, F151:245,L1,C1, F151:246,L1,C1, F151:247,L1,C1, F151:248,L1,C1, F151:25,L1,C1, F151:250,L1,C1, F151:251,L1,C1, F151:252,L1,C1, F151:253,L1,C1, F151:26,L1,C1, F151:27,L1,C1, F151:28,L1,C1, F151:3,L1,C1, F151:30,L1,C1, F151:31,L1,C1, F151:32,L1,C1, F151:33,L1,C1, F151:35,L1,C1, F151:36,L1,C1, F151:37,L1,C1, F151:38,L1,C1, F151:40,L1,C1, F151:41,L1,C1, F151:42,L1,C1, F151:43,L1,C1, F151:45,L1,C1, F151:46,L1,C1, F151:47,L1,C1, F151:48,L1,C1, F151:5,L1,C1, F151:50,L1,C1, F151:51,L1,C1, F151:52,L1,C1, F151:53,L1,C1, F151:55,L1,C1, F151:56,L1,C1, F151:57,L1,C1, F151:58,L1,C1, F151:6,L1,C1, F151:60,L1,C1, F151:61,L1,C1, F151:62,L1,C1, F151:63,L1,C1, F151:65,L1,C1, F151:66,L1,C1, F151:67,L1,C1, F151:68,L1,C1, F151:7,L1,C1, F151:70,L1,C1, F151:71,L1,C1, F151:72,L1,C1, F151:73,L1,C1, F151:75,L1,C1, F151:76,L1,C1, F151:77,L1,C1, F151:78,L1,C1, F151:8,L1,C1, F151:80,L1,C1, F151:81,L1,C1, F151:82,L1,C1, F151:83,L1,C1, F151:85,L1,C1, F151:86,L1,C1, F151:87,L1,C1, F151:88,L1,C1, F151:90,L1,C1, F151:91,L1,C1, F151:92,L1,C1, F151:93,L1,C1, F151:95,L1,C1, F151:96,L1,C1, F151:97,L1,C1, F151:98,L1,C1
Type: READ
Mfg: Allen-Bradley
PlcType: SLC-503+
Desc: Transaction size plus word address is too large
Number of occurrences: 1
Last Error occurred: 03/29/07 - 11:16:49
AB_ETHIP-1\0.(192.168.27.64) : F152:0,L1,C1 : 03/29/07 - 11:16:49 : 61450 (F00Ah)
___________________________________________________________

It did this for every "run" of the process.

Interestingly, the client just called me while I was typing this, and reported that it works now!

Apparently, they changed a maximum packet size value from 200 to 40,000, and unchecked "maximum packet size (Ethernet)" or something.

Can anyone comment on this? I know it works, and I should just be happy that it works... but I don't want trial and error to be part of solving every issue like this.

Also, the version that works had EACH of the above data points copied and pasted as a link INDIVIDUALLY. After seeing my copy a whole block of info, the client tried to do the same, it he still gets #N/A. Apparently, importing single words per cell is the only way they can get it to work... and importing a whole block of info does NOT work. Any comments?

Thanks for your help!

Old topic (no pun intended), but ...

I have been struggling through something similar. I programmatically created a large spreadsheet so I could pull the tag data from 9 different SLC PLCs. 7 of the PLCs had a long path between my workstation and the PLC (through ethernet, controlnet, and DH+), but I couldn't pull tag data for the 2 PLCs that were directly connected via ethernet alone. I would occasionally see a good data value appear on-screen, then immediately disappear.

Finally, after reading about "maximum packets" here, I turned off both "Limit Maximum Packets" and "Use Maximum Packet Size (Ethernet)" -- and it worked great. No more time-consuming tag checking: open up one spreadsheet and all the tag data is in one spot.