×

AutoCAD

The Best Table In The House (Learning Curve AutoCAD Tutorial)

31 Aug, 2008 By: 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 repair the skimmer on his swimming pool, because the forecast for the next day was hot and sunny and he wanted to get the solar collector running again. Meanwhile, his dinner was getting cold on the kitchen table?

That's it! This month's topic!

This segue is going to hurt, isn't it?

Yes. This month's topic is a continuation of the August 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 began by running the DataLink command (Tools | Data Links | Data Link manager) which brought up the Data Link Manager dialog box shown in Figure 1:

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

Last month we just took the defaults, but we will come back to the options a little later.

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

We clicked on the From A Data Link radio button, clicked OK, and like magic a table was created in AutoCAD that mimicked the Excel spreadsheet. What was even more magical was that we saw that this is an active two-way link. If you edit the table from within AutoCAD then the changes reflect back to the spreadsheet and vice-versa.

Even better, AutoCAD doesn't care how the values got into the cells. We can use formulas, lookup tables, if-then statements, text string parsing and concatenation, cell values from other sheets, cell values from other Excel files, and any other Excel functionality.

Doing It in Style
So much for the review. Now let's go back and look at some of the options that we skipped over earlier. Using a perfectly logical sequence, we'll do this back to front by going back to the Insert Table dialog box shown in Figure 3.

The obvious first question is How do I use a different table style? The Table Style drop list is greyed out.

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

The second answer is that when creating a table from a spreadsheet, AutoCAD uses the current table style as set by the TableStyle command. Fortunately there is a quick trick so that you do not have to run the TableStyle command before creating a table from a spreadsheet. The trick is to select the table style from the drop list in the Insert Table dialog box before you tell it to use the data link. Click back and forth between Start From An Empty Table and From A Data Link to see what I mean.

One Giant Leap Backward
As promised, we will now move back a step to look at the options that are available when we create a data link. To do this, we need to look at Figure 2 again.

Starting from the top down, the options are pretty obvious from their titles:

• We can select a particular sheet from a multisheet Excel workbook file.

• We have already seen linking to the entire sheet.

• We can link to a named range, if any exist in the Excel file.

• We can link to an identified range. You type it in using the format of a letter, a number, a colon, a letter, and a number as shown in the example in the dialog box window. To do this, you have to know the range in advance because there is no way to go to the spreadsheet and simply 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 than contains anything, and not by the selected column or columns.

And Now For the Interesting Bits
There are a number of interesting bits related to AutoCAD-to-Excel linking, but most of them are so simple that all I need to do is to list them:

• We can have multiple data links in one drawing.

• We can have multiple links to the same spreadsheet in one drawing. For example, two different AutoCAD tables in one drawing can display different or even overlapping portions of the same spreadsheet.

• A single AutoCAD drawing can link to more than one spreadsheet.

• Many drawings can link to the same spreadsheet.

• Linked tables can be placed in model space, or in paper space layouts.

• We can place several copies of the same linked table in one drawing. For example, duplicates can be placed in several different layouts.

• If you often have the same links in many drawings, all you need to do is to set them up once and then save the drawing as a template file. All new drawings created from the same template will contain the same data links and/or linked tables.

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

And Now for Something a Little Bit Different
Two previous Learning Curve articles (December 2007 and January 2008) covered the subject of extracting data from drawing objects. This data can be used directly during the creation of an AutoCAD table, or it can be written out to an Excel spreadsheet.

Here is a common scenario. You have extracted data from drawing objects. You now want to massage and manipulate the data and then place it in an AutoCAD table back in the source drawing. Knowing what we now know, this should be easy enough. Simply extract the data to an Excel spreadsheet, edit it in Excel to add our additional data, and then create an AutoCAD table that is linked to the edited spreadsheet.

This seems to work at first, but it soon falls apart. The problem is that when you update the data extraction because drawing objects have changed, AutoCAD doesn't update the spreadsheet. Instead, it deletes it and then creates a new one with the same name, so your spreadsheet additions get lost.

There are two work-arounds for this situation.

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

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

First, extract the desired object data to a spreadsheet. For explanation purposes let's call it Sheet-1.

Next, use Excel to create a new spreadsheet that we will call Sheet-2. With both sheets open in Excel at the same time, it is quite easy to link cells in Sheet-2 to cells in Sheet-1, even though they are separate files. Simply select the target cell in Sheet-1, enter an = (equal) sign, switch to Sheet-2, click on the desired source cell, and press Enter. You can even add other equation functions right in the same target cell directly to manipulate the data obtained from the source cell.

Finally, create an AutoCAD table by establishing a data link to Sheet-2.

Now when you update the data extraction it will overwrite Sheet-1, but Sheet-2 will update to the new Sheet-1values and so the AutoCAD table will update correctly.

By combining AutoCAD's table, data extraction, and data linking capabilities, it is possible to easily build intelligent data tables in an AutoCAD drawing.

And Now for Something Completely Different
If you own, rent, or work around boats then the key rings for your car and your boat should each have a float attached to them so they won't sink if you drop them overboard. A fishing bobber is often quite satisfactory. Yes, it makes them a little bulkier in your pocket but it still beats the alternative. I have a summer ring and a winter ring for my car.