Your Table is Waiting (Learning Curve AutoCAD Tutorial)

You can link spreadsheet data to your AutoCAD files — and vice versa.

Don't you mean DMV, as in Department of Motor Vehicles?

Nope. DMZ, as in the De-Militarized Zone between North and South Korea. He was heading for the Third Intrusion Tunnel that the North was caught digging into the South.

What on earth was he doing there?

BCIT, the institute for whom he used to teach.

Used to?

Yes. He retired effective June 30.

Anyway, he was on an inspection tour of the engineering school in Daegu with which BCIT has links?

That's it! This month's topic! Links!

In previous articles, Tell Me All You Know: Attribute Extraction, Part 1 and Tell Me All You Know and Even More, I discussed data extraction. One of the options I mentioned was the fact that the data can be exported in the format of an Excel spreadsheet, to which my regular reader Aaron responded by saying, "Wouldn't it be nice if this worked the other way around? I would like to be able to link AutoCAD to data in a spreadsheet."

Be Careful What You Wish For
AutoCAD 2008 now allows for that exact functionality. Yes, I know AutoCAD has supported OLE (Object Linking and Embedding) for some time now, but its operation can be flakey on a good day, to put it mildly. Instead of OLE, this month we are going to explore some of the new data linking capabilities in AutoCAD 2008.

Let's start with a simple example. Create an Excel spreadsheet that looks something like this:

Start with a simple Excel spreadsheet.

In this spreadsheet, cell C2 contains the simple formula =A2*B2. This is repeated for cells C3 and C4. The value in C6 is =sum(C2-C4). Save this as Example 1.xls in a suitable folder.

Now go to an AutoCAD drawing and start the DataLink command (Tools / Data Links / Data Link manager). This brings up the Data Link Manager dialog box as shown here:

The Data Link Manager dialog box is used to link an AutoCAD drawing to an Excel file.

Click on the Create a new Excel data link item, and name it Costs.

Click on the three-dot button in the New Excel Data Link dialog box and browse to and select your Example 1.xls spreadsheet file.

The New Excel Data Link dialog box (shown below) appears.

We are now linked to our desired spreadsheet.

Make sure the Link Entire Sheet and Preview buttons are selected, and then click OK enough times to get back to the Command: prompt in your drawing.

Times Tables
We now come to the fun part. Create a new table in AutoCAD, using the Table command (Draw / Table). This brings up the Insert Table dialog box that looks like this:

The Insert Table dialog box has been linked to our spreadsheet.

Select the From a Data Link button, and then select Costs from the dropdown list. Note how AutoCAD previews your specific spreadsheet.

Click OK. The dialog box closes, and you are invited to select an insertion point for the table. When you do, AutoCAD inserts a table into your drawing that looks almost exactly like the Excel spreadsheet. It is in fact linked to the spreadsheet, sort of like an xref. I'll come back to "sort of" a little later.

Turning The Tables
Ah, but now comes the real fun. Open your Example 1 spreadsheet and change the value in one or more of the column A and/or column B cells. The column C cells update as expected. Save your changes.

Now go back to your AutoCAD drawing and start the DataLinkUpdate command in its Update mode (Tools / Data Links / Update data Links). Select the table and then press Enter. After a brief pause the AutoCAD table will update to reflect the changes to the spreadsheet. Now that's magic!

You can do this as often as you want within an editing session, but don't forget that you must save your Excel changes before AutoCAD will update. AutoCAD reads from the file on disk, not the copy in memory in Excel.

Go Back! Go Back!
Click on a cell in the AutoCAD table, such as A4. The appearance of the table changes and the table editor toolbar appears, but there are two additions. The linked portion of the table has blue corners to indicate that it is linked. In our current case this includes the full table.

In addition, a double padlock-and-chain glyph appears at the cursor to indicate that the selected cell is locked and linked.

Click on the Locking button near the center of the toolbar, and then select Unlocked from the context menu that appears. The padlock portion of the glyph goes away.

Double-click on the now-unlocked cell. The normal Text Formatting toolbar appears. Change the value in the selected cell, and then click OK. AutoCAD returns to the Command: prompt, but only the selected cell has changed.

Start the DataLinkUpdate command in its Write mode (Tools / Data Links / Write Data Links). Select the table and press Enter. AutoCAD should advise you that one link was updated successfully, and a notifier balloon appears in the lower right corner of the screen. Click on the Update Tables.. link in the balloon and watch in shock and awe as the AutoCAD table updates to show the correct calculated values in the cells that are dependent on the revised one.

Now go back to Excel and open your spreadsheet again. Surprise! It has updated to show the values that you wrote out from AutoCAD!

Things just keep getting better and better. Not only is it now possible to read Excel spreadsheet data into an AutoCAD table, but AutoCAD table revisions can also be pushed back into the same spreadsheet.

The Interesting Bits
There are a number of interesting bits to this functionality, but here are a few of the main ones for starters.

Obviously, Microsoft Excel must be installed on the same computer as AutoCAD for any of this to work.

Earlier I said "Sort of like an xref," because there is a significant difference. If you move or rename a normal xref drawing, the next time you open the host it won't be able to find the xref. On the other hand, a table linked to an Excel spreadsheet remembers how the spreadsheet looked the last time it saw it. You won't realize that there is a problem until you attempt to update the linked data, and then all you get is a quick note at the Command: line indicating that the update was unsuccessful.

Okay, actually there are so many interesting bits to this functionality that it is going to take two or three more articles to begin to cover them. Come back next time to see what comes up.

And Now For Something Completely Different
When you visit Korea, be sure to take clean socks without holes in them, and slip-on shoes without laces. Their standard style of eating is to leave your shoes at the door and then to sit cross-legged on the floor at a very low table. This applies almost universally, from the traditional high-end restaurants through to the local lunch cafes.