TJ2020

Is there an easy way to copy an Excel table into a string array in RSLogix?

27 posts in this topic

Is there an easy way to copy an Excel table into a string array in RSLogix? instead of doing it one by one?

Share this post


Link to post
Share on other sites

TJ2020 welcome to mrplc.codo the trick.m.  IF you check the download section you'll find several examples of using DDE and OPC to copy data from an Excel Sheet to a Rockwell PLC.  That should 

Share this post


Link to post
Share on other sites

I think the method I found works only with a live PLC. how about without PLC and offline?

Share this post


Link to post
Share on other sites

TJ2020 - I didn't realize you were want to work offline.  That is more difficult then online, but is possible.

Offline involves having an Excel VBA Macro that that takes your strings and places them into an CSV file which RSLogix / Studio 5000 can import.

To get an ides of the format look into exporting portions of your program with the software.

Share this post


Link to post
Share on other sites

I am lost, is there any macro to do that? can you walk me through it?

Share this post


Link to post
Share on other sites

lets start with what you do manually.  if the program is not proprietary/confidential, please post your spreadsheet and logix program.

Share this post


Link to post
Share on other sites

When I try to upload my files it gives me an error? my file is below 2 MB???

 

 

Share this post


Link to post
Share on other sites

 

Unfortunately I can't share the files due to an error from the forum.

There are 3 tags (Fault_Banner_1, Fault_Banner_2, Fault_Banner_3) with array of 500.

There is excel sheet with 3 tabs, each tab contain 500 faults.

Need to copy each tab's faults into each tag. ( Each fault will be copied into each string)

I will upload if the fault goes away, if not then please advise based on what I just explained above.

Cheers,

Share this post


Link to post
Share on other sites

ok so this is one time kind of deal...

try to enter 2-3 tags and then export project as L5K. this way you can see exactly the format. L5K is just a text file so you can easily merge data from Excel. when done, import the L5K back into RSLogix and see if it is all correct.

Share this post


Link to post
Share on other sites

The problem with this method is that you need to count the characters in each fault and enter the number.....

Share this post


Link to post
Share on other sites

why...?

why don't you use Excel.... to give you length of each string in a cell next to it...

whatever i do, i let the Excel to all the work so i get ready to import string. sorry cannot add attachments, 

if your string is in column C (for example in C2 is the first line of text), then click on B2 or D2 and enter

=LEN(C2)

that should give you result for one string.

click on cell with formula, then grab handle in lower right (black rectangle) and drag it down....

this will copy the formula to other lines.. done in 5 sec

 

 

 

Share this post


Link to post
Share on other sites

To tell you the truth I did this at the past and didn't do well, that's why I am trying to find a better way...

 

Share this post


Link to post
Share on other sites

well, suit yourself... 

personally, i love my Excel... with just few clicks it does tons of work for me every day regardless if working with PLC or robot or HTML or whatever. without it i could not do nearly as much... 

Share this post


Link to post
Share on other sites

are you familiar with VBA or AutoIt? making custom script would be ok if this need to happen frequently,

here is another idea, using SendKeys to populate spreadsheet (but it could go the other way around too)

Both Excel and RSLogix support VBA,

in the downloads should be example for VB to control applications from your program, don't remember if VB6 or .NET.

AutoIt is free and does the same (and more). it is packed with help/examples (each help is a script that can be executed).

i mean there is a million ways to do it, i just don't know how many tools are in your toolbox.

 

Share this post


Link to post
Share on other sites

Unfortunately I am not familiar with those.I am usually using mouse recorder to be able to do this task in auto, but the problem was I couldn’t find a shortcut on the keyboard to open the string box instead of mouse click to paste my text... do you know that by any chance?

Share this post


Link to post
Share on other sites

don't have RSLogix here right now, so cannot try but should be able to get there using keyboard...may need a sequence of several keys like arrow keys/tab/enter

Share this post


Link to post
Share on other sites

I pretty much tried all kinds of combinations, but didn’t find it 

