Nate Holt's Blog

November 19, 2009

Overview of Simple BOM reporting – AutoCAD Electrical

Filed under: Electrical, Tutorials — nateholt @ 1:20 am

Brief overview of how two different databases are queried to create various AutoCAD Electrical BOM reports.

This question comes up from time to time, most recently today. Here is a brief summary in the form of screenshots illustrating how a component on an AutoCAD Electrical drawing (a mechanical footprint symbol on a panel layout drawing) finds its way on to a BOM report. This illustrates just a simple, straightforward catalog assignment – without “ASSYCODE” and without “Multi-BOM” catalog assignments (future posting!).

A small portion of one drawing, DEMO08.dwg, which is part of a sample AutoCAD Electrical project drawing set “WDDEMO”, is shown below.

AutoCAD Electrical maintains a “scratch” database file, in Microsoft Access format, that follows what is going on in the project drawing set (it does not drive it, it follows it). The above mechanical footprint component, SS433 on drawing DEMO08.dwg of the project, shows up in the “PNLCOMP” table of the scratch database file for this project.

 The scratch database file name is the same name as the active AutoCAD Electrical project name but with an “.mdb” extension. It is saved in the local AutoCAD Electrical “user” folder. The full name can be displayed by typing this at the AutoCAD “Command:” prompt.

 Command:  (c:wd_mdb_get_proj_scratch_dbnam nil) [Enter]

 Here is a screenshot of the above panel component in the PNLCOMP table of the project scratch database:

And here’s another screenshot of the above record with a few other fields exposed. These fields are used to guide AutoCAD Electrical’s BOM reporting command to the appropriate “table” in the catalog lookup database file DEFAULT_CAT.mdb (shown shortly).

The actual table name that the BOM report queries for this “SS433” symbol’s catalog description information is influenced by the WDBLKNAM value shown above. See this posting for a description of how auto-table selection works.

Ultimately, the BOM reporting goes to table “SS” in the DEFAULT_CAT.mdb file. It uses the MFG/CAT attribute values carried on the panel footprint symbol (and extracted into the PNLCOMP table shown above) to find the appropriate record in the “SS” table shown here:

And now the BOM reporting command merges the data in the record above with the data from the scratch database file. It is formatted into the report and shows up as an entry here in the BOM Report Generator dialog ( ! ):

Now, at this point, you can write the report out to a file (in various formats) or insert the report as a “smart” table on to your AutoCAD drawing.


November 12, 2009

Enhanced wire number tags based on connected components – AutoCAD Electrical

Filed under: Electrical — nateholt @ 8:45 pm

Wire number tags based upon connected component tag-ID at each end.

Some time ago we had this posting that dealt with a wire numbering tool utility. It would generate a wire number tag based upon combining the tag-ID and termination pin number of the component connection at each end of the wire. This generated explicit but really “long” wire numbers.

Here’s an example:


Yes, really long wire numbers ( ! )

Request for a Simplified Version

A user in Australia wanted to do this but make the tags shorter. He just wanted to concatenate the two tag-IDs and then add an alpha suffix to keep each wire number unique. No connection pin numbers embedded in the wire number.

For example, in the above drawing, the upper right-hand wire number would be the two device tags jammed together, “SS406TD406”, and an alpha sufffix, let’s say “A”.

Now there are two more wires that tie the same two devices together: the right-hand wire leaving the two child contacts on the selector switch. Both will be “SS406TD406”. So the utility would need to add a “B” and a “C” to each respectively to keep the wire numbers unique.

Modifying the existing milnum_alldwg.lsp utility

So, let’s start with the previous post’s lsp utility, copy it to a new file name, “milnumx_alldwg.lsp”. Open it up in AutoCAD’s Visual Lisp editor (type vlide [enter] at the “Command:” prompt) or open in any ASCII text editor (like WordPad.exe) and let’s have some fun.

