Update Your Attribute Blocks Automatically

Change room location values in Microsoft Access, then use this AutoLISP routine to transfer the data into your AutoCAD drawing.

Kenneth Pettit e-mailed a request to take information from a Microsoft Access database and use that information to change AutoCAD attribute blocks. Kenneth has used the Automatic Attribute Extraction tool to export his attributes directly into Access. It's interesting that this is now possible to do without knowledge of the format or type of data that is being exported, and even a template file is no longer needed. Kenneth has a set of blocks that have attributes for the address and location of rooms in a building. The address codes have a format such as XX:A1-01, and they never change, but the room location values may change, and they are subject to updating in the drawing. The attributes for the room location may be updated in the Access database.

The AutoLISP Solution is ATTRIB-REPLACER.LSP, which uses a comma-delimited file exported from Microsoft Access. Any changes that have been made to the location field cause changes to be made to the corresponding attributes in the drawing. To test the code, I made a drawing with rooms and attributes for address codes and locations. I named the blocks containing the attributes ROOM-ID, although any block name can be used. The Microsoft Access table created by the Attribute Extraction tool is shown here (figure 1). The attribute values are Block name, Count, X insertion point, Y insertion point, LOCATION, and ADDRESS, as shown.

Figure 1. The Microsoft Access table created by the Attribute Extraction tool.

When the comma-delimited file is exported from Access (figure 2), it includes the headings. ATTRIB-REPLACER.LSP relies on the block name being in the first column, and the LOCATION and ADDRESS strings in columns 5 and 6, respectively. The other data in the table are not used, and there may be additional columns to the right of column 6.

Figure 2. After you export the comma-delimited file from Access, it includes the headings.

EXPORTING FILES FROM MICROSOFT ACCESS
The way to export the data from Access is to select the File menu, then Export?. The Export Table dialog box has an option at the bottom to Save As Type:, and you should scroll through the choices to find the Text Files type, then name the file in the File Name: box. You may then choose the Export All button, or if some rows have been selected, you may choose the Save Selection option. In either case, you will see the Export Wizard dialog box from which you may accept the default choice, Delimited, and click the Next button to choose the delimiter (a comma). Click Next, then Finish to save the file that looks like figure 2.

GET THE CODE
Download the ATTRIB-REPLACER.LSP file 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 ATTRIB-REPLACER.LSP
After you load the code, enter ATR to start the program. You will see the Text File From Access dialog box (figure 3). Navigate to where you exported the comma-delimited file from Access, and select it. After a short delay, the changes will be made to your drawing file. I used a modest drawing with 25 room location attributes (figure 4) to test the code, and the room locations were updated (figure 5).

Figure 3. The Text File From Access dialog box appears when you launch the new AutoLISP program.

Figure 4. My test drawing contains a modest 25 room location attributes.

Figure 5. The zoomed area reflects updated room locations.

The program tests each address against the others, meaning it has to perform a number of tests equal to the square of the number of rooms. For very large numbers, this may take a while. PROGRAMMING NOTES The program starts with my error handler and system variable manager that I have been using for more than ten years. I guess I should update it, but it still works. The main function is ATTRIBUTE-REPLACER that sets up the environment for the object-oriented or ActiveX functions. The following code is then used to collect and filter all the blocks with attributes into a selection set of objects:

(setq ssobj (vla-add ssets "selection1"))
 (vla-select ssobj acSelectionSetAll)
 (setq j (- 1))
 (setq InsertObjList nil)
 (repeat
 (vla-get-count ssobj)
 (setq item (vla-item ssobj (setq j (1+ j))))
 (if
 (and
 (= (vla-get-ObjectName item)
 "AcDbBlockReference"
 ) ;_ end of =
 (= (vla-get-HasAttributes item) :vlax-true)
 ) ;_ end of and
 (progn
 (setq InsertObjList
 (append InsertObjList (list item)))
 ) ;_ end of progn
 ) ;_ end of if
 ) ;_ end of repeat

I used entirely VLA- functions (methods) to create a list of insert objects with the test that the object name is AcDbBlockReference. To verify that the objects have attributes, the VLA-GET-HASATTRIBUTES method is used. Using this type of object oriented code has benefits downstream because as soon as the list of attributed block references is created, the following while loop is used to finish the job of replacing attributes:

(while (/= strx EOF)
 (setq strx (read-line f))
 (replace-attrib strx InsertObjList)
 ) ;_ end of while

The strx variable refers to each record from the comma-delimited file that is passed to the REPLACE-ATTRIB function along with the list of block reference objects.

REPLACE-ATTRIB starts by getting the first, fifth, and sixth fields from the database comma-delimited record for the Block Name, Location, and Address, respectively, as shown in this code fragment:

   (setq bname  (read (get-str str 1))
 location (read (get-str str 5))
 address (read (get-str str 6))
 ) ;_ end of setq

A repeat loop for the length of the list of block reference objects is used to process each object and to replace attribute values if necessary. Getting attributes with Visual LISP is easily done with the getAttributes method, as in the following:

    (setq attribs (vla-getAttributes obj))

The variable attribs contains all the attribute objects attached to the block reference, and they are tested in turn to see if the address matches that of the current record (str). If so, the location and address are collected. Finally, if the address is correct but the location does not match, the location is replaced with the new location in this code fragment:

(if (= address (cdr addrpair))
 (progn
 (if (/= location (cdr locpair))
 (progn
 (setq element (car locpair))
 (setq attrefobj
 (vlax-safearray-get-element sarr element))
 (vla-put-TextString attrefobj location)
 ) ;_ end of progn
 ) ;_ end of if
 ) ;_ end of progn
 ) ;_ end of if

Changing the attribute is done with the VLA-PUT-PROPERTY method as shown, in which the attribute value is the TextString property of the attribute object, and the new value is the LOCATION variable.

As always, keep those requests coming in, and good programming.