AutoLISP Solutions: Placing Notes in Paper Space Based on Attributes in a Model Space XREF

Combine attribute numbers from model space with keynotes from Excel on a paper space layout

This month's request comes from Fernando Salazar of Phoenix, Arizona. Fernando wants to use attribute numbers from an external reference in model space to place keynotes from an Excel file onto a selected paper space layout. The external reference is a floor plan similar to figure 1.

Figure 1. The external reference floor plan shown in model space.

The floor plan may include any combination of numbered items, and for each numbered item, a keynote is placed on a paper space layout. An Excel file originally contained the numbers and keynotes, as shown (figure 2).

Figure 2. The Excel file with numbers and keynotes.

This month's AutoLISP solution is KEYNOTE.LSP and the corresponding dialog box file KEYNOTE.DCL. The routine lets you select a comma-delimited file exported from Excel, and then specify a layout, with text height and line spacing, so you can place the list of keynotes at your selected location on the paper space layout.

Get the Code
Download the KEYNOTE.LSP and KEYNOTE.DCL files from Cadalyst's CAD Tips site. Save the file in AutoCAD's Support directory. Use the Appload facility by selecting Tools / Load Application, and then use the browser to select the file.

How to Use the Keynote Routine
After you load the LISP routine, the system prompts you to enter KN to start the program. To see this prompt, you may need to set your Command window size to three lines by dragging the Command window splitter bar appropriately. After you enter KN, you see the CSV file from Excel dialog box as shown (figure 3).

Figure 3. The CSV file from Excel dialog box.

Select the CSV file, and the Keynote Layout dialog box pops up (figure 4). Select the required layout on which the keynotes will be placed. You can also enter the text height and line spacing for the keynotes. The default values are 0.18 and 2 (double spacing).

Figure 4. The Keynote Layout dialog box.

After you select the layout, text height and line spacing, click OK. Then the prompt for the location (upper left) of the list of keynotes will appear (figure 5).

Figure 5. The keynotes list placed on the drawing layout.

Programming Notes
The program was written in AutoCAD 2004 and was tested in both AutoCAD 2004 and 2005, but it should work in any version of AutoCAD later than 2000. KEYNOTE.LSP starts as usual with my error handler and system variable functions. The function KEYNOTE is the main function that calls GET-CSV-FILE-KEYNOTES, XREF-VALLIST, GET-LAYOUT and WRITE-KEYNOTES.

GET-CSV-FILE-KEYNOTES opens the CSV file, uses a while loop to read each line in turn and creates a list of "dotted-pairs" of numbers and keynotes. The while loop is shown here:

  (while strx
 (setq strx (read-line f))
 (if	strx
 (progn
 (setq num (atoi (substr strx 1 3)))
 (setq note (get-str strx))
 (setq keynotelist (append keynotelist (
 list (cons num note))))
 ) ;_ end of progn
 ) ;_ end of if
 ) ;_ end of while

Note that the numbers are taken directly from the current line of the CSV file, but the notes are returned by GET-STR before being collected together as a dotted pair by the CONS function. It is convenient to use dotted pairs, because the notes can be extracted from the keynote list later by using the standard ASSOC function. The GET-STR function uses a while loop to find the first comma in the string as follows:

      (while (/= char comma)
 (setq char (substr str (setq i (1+ i)) 1))
 ) ;_ end of while
 (setq str1 (substr str (1+ i)))

Originally, I assumed that I could return the remainder of the line as STR1 and that it would be identical to the required keynote. However, because the keynotes may contain commas, extra double quotes appeared in the keynotes to designate more than one string. To deal with these, I eliminated any unwanted double quotes. This turned out to be complicated because some double quotes represent inch units. I used the following conditional statements to get the desired results:

    (repeat (1- len)
 (setq char1 (substr str1 (setq i (1+ i)) 1))
 (setq char2 (substr str1 (setq i (1+ i)) 1))
 (cond
 ((and (/= char1 dq) (/= char2 dq))
 (setq str2 (strcat str2 char1 char2))
 )
 ((and (= char1 dq) (= char2 dq))
 (setq str2 (strcat str2 char1))
 )
 ((and (/= char1 dq) (= char2 dq) (= i len))
 (setq str2 (strcat str2 char1))
 )
 ((and (/= char1 dq) (= char2 dq) (/= i len))
 (setq str2 (strcat str2 char1 char2))
 )
 ((and (= char1 dq) (/= char2 dq))
 (setq str2 (strcat str2 char2))
 )
 (T (setq str2 (strcat str2 char1 char2)))
 ) ;_ end of cond
 ) ;_ end of repeat