Step 1 – this is the easiest part. We want to bypass the original utility’s task of adding in the connection pin numbers. Here is the before and after code:


                    (if (= flipit 1)
                      (progn ; reverse the order
                        (setq new_wireno comp2_tag)
                        (if (AND pin2 (/= pin2 ""))(setq new_wireno (strcat new_wireno "-" pin2)))
                        (setq new_wireno (strcat new_wireno "/" comp1_tag))
                        (if (AND pin1 (/= pin1 ""))(setq new_wireno (strcat new_wireno "-" pin1)))
                    ; ELSE
                      (progn ; normal order of wire number format                  
                        (setq new_wireno comp1_tag)
                        (if (AND pin1 (/= pin1 ""))(setq new_wireno (strcat new_wireno "-" pin1)))
                        (setq new_wireno (strcat new_wireno "/" comp2_tag))
                        (if (AND pin2 (/= pin2 ""))(setq new_wireno (strcat new_wireno "-" pin2)))
                    ) )   
                    (c:wd_putwnf en new_wireno) ; put the wire number on wire
                    (if (= flipit 1)
                      (progn ; reverse the order
                        (setq new_wireno (strcat comp2_tag comp1_tag)) ; just jam two tags together, no delimiter
                    ; ELSE
                      (progn ; normal order of wire number format                   
                        (setq new_wireno (strcat comp2_tag comp1_tag)) ; just jam two tags together, no delimiter
                    )  )  
                    (c:wd_putwnf en new_wireno) ; put the wire number on wire

Step 2 – the harder part. Okay, we have the base wire number figured out above and pushed into lisp variable “new_wireno”. But we need to check each wire number and make sure it is unique. We add an incrementing alpha suffix to the wire number until it is unique.

This means that we need to first extract a list of all existing wire numbers on the active drawing (could do for all dwgs in project but that will be an upcoming blog posting). Then, when we generate a new wire number, we compare against the existing wire number list and, if a repeat found, keep incrementing the suffix for our new wire number until we find one that isn’t already in the list.

So, here’s a little subroutine we’ll add to our milnumx_alldwg.lsp utility. It runs at the front end of our utility to establish a list of all existing wire number assignments.

Here’s how it works… it creates a “selection set” of all wire number block inserts found on the active drawing. AutoCAD Electrical wire numbers have block names that begin with either “WD_WNH” or WD_WNV”. So the “ssget” line below gets all instances of wire number block inserts on the active drawing.

Then we cycle through this list, one at a time, and get each wire number block insert instance, the “(setq en (ssname sswn ixwn))” line below. Then we get the wire number attribute value, WIRENO or WIRENOF. Add to the running list of wire numbers, loop back to the top of the loop, and process the next wire number block insert instance. When done, all of the drawing’s wire numbers are sitting in AutoLISP variable “lst”. This data is returned from this function.


  (defun milnumx_get_all_existing_wirenums ( / sswn ixwn slenwn lst )
    ; Extract a list of all wire numbers found on the active drawing. Create a selection
    ; set of all block insert instances that have block name "WD_WNH*" or "WD_WNV*". There
    ; are AutoCAD Electrical wire number block names.
    (setq lst nil)
    (setq sswn (ssget "_X" '((-4 . "<AND")(0 . "INSERT")(2 . "WD_WNH*,WD_WNV*")(-4 . "AND>"))))
    (if (/= sswn nil)
      (progn ; some wire number block instances found. Process them.
        (setq slenwn (sslength sswn)) ; number of block instances found
        (setq ixwn 0) ; this will be used to index through the blocks
        (while (< ixwn slenwn)
          (setq en (ssname sswn ixwn)) ; get next block instance entity name
          ; Get WIRENO* attribute value off of this wire number block insert
          (setq wn (c:wd_getattrval en "WIRENO*"))
          (if (AND wn (/= wn "") (not (member wn lst)))
            (progn ; add to the running list
              (setq lst (cons wn lst))
          ) )
          (setq ixwn (1+ ixwn))
    ) ) )
    (setq sswn nil) ; release the selection set
    lst ; return the list of existing wire numbers found on active drawing

Step 3 – Putting it all together. Now we add a call to our wire number collection function to capture a list of all wire numbers and dump into AutoLISP variable name “existing_wnum_list”. We do this toward the beginning of the utility as shown here:

  ; -- main routine starts here --                  

  ; Two options here. Uncomment first two lines and uncomment third line to prompt
  ; user to "window" what wires to process. Or, comment out the first two lines and
  ; uncomment the third line to process all wires on active drawing without any
  ; user prompts.

;;;  (princ "\nSelect wire or wires to process for special source/dest wire number format")
;;;  (setq ss (ssget '((0 . "LINE")))) ; user windows what to process
  (setq ss (ssget "_X" '((0 . "LINE")))) ; automatically process all wires on active dwg

  (if (/= ss nil)

      ; Get list of all existing wire numbers
      (setq existing_wnum_list (milnumx_get_all_existing_wirenums)) ; ** 12-Nov-09 NEHolt
      (setq slen (sslength ss)) ; number of LINE entities selected
      (setq lines_processed_entlist nil) ; used to track what has been processed
      (setq ix 0) ; used to index through the selection set
      (while (< ix slen)

… and we go back to what we did in Step 1. We add a “foreach” loop just below the point where we have generated the raw “component1-component2” tag. We want to find the next suffix character that keeps this proposed wire number unique.

                    (if (= flipit 1)
                      (progn ; reverse the order
                        (setq new_wireno (strcat comp2_tag comp1_tag)) ; just jam two tags together, no delimiter
                    ; ELSE
                      (progn ; normal order of wire number format                  
                        (setq new_wireno (strcat comp1_tag comp2_tag)) ; just jam two tags together, no delimiter                       
                    ) )   
                    ; Now add an incrementing alpha suffix to the wire number until
                    ; a unique wire number is found (one that does not appear to have
                    ; already been used elsewhere on this drawing).
                    ; "existing_wnum_list" is the list of existing wire numbers
                    ; collected for the active drawing.

                    (setq alphalst (list "A" "B" "C" "D" "E" "F" "G" "H" "J" "K" "L" "M" "N"
                                         "P" "R" "S" "T" "U" "V" "W" "X" "Y" "Z" "A1" "A2"))
                    (setq hit nil)
                    (foreach suffix alphalst
                      (if (not hit)
                        (progn ; don't have a unique number yet. Try this one.
                          (setq x (strcat new_wireno suffix))
                          (if (not (member x existing_wnum_list))
                            (progn ; Yes, found next available suffix that yields a
                                   ; unique wire number!
                              ; Add this new wire number to the running list
                              (setq existing_wnum_list (cons x existing_wnum_list))
                              (setq hit T) ; to bypass the rest of this "foreach" loop
                              (setq new_wireno x)
                          ) )
                    ) ) )                                        
                    (if (not hit)
                      (progn ; didn't find a unique wire number suffix. Add "???"
                        (setq new_wireno (strcat new_wireno "???"))
                    ) )
                    (c:wd_putwnf en new_wireno) ; put the wire number on wire

There it is. Let’s test. Save the above file. APPLOAD it. Then type MILNUM_ALLDWG [Enter] at the Command: prompt.


Download full file here

UPDATE: found typo in the uploaded file (13-Nov-2009). Fixed and reposted.

November 6, 2009

PLC I/O Drawing Generator – Pre-defining wire numbers – AutoCAD Electrical

Filed under: Electrical — nateholt @ 11:02 am

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:



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:


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     
        ((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.
              ((/= 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))             
        ) ) )    
; ** 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

Create a free website or blog at