Auto generate code in Excel?

PhilRey

Member
Join Date
Apr 2002
Posts
57
In a previous thread named Alarm Code , a reference was made about autogenerating code using Excel.

I am familiar with using text for coding ladder, but I am curious about how Excel is used to automatically generate code.

Can anyone shed some light on this for me? :confused:

Thanks,
-Phil
 
Autogenerating Code

I don't even bother with VBA (yet - I'm heading there.)

Before hand, I lay out the data tables in such a way as the whole thing will work. That's the key. You have to create a pattern and follow it. If you have your analog inputs in one order, you really should have the alarms in that same order.

Then I create one rung that references the first set of registers. Test it, debug it, test it some more.

Once I'm sure that it's what I want, I copy the ASCII string into Excel. Take the "Analog Input High" alarm in my example:

XIC B211/1 BST GRT F31:0 F215:1 NXB XIC B210/0 BND CMP F31:0 (F215:1 - F215:4) BST XIC T224:0/DN OTE B210/0 NXB TON T224:0 0.01 1 0 BND BND

Next I break it into chunks of "Fixed Text" and "Variable Addresses", and place them in different cells on the same line:

B1: "XIC B211/"
C1: 1
D1: " BST GRT F31:"
E1: 0
F1: " F215::"
G1: 1
H1: " NXB XIC B210/"

and so on.... Note the leading spaces. Very important.

Cell A1 is the contantenation of the other cells: =B1 & C1 & D1 & E1 &....

Copy line 1 to line 2, change the variables to fit whatever pattern I'm after (increment by 1, increment by 5), a few quick drags of the cells (grabbing the box in the lower right)

And presto, column A has the ASCII code, all 100 alarms.

In RSLogix, do a "Copy to Library" of the good rung, creating the .SLC. Open it up in Notepad. Delete the one rung, Select Column A in Excel, copy, paste in notepad. Save.

In RSLogix, "Paste from Library", and you're done. 100 rungs of TYPO_FREE code in about 10 minutes.



The above uses the the PLC register addresses. You can also use the technique with Symbol names. Again, pre-planning is the key. If you've got the Analog Input "TANK_LEVEL", and you just add suffixes to it, for example: the alarm enable is "TANK_LEVEL_ALM_ENA", the high limit is "TANK_LEVEL_ALM_HI_LIM", the deadband is "TANK_LEVEL_ALM_DBND", and so, you could generate you code like so:

B1: "XIC "
C1: "TANK_LEVEL"
D1: "_ALM_ENA BST GRT "
E1: "TANK_LEVEL"
F1: " "
G1: "TANK_LEVEL"
H1: "_ALM_HI_LIM NXB XIC "

etc.

Notice that column C, E, G are the same. So change them to '=C1'
If you copy this rung 100 times, and have a list of the base symbol, you can just copy that into column C (or reference it from there), and again, you're done.

The catch is that the symbol must exist in the PLC. But since you're using Excel, you can use it to create all the symbol names up front and import it.

The nice thing about using this technique is that the addresses don't matter. But you still import bug-free, annotated code in a remarkably short time.
 
I essentially do what Allen does, but having VB to the heavy lifting. You can write a text file from vb and call it anything you want. We build a lot of dial index machines and often have one of about six types of mechanisms at each station. I can then just enter the station name, choose the type and press go. It also generates a .csv file for the I/O descriptions.

There is a lot more to it than that, but I will have to wait until I get to work tomorrow and look at agian.

BTW, all that work is unnecessary for a plc that supports user created function blocks and data types.
 
Rick Densing said:
BTW, all that work is unnecessary for a plc that supports user created function blocks and data types.

Yes and no. With user-defined function blocks, the code structure is in place, but you still have to link the source data to it.

Thus, you spend as much or more time working with the PLC programming software as you might Excel.

And the above exapmle is for autogenerating NEW code. For code that's already proven that I'm porting from one project to the next, all that's needed is the new I/O list, and the alarm logic is generated. (That's why I'm moving to VBA, and using Access instead of Excel. In theory, I will be able to enter the I/O list, establish some relationships (this input is the energized state feedback for that output; this phase runs that output on step x), push a button, and 80+% of the code will be done, along with 80+% of the documentation (both PLC annotation and funtional specifications, test plans, etc.)

This feature, being able to import ASCII text in RSLogix (and AI/APS/6200 before it) is what makes the AB PLC superior, in my opinion. I know of no other brand that can do this. (I don't want to turn this thread into another "My PLC can beat up your PLC, though, so please don't reply listing Rockwell's shortcomings. But if you know of another brand that does allow ASCII import of code, I'd like to hear it.)

And that's what I don't like about AB's Contrologix platform. You can't do partial imports - it's all or nothing. I know that Rockwell's working on it, and I understand the challenges (the need to define a tag before it can be used, but the undesirability of having multiple (conflicting) tag definitions when importing. I hope they get it soon.
 
Writing PLC code in ASCII

Allen Nelson said:

I know of no other brand that can do this. (I don't want to turn this thread into another "My PLC can beat up your PLC, though, so please don't reply listing Rockwell's shortcomings. But if you know of another brand that does allow ASCII import of code, I'd like to hear it.)

Ehh, since you brought it up...

My favorite can. In fact, you can write the whole PLC program in instruction list with any ASCII-editor.
(Except if you want to use the graphical editors for ladder/fupla or grafcet, but even grafcet can be done in an ASCII-editor)

Here is a samle program that can be viewed and edited in e.g. notepad and later assembled, linked (linked because the project can consist of several program files) and downloaded to the PLC as is.

Note: This is a modem testprogram not to be used in e.g. processcontrol because of all waitloops... (yes it is no-no)
 
But which brand is it???

Karl:

It makes sense that IL (or STL for that matter) can be programmed in ASCII. I've not programmed in those languages extensively, so they don't leap to mind.

But you don't say which brand your code is for!!!
 

Similar Topics

Hello, I m having a problem with wago 750-862 plc. The plc won't start automatically in run mode after a power reboot. I've also tried the...
Replies
4
Views
292
I have 32 masters in a linear topology network chain. I would prefer to use AL1xxx series from IFM. They do support device level ring if that...
Replies
15
Views
964
Hello all, I am looking for a way to have a user get logged out after an X amount of time because to default so that user privilages are no...
Replies
4
Views
504
Hello Guys, Can anyone share the Combined tube autopilot program If anybody worked with it.. we have newly installed this Auto pilot CT which is...
Replies
8
Views
1,348
Hi All, Does anybody know a way to periodically refresh the Alarm and Event Log Viewer in FactoryTalk View SE?
Replies
0
Views
586
Back
Top Bottom