In this code fragment, two characters are read from the string, and five conditions were tested: neither character was a double quote, both characters were double quotes, only the first character was a double quote or only the second character was a double quote. Additionally, if the double quote was the last character in the string, it was eliminated. Even though it leads to these complications, the comma-delimited format is a better option than a tab- or space-delimited format because the Excel file text may contain any number of spaces.

Extracting the attributes from the external reference demanded that the drawing should be set to model space, and that is done using ActiveX objects. It is interesting that the function (LayoutList) returns a list of all of the layouts except the Model layout, whereas the (vla-get-layouts) method returns the entire layout collection, including the Model layout. I used this code to set the Model tab as the current layout:

  (setq LayoutCollection (vla-get-layouts *thisdrawing*)
 j                (- 1)
 ) ;_ end of setq
 (repeat
 (vla-get-count LayoutCollection)
 (setq Layout (vla-item LayoutCollection
 (setq j (1+ j))))
 (if (= (vla-get-name Layout) "Model")
 (progn
 (setq *layout* Layout)
 (vla-put-activelayout *thisdrawing* *layout*)
 ) ;_ end of progn
 ) ;_ end of if
 ) ;_ end of repeat

I used a similar technique later to set the required paper space layout tab as the current one for writing the keynotes.

External references save drawing file space by retaining a pointer to the external reference, which is then resolved when the drawing is opened. Therefore, all of the information about the external reference is self-contained within the open drawing. That data is stored in the BLOCKS table, along with any component objects and their attributes. I used the TBLSEARCH function to find the BLOCK table, and then the ENTNEXT function to step through the database looking for all of the attribute objects that met the criterion of being on the layer reserved for the keynote numbers. External references introduce new layers that are named for the original layer used in the external file, and preceding that layer name with the name of the external reference drawing plus the delimiter (|). Here is a code fragment that I used to filter the appropriate set of objects containing the keynote numbers:

	(if
 (and
 (= (vla-get-ObjectName entobj)
 "AcDbBlockReference"
 ) ;_ end of =
 (= (vla-get-Layer entobj)
 (strcat sym "|" "A-FLOR-NOTE")
 ) ;_ end of =
 (= (vla-get-HasAttributes entobj) :vlax-true)
 ) ;_ end of and
 (progn
 (setq InsertObjList (append InsertObjList
 (list entobj)))
 ) ;_ end of progn
 ) ;_ end of if

After the list of insert objects with attributes was collected, it was sorted so that only one occurrence of each was listed using the (vl-sort) method. Finally, the keynotes were written by the WRITE-KEYNOTES function that contains the following repeat loop:

  (repeat (length IDlist)
 (setq ID  (car IDlist)
 txt (cdr (assoc ID csv-data))
 p2  (polar p 0.0 (* (atof *txtheight*) 4.0))
 ) ;_ end of setq
 (vla-AddMtext *paperspace* (vlax-3D-point p)
 mt-width (itoa ID))
 (vla-AddMtext *paperspace* (vlax-3D-point p2)
 mt-width txt)
 (setq IDlist (cdr IDlist))
 (setq p (polar p (* pi 1.5) (*
 (atof *lspacing*) (atof *txtheight*))))
 ) ;_ end of repeat

Note the use of the (vla-AddMtext) method because the example that Fernando sent me contained Mtext rather than the single line text.

As always, I look forward to receiving your requests for AutoLISP Solutions. Contact me using the links below.