×

AutoCAD

Link It! (Circles and Lines AutoCAD Tutorial)

30 Jun, 2007 By: Lynn Allen

---

AutoCAD 2008 offers true reciprocal links with Excel tables.

For years, we've been able to insert an Excel Spreadsheet as a linked OLE object so that as an Excel spreadsheet updates, so does the OLE object. But OLE objects are limited in their capabilities and aren't true AutoCAD entities. You can scale, rotate and move an OLE object as a whole, but editing the contents in AutoCAD is impossible and getting the proper text height is a total guessing game. With the addition of AutoCAD tables, several releases ago, hope sprung up that we could get the same results by inserting an Excel file as an AutoCAD table.

This wasn't the case, because these tables had low I.Q.'s. Once inserted, they never referenced back to the original Excel file, so if the Excel file changed, you had to erase the previously inserted AutoCAD table and reinsert an updated Excel file. This process was tedious at best. AUGI members from around the world requested a true link between an Excel spreadsheet and an AutoCAD table, and they added this request to the famous AutoCAD wish list. Finally, this wish has been granted!

AutoCAD 2008 brings with it the unexpected surprise of truly linked AutoCAD tables. You can insert an Excel spreadsheet into AutoCAD as a table object, and as the Excel spreadsheet is modified and updated, so is the AutoCAD table! But, wait! There's more! You also can update this same table inside AutoCAD and have it update the Excel spreadsheet too (if desired). It just doesn't get much better than that! Let's take a look.

Ready, Set, Link!
To begin, we have our Excel file. Simply highlight the cells you want to bring into AutoCAD and copy those cells to the clipboard. Then, you're ready to paste the spreadsheet into AutoCAD as an AutoCAD table.

Don't use the standard Paste command because it will take us back to the less-than-intelligent process of the past. We want to use Paste Special found under AutoCAD's Edit menu. Select the Paste Link option, and here you find two options as displayed below. Microsoft Excel Worksheet sounds tempting, but this inserts the Excel file as an OLE object (less than desirable). You want to insert it as an AutoCAD entity. This choice magically converts it to an AutoCAD table and allows you to place it in your drawing. You now have an AutoCAD table that is linked to an Excel spreadsheet.

If you prefer, you can also use the new Datalink command to accomplish the same task. This command isn't found in any menus (apparently it's top-secret), but you can find it in the Dashboard in the Tables control panel. Here you'll also discover the new Datalink manager. Simply select the option for creating a new Excel Data Link, and you're on your way!

This is a nice approach if you intend to link an entire sheet within a file so there's no need to manually select all of the cells. After assigning a name to your data link, you're prompted to select the Excel file. Here, you can link to a specific sheet or an explicit range of cells within the spreadsheet.

Now, listen up! There's a very powerful little arrow in the lower-right corner of the dialog box. Select this arrow and many more options open up to you. None of these options appears when you use the simple Paste Special technique listed earlier, but you can access these options at any time later on.

The magical arrow opens up many new doors. Do you want the data types in your spreadsheet converted to AutoCAD text or would you prefer to retain the formula information? Are you going to permit writing back to the Excel spreadsheet from within AutoCAD? By default, the linked tables insert with the table locked so you can change the Excel file and have it update in AutoCAD -- but not the other way around. This default prohibits renegade CAD designers from accidentally changing the Excel file. You must select "Allow writing to source file" before you can write back to the original Excel file.

Key here is the information listed under Cell formatting. In most cases, you'll want to use the Excel formatting to determine how a table should appear in AutoCAD (otherwise it uses the current table style). But after that, you might prefer to make some appearance modifications using AutoCAD tools (text height, borders, etc.). You'll probably want to maintain those changes you've worked so hard on during future updates. If this is the case, select "Start with Excel formatting, do not update."

Give your link a try by changing a cell or two in the Excel file and then save it. After returning to your AutoCAD drawing, you'll see the bubble in the lower-right corner, which we recognize as similar to xref notes.

Simply hit the magical blue line, and your linked AutoCAD table automatically updates! Beautiful!

Link from AutoCAD to Excel
So, what if you want to go the other way? No problem. You'll find that your table still appears to be locked as you go from cell to cell. Simply select the cells you want to change and select Unlocked from the shortcut menu.

After making the changes to your AutoCAD table, you're ready to update your Excel file. Highlight the entire file and select "Write Data Links to External Source." I suggest that you have the Excel file closed when you do this process -- just for insurance purposes.

Give it a Try!
You're absolutely going to love how easy it is now to link your Excel files with AutoCAD! Give it a whirl, and you too will discover the power of a truly linked table -- just imagine the time you are going to save! Until next month, Happy AutoCADing!