The Best Table in the House (AutoCAD Learning Curve Tutorial)

August 31, 2008

From: Bill Fane

Part two on linking AutoCAD tables to Excel spreadsheets.

It was late on a dark and stormy afternoon. Captain LearnCurve was frantically trying to fix his pool skimmer because the forecast for the next day was hot and sunny and he wanted to get the solar collector working again. Meanwhile, his dinner was cooling on the kitchen table?

That’s it! Topic of the month!

This sequel is going to hurt, isn’t it?

Yes. This month’s topic is a continuation of August’s column on how to connect an Excel spreadsheet to an AutoCAD 2008 table.

Last time we saw that it is a simple two-step process. We started by running the DataLink command (Tools | Data Links | Data Link manager) which brought up the Data Link Manager dialog shown in Figure 1:

Figure 1: The Data Link Manager dialog is used to link an AutoCAD drawing to an Excel file.

This led to the New Excel Data Link dialog box (Figure 2):

Figure 2: We are now linked to our desired spreadsheet.

Last month we just took the defaults, but we’ll come back to the options a bit later.

The second step was to use the Table command (Draw | Table). It brought up the Insert Table dialog box (Figure 3).

Figure 3: The Insert Table dialog has been linked to our spreadsheet.

We clicked the From Data Link radio button, clicked OK, and magically a table was created in AutoCAD that mimicked the Excel spreadsheet. What was even more magical was that we saw that it was an active two-way link. If you edit the table from AutoCAD, the changes are reflected in the spreadsheet and vice versa.

Best of all, AutoCAD doesn’t care how values ​​are entered into cells. We can use formulas, lookup tables, if-then statements, parsing and concatenation of text strings, cell values ​​from other sheets, cell values ​​from other Excel files and any other functionality Excel.

Do it in style
That’s it for the exam. Now let’s step back and look at some of the options we overlooked earlier. Using a perfectly logical sequence, we’ll do it in reverse by returning to the Insert Table dialog box shown in Figure 3.

The first obvious question is How can I use a different table style? The Table Style drop-down list is grayed out.

There are two answers to this question. The first obvious answer is that if there is only one table style defined in the drawing, that is the one that will be used. On the other hand, a little experimentation will reveal that it is still greyed out even though there are other styles available.

The second answer is that when creating a table from a spreadsheet, AutoCAD uses the current table style defined by the TableStyle command. Luckily, there’s a quick trick so you don’t have to run the TableStyle command before creating a table from a spreadsheet. The trick is to select the table style from the dropdown in the Insert Table dialog before you tell it to use data binding. Click back and forth between Start from empty table and From data binding to see what I mean.

A giant step back
As promised, we’ll now step back to look at the options available when we create a data binding. To do this, we need to revisit Figure 2.

Going from top to bottom, the options are pretty obvious from their titles:

  • We can select a particular sheet from a multi-sheet Excel workbook file.
  • We have already seen a link to the full sheet.
  • We can link to a named range, if there is one in the Excel file.
  • We may link to an identified range. You type it using the format of a letter, number, colon, letter, and number, as shown in the dialog window example. To do this, you need to know the range in advance because there is no way to go to the worksheet and just select it.
    • To link to a single cell, enter its coordinates twice as in C3:C3.
    • You can link to an entire column (B:B) or a range of columns (B:D). The length of a column is determined by the highest numbered row anywhere in the sheet that contains anything, not the selected column(s).

And now for the interesting bits
There are a number of interesting bits related to linking AutoCAD to Excel, but most of them are so simple that all I have to do is list them:

  • We can have multiple data links in a drawing.
  • We can have multiple links to the same worksheet in a drawing. For example, two different AutoCAD tables in a drawing may show different or even overlapping parts of the same worksheet.
  • A single AutoCAD drawing can be linked to multiple spreadsheets.
  • Many drawings can be linked to the same worksheet.
  • Linked tables can be placed in model space or in paper space layouts.
  • We can place multiple copies of the same linked array in a drawing. For example, duplicates can be placed in several different layouts.
  • If you often have the same links in many drawings, you only need to set them up once and then save the drawing as a template file. All new drawings created from the same model will contain the same data links and/or linked tables.

And the lumpy piece
One item in particular bears repeating from last month’s article. Linked spreadsheets are a bit like XREFs with one difference. The similarity is that they are separate files. The difference is that if you move or rename the worksheet, AutoCAD won’t complain, at least not too loudly. The drawing table remembers the last worksheet state it saw. You won’t know you have a problem until you try to update the data, then all you get is a terse note at the Command: prompt telling you that the update failed.

And now for something a little different
Two previous Learning Curve articles (December 2007 and January 2008) dealt with the topic of extracting data from drawn objects. This data can be used directly when creating an AutoCAD table or written to an Excel spreadsheet.

Here is a common scenario. You have extracted drawing object data. Now you want to massage and manipulate the data and then place it into an AutoCAD table in the source drawing. Knowing what we know now, that should be pretty easy. Simply extract the data into an Excel spreadsheet, modify it in Excel to add our additional data, then create an AutoCAD table linked to the modified spreadsheet.

It seems to work at first, but it quickly falls apart. The problem is that when you update the data extract because the drawing objects have changed, AutoCAD does not update the spreadsheet. Instead, it deletes it, then creates a new one with the same name, so your additions to the spreadsheet get lost.

There are two workarounds for this situation.

If your additional calculations are relatively simple, you just need to extract the data directly into an AutoCAD table without going through a spreadsheet. You can now add additional columns to the table, the contents of which can include simple formulas based on the contents of the extracted cells. Everything updates properly.

If your calculations are relatively complex, follow these simple steps.

First, extract the desired object data into a spreadsheet. For the sake of explanation, let’s call it Sheet-1.

Next, use Excel to create a new worksheet which we will call Sheet-2. With both sheets open in Excel at the same time, it’s pretty easy to link cells on Sheet-2 to cells on Sheet-1, even if they’re separate files. Simply select the target cell in Sheet-1, enter an = (equals) sign, switch to Sheet-2, click on the desired source cell, and press Enter. You can even add other equation functions directly in the same target cell to manipulate the data obtained from the source cell.

Finally, create an AutoCAD table by data linking to Sheet-2.

Now when you update the data extract it will overwrite Sheet-1, but Sheet-2 will be updated with the new Sheet-1 values ​​and so the AutoCAD table will update correctly.

By combining AutoCAD’s table, data extraction, and data link functions, it is possible to easily create intelligent data tables in an AutoCAD drawing.

And now for something completely different
If you own, rent, or work around boats, your car and boat key rings should each have a float so they won’t sink if you drop them overboard. A fishing bobber is often quite satisfactory. Yes, that makes them a bit bulkier in your pocket, but it still beats the alternative. I have a summer ring and a winter ring for my car.