My post titled “Quickly Link Excel Tables to AutoCAD” from last August continues to be one of my most popular posts to date. As a result I have received a number of e-mails from readers such as you asking about other ways to link Excel data with AutoCAD data. A popular variation has been asking about a way in which you can link Excel data with AutoCAD Block Attributes. Since Block Attributes function much like a database form, it seems logical that we should be able to use Excel as a data source for those Block Attributes. Sadly there is not a true “Link Block to Excel” command (to my knowledge) within AutoCAD. That however does not mean there’s no workaround to the situation!
I originally developed this workaround back in the days before Sheet Set Manager. At the time I was working on a two-part project, each of which consisted of more than 100 sheets. The kicker was that we had to include the station range of the alignment being illustrated on each plan and profile sheet. Of course, as luck would have it, our road alignments were constantly changing in the early phases of design, consequently making our sheet titles incorrect. I thought to myself – there has to be a better way!
After a little digging around I found two Express Tool Commands which caught my eye; ATTIN and ATTOUT. Using these commands in conjunction with one another I was able to export a text file containing my attribute values, bring that text file into Excel to change and manage values, finally importing that text file back into AutoCAD. Admittedly the process is a little rough around the edges, but at the end of the day – it works!
Staging your Blocks
The first and arguably most critical element to this procedure is the Attribute Blocks their self. Every object we draw and/or insert into an AutoCAD drawing has what is called a handle. Looking at a DWG file as a database, an objects handle is effectively its unique identifier, separating it from any other object within the current drawing. I mention this detail because the ATTOUT command records this unique object handle to the text file it exports. It’s by this unique handle the ATTIN command knows which instance of a given block to update.
That said you will want to first insert your blocks into your drawing(s). Back when I used this method for managing sheets, I would create my various layout tabs, inserting my attributed sheet title block into each respective layout tab. From that point on I made it a point to NEVER ERASE THAT BLOCK FROM THE DRAWING. Since AutoCAD sees any block insertion as a new object, it will consequently create a new handle. Consequently, the ATTIN command will only work if a handle in your Attribute Extraction Text File matches with a handle in the current drawing.
Creating your Export Files
With your block insertions made, you’re ready to begin exporting their attribute values. You can do that by using the ATTOUT command, which can also be launched by going to Express > Blocks > Export Attribute Information. Upon launching the command, simply select on the attributed blocks you wish to include in your export. Finally, after selecting the desired blocks, you’ll be prompted for a location to save the TXT export file.
Working in Excel
After creating your Block Attribute Export File(s), you’re ready to begin importing things into Excel. Getting the TXT file into Excel is rather straight forward. Using Excel 2007…
1. Go to the Data tab, and select “From Text”
2. Browse to the location of your Block Attribute Export File, and click [Import]
3. The Block Attribute Export File is a Tab Delimited TXT file. Consequently you’ll want to select “Delimited” from the resulting dialog box.
4. In Step 2 of the Text Import Wizard, select “Tab” as the delimiter type.
Your Block Attribute Data will be structured into a familiar table format. From this point you can keep things simple, or make them as complex as you desire. For instance, you may wish to use the table created in the above as more of a data source, using subsequent tables to further refine the data. Additional tables may perform calculations, or even make basic formatting adjustments. Whatever you choose to do, just be sure your final table subscribes to the same format as the original Block Attribute Export File.
To create an updated Block Attribute File:
1. Click on the Office Button, select Save As > Other Formats.
2. From the Save As dialog, change the “Save as type” to “Text (Tab delimited) (*.txt).
Updating AutoCAD Blocks
You have made it to the homestretch, and only one command away from updating your Attributed Blocks. Use the ATTIN command, which is also available from Express > Blocks > Import Attribute Information, to update your block insertions. After invoking the command, you’ll be prompted to browse to wherever you saved your updated TXT file to from within Excel. As soon as you press [Open] AutoCAD will update the attributes of any block for which it finds a matching handle.
Admittedly the procedure isn’t as eloquent as we may want it to be. Perhaps Autodesk will include a more dynamic way of achieving this in a future release of the software. Until then, this procedure is the best I have been able to work up!
