Adding a new option to the “Spreadsheet to PLC I/O” drawing generator tool – pre-defining wire numbers right in the spreadsheet.
This AutoCAD Electrical tool is pretty flexible. Using Excel, you lay out your control system’s I/O module requirements and out pops a set of 75-80% complete PLC I/O drawings.
For example, the first three lines of this example spreadsheet drives the AutoCAD Electrical Spreadsheet to PLC I/O utility to generate the circuitry shown below. But wouldn’t it be nice if we could pre-define the existing wire number assignments that tie into the two customer-supplied interlocks?
How to add this new feature
This is not too difficult. One thing that makes it possible is that this Spreadsheet to PLC I/O generator tool runs is an AutoLISP function that runs totally bare. It is supplied and runs in full-source mode, file name “wdio.lsp”. This means you are free to modify your copy of this tool.
The second thing that makes adding this new option easy-to-do is that the tool is already set up to support “piggy-backed” attribute assignments. For example, in the row of your spreadsheet for I/O point “I:003/10″ you’ve entered the block name for a N.O. temperature switch, block name “HTS11″. In the D3DESC column you’ve entered a description for this switch, “SLURRY HEAT READY”. But you’d also like to define the “RATING1″ value for this switch. But your spreadsheet has no column for RATING1 for the 3rd connected component. No problem. Just piggy-back the RATING1 assignment to the D3DESC value:
D3DESC value before: SLURRY HEAT READY
D3DESC value after: SLURRY HEAT READY;RATING1=95C
We’ll use this same idea to flag pre-defined wire number insertion on each side of a component. Instead of a name of an attribute, we’ll use these names: $WNL and $WNR for “wire number left side” and “wire number right side”.
So, let’s say that we want to pre-define wire number assignments “TS-100″ and “TS-101″ for this temperature switch in our spreadsheet. We’d enter this:
D3DESC value: SLURRY HEAT READY;RATING1=95C;$WNL=TS-100;$WNR=TS-101
So, that’s how we’ll set up our Excel spreadsheet to define wire number assignments. Now, the key piece is to modify the “wdio.lsp” file to be ready to recognize that a piggy-backed attribute name that is $WNL or $WNR is really a flag for a wire number insert.
Step 1 – find the wdio.lsp utility. A good way to do this is to type this at your AutoCAD Electrical “Command:” prompt
(c:ace_find_file “wdio.lsp” 3) [Enter]
If it can be found, the full path will display. Ignore the doubled-up back-slashes. It’s a Lisp thing.
Step 2 – MAKE BACK-UP COPY OF wdio.lsp. Important! Safety first.
Step 3 – Open wdio.lsp with any ASCII text editor or use AutoCAD’s Visual Lisp editor (type vlide [enter] at Command: prompt)
Step 4 – find this function within wdio.lsp file
Step 5 – Carefully cut and paste the code below to replace everything shown in the screenshot above.
; -- (defun wdio_write_out_accum_extra_attr_data ( en lst2 / atnam atval x xx hit_wen lstx) ; "lst2" = (list (list attrnam attrval) (list attrnam attrval) ...) (foreach x lst2 ; ** 06-Nov-09 NEHolt.begin, N8 Consultants LLC (cond ((wcmatch (strcase (car x)) "$WNL,$WNT,$WNR,$WNB") ; Look for special wire number flag ; masquerading as an attribute tag name. When found, the "attribute value" associated ; with it will be the desired fixed wire number value. ; The name of the flag indicates which "side" of the inserted component "en" that ; the wire number goes with. ; Get component's wire connection data (setq lstx (c:wd_get_sym_pntlst en 1 nil)) (setq hit_wen nil) (foreach xx lstx ; Going through each wire connection returned from the component that was ; just inserted (entity name "en"). Each sublist has the X?TERMxx wire connection ; direction (the "?" part) held in (nth 4...) element. If any wires tied to ; this connection, they are held in a list of entity names in (nth 3...) element. (cond ((/= hit_wen nil)) ; already processed this wire number, skip ((= (nth 3 xx) nil)) ; no wire connected to this component connection, keep looking ((AND (= (strcase (car x)) "$WNL") (= (nth 4 xx) 4)) ; connection direction is from the left ; Found wire connection on left side (wire connection direction "4") ; Push fixed wire number out to this wire connection's attached wire (setq hit_wen (car (nth 3 xx))) ; save wire entity name & exit loop ) ((AND (= (strcase (car x)) "$WNR") (= (nth 4 xx) 1)) ; connection direction is from the right ; Found wire connection on right side (wire connection direction "1") ; Push fixed wire number out to this wire connection's attached wire (setq hit_wen (car (nth 3 xx))) ; save wire entity name & exit loop ) ((AND (= (strcase (car x)) "$WNT") (= (nth 4 xx) 2)) ; connection direction is from above ; Found wire connection on top side (wire connection direction "2") ; Push fixed wire number out to this wire connection's attached wire (setq hit_wen (car (nth 3 xx))) ; save wire entity name & exit loop ) ((AND (= (strcase (car x)) "$WNB") (= (nth 4 xx) 8)) ; connection direction is from below ; Found wire connection on bottom (wire connection direction "8") ; Push fixed wire number out to this wire connection's attached wire (setq hit_wen (car (nth 3 xx))) ; save wire entity name & exit loop ) ) ) (if hit_wen (progn ; push the defined wire number out to wire entity "hit_wen". ; Insert it as a "fixed" wire number value. (c:wd_putwnf hit_wen (cadr x)) ) ) ) (T ; ** 06-Nov-09 NEHolt.end (if (AND (not (c:wd_modattrval en (car x) (cadr x) 1)) (/= (car x) "")) (progn ; target attribute name not found ; If it is the format of CATxx, MFGxx, CNTxx, ASSYCODExx, or WDBLKNAMxx then write out as XData (if (wcmatch (car x) "CAT??,MFG??,CNT??,ASSYCODE??,UM??,WDBLKNAM??") (progn ; write as XData (c:wd_upd_pnlval en (car x) (cadr x)) ) ) ) ) ) ) ) )
Step 6 – save your modified version of wdio.lsp.
Now, let’s test.
Here we’ve opened up the demo PLC spreadsheet demoplc.xls and added some references. We changed the terminal block names from HT0001 to HT1001 so that wire numbers change through them. We’ve added a couple customer symbol references. Finally, we added in our wire number flags to insert wire numbers “W1″, “W2″ on the customer limit switch and “W5″ and “W9″ for the N.C. relay contact.
Save spreadsheet. Exit out of Excel. Cross fingers and fire up the AutoCAD Electrical Spreadsheet –> PLC I/O utility. Reference the modified spreadhseet, and watch what happens…
A few things to keep in mind with this modification:
1. Remember that terminals come in two flavors… those that maintain a wire number assignment through the terminal on those that trigger a wire number change through them. You might need to use the latter.
2. If you put the “$WNL=<wire number>” or “$WNR=<wire number>” flag in an empty cell in your spreadsheet, precede it with a semi-colon (ex: empty cell contents becomes “;$WNL=12345″). This triggers the utility to treat this as a piggy-backed attribute.
3. For wires attaching from above or below, use flags $WNT and $WNB.
4. If these flag names don’t make sense or the “$” character is not one that shows up on your keyboard, adjust to suit your needs by editing the appropriate places in the pasted in Lisp code of step #5 above.
Advertisement – if your company is in need of AutoCAD Electrical customization, please visit http://n8consultants.com