Share this post


Link to post
Share on other sites

I downloaded your String_trial from the download folder and got access to the plc, but can't make it work. Can you give me some directions please?

Share this post


Link to post
Share on other sites

Unfortunately anytime I copy a link in my message, it won't let me send it and gives me an error.

And yes, that's the link. how can I make it work? I downloaded into the PLC, open the excel  and added couple of messages and hit write, but nothing changed.

 

Share this post


Link to post
Share on other sites

unfortunately few things may be broken with the forum. that may be one of them.

also please use complete sentences when describing steps you did. it is not easy to follow what you mean and i don't want to keep on guessing. just like you would like detailed instructions, we would like to know what you have done or are trying to do. 

1. you downloaded 'what' to a PLC? you mean your program?... or enclosed ACD file? which is it?

2. you open Excel? what does that mean? there are two files, which one did you open and how? Using some version of Excel? there should be prompt asking if you are sure to enable macros etc. did you do so?

3. there is a dialog suggesting to start RSLinx...  did you get that?

4. link description says " VBA code is well documented " did you read the code? it uses DDE. What type of RSLinx you have? i don't think DDE works with RsLinxLite.

5. you added couple of messages... where exactly?

6. did you adapt DDE Topic name

7. you clicked and nothing changed? what does that mean? no response? dialog did not open and confirm operation succeeded or failed? or it did but no change on PLC? or something else? did you try setting brake points and stepping through code?

 

Share this post


Link to post
Share on other sites

My apologies, the problem is anytime I write details I get an error and doesn't submit.

1.Downloaded the PLC file ( Strign_Trial.ACD ) into the L73.

2.Opened both by just double clicking on them. And hit write the tag PB in both. I have Office 2010 installed. Does that matter? Yes, message popped up and I said yes.

3.Yes, even prior to that I started my RSlinx.

4.“VBA code is documented” just opened the macro and went through that. Is that what it meant? also Have RS linx classic installed.

5.MSG_Tag[4]   Faultmessage1, MSG_Tag[5]   Faultmessage2

6. I think this is my problem, how should I do that?

7. I clicked on write the message PB and was expecting the messages to be written in the PLC. The dialog box opened up and gave me the successful message, but didn’t see the fault being copied from excel into the PLC. I went through the code, but didn’t see anything to be in a wrong order.

Share this post


Link to post
Share on other sites

You’re the man, I works now.

It works with Strign_Trial.ACD and String_Read_Write.xls, so now my question I want to make my own file with it’s own name work. What needs to ne renamed in the macro in order to make it work?

Share this post


Link to post
Share on other sites

i would just use the existing one and maybe change few things... and make it look better and more flexible (easier to scale...)

 

1. goto CONTROLS sheet and change color of cells F2,F3,F4 to something else and maybe set borders. there are cells where you can enter own values.

2. click on StringList cell A2 and change formula to =CONTROLS!$F$3&"["&J2&"]"   (this one looks better and it is easier to extend since you will need 500 elements)

3. then click on that cell again , grab black rectangle in the lower right corner and drag it down sufficiently far (1000 rows for example)

4. click on StringList, J2 and enter 0 (whatever the first array index is), in J3 enter 1... select both J2 and J3 at once and like before drag the lower right corner down sufficiently far (1000 rows for example)

5. now the spreadsheet got some facelift...  go to sheet CONTROLS and click on cell F3, change name from MSG_TAG to name you need, then check previous sheet with string data. note that all those 1000 rows now show your variable name and correct index, and you can do more than 300 elements.

6. go to sheet CONTROLS and click on cell F4, change value to 500

7. paste your strings from your Excel document to StringList cells B2-B501

8. click on write....first of three tags are being written....

9. repeat from step 5 for other two tags.

 

 

Btw. i strongly suggest to not use this method on first few projects. just copy those 1500 strings one by one manually and do so on at least 2-3 projects. after that you will no longer lack motivation to get acquainted with Excel and VBA ;-)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now