AutoCAD and Excel work in Perfect Harmony.
In this third part of our deep dive into AutoCAD tables, we explore data linking. You can review the earlier installments on tables and data extraction.
What is data linking? It connects your AutoCAD drawings to external data sources. The name is a bit misleading because the data source must be a spreadsheet (Microsoft Excel) or a CSV file. Data linking is useful when you need to keep your AutoCAD drawing up to date when data is changing. That could be project timetables, part lists, inventory records or facility management-type records.
For data linking to work, Microsoft Excel must be installed on your computer. None of this works with Google Sheets or other spreadsheet applications.
So, instead of manually entering or updating the information, you link it. Changes made in the external data source update your AutoCAD drawings. This ensures that your drawing reflects the most current information, saving time and avoiding costly data entry errors.
Although not limited to tables, it is tables that supply the simplest (and, in most cases, best) method for presenting the data. AutoCAD tables organize and present data in a structured format. They are essentially spreadsheets.
How to Data Link
Start the Table command. The default is starting from an empty table, but you want to use the From a data link option. You can then select an existing data link or launch the Data Link Manager to set up a new link.
In the case of using an existing link, the configuration is complete. Click OK to insert the table. The selected data link establishes the initial number of rows and columns in the new table.
Pro tip: You can use a single data link in multiple tables (and other AutoCAD objects).
To create a new link, select the Data Link Manager button. In the Data Link Manager dialog:
- Select Create a new Excel Data Link
- Set the new link’s name
- Browse for and select the spreadsheet (xls, xlsx) or comma delimited file (csv)
- Select the path type: Full, Relative or No path
Tip: Use the “Full path” option when the spreadsheet is stored in a fixed location, which won’t change even when you move the drawing to a different folder. “Full path” forms an absolute path and defines the file location, not the location of the drawing.
AutoCAD previews the table when Preview is enabled. Using the Link options, set the sheet to extract from the range. Options for the data range are the entire sheet, a named range or manually setting the range.
Select Preview to update the table preview with the new range. This is useful for reviewing the selected data before creating the link.
When you return to the table dialog, AutoCAD sets the new data link active. Click OK to insert the new table into the drawing.
AutoCAD inserts the data between the title, header and footer rows (if they exist). With data linked tables, the tooltip displays information about the linked file. In addition, a chain link icon appears next to the cursor to visually show linked data.
When the externally linked file changes, AutoCAD displays a tooltip. Clicking the update link updates the table with the changes. There are many methods of updating the link manually. You can right-click the chain links icon in the status bar and update all data links in the drawing. Likewise, with the table selected, you can use Update Table Data Links from the right-click menu.
Copy and Paste (Special)
Another method is using Copy and Paste. After selecting the cells within the Excel spreadsheet, Copy (Ctrl + C) the data to the clipboard. Switch over to AutoCAD and use Paste Special to start the insertion process.
In the Paste Special dialog, select Paste Link. The right-side panel shows two options: Microsoft Excel Worksheet and AutoCAD Entities. You want to select AutoCAD Entities because the Excel Worksheet option creates an object linking and embedding (OLE) link, which will keep the data in Excel format.
After clicking OK, AutoCAD prompts you to insert the new table. It uses the active table style.
With this method, a new data link is created, even if the spreadsheet was already set up with an existing link. After inserting the table, use the Data Link Manager to rename the link.
Editing the Link
The Data Link Manager is accessible from outside the table command. You can use it to not only create links but also to rename, delete and edit the links contained within the drawing. Additionally, you can launch the linked spreadsheet from the Data Link Manager.
When you edit an existing link, AutoCAD presents all the options just as when you create a new link. You can select a different spreadsheet, change the path type and adjust the sheet and range.
Working with the Table
You can alter the table by changing the way it looks by adding rows and columns, using the linked data in formulas and changing the information in the cells. When you update the table, AutoCAD retains any changes made to the formatting and structure.
When you pick the table, bold lines highlight the linked data cells. Adding rows and columns does not change the linked data, but you can only add columns and rows outside this perimeter.
By default, AutoCAD locks the linked content to prevent accidental changes. But these cells are unlocked (and available) for formatting changes. By using the options in the right-click shortcut menu, you can unlock or lock the cells, allowing or preventing changes to their data and formatting.
When the linked spreadsheet changes, the table updates accordingly using the DATALINKUPDATE command. Likewise, when you change the linked data in AutoCAD, you can update the linked spreadsheet with the same command.
To remove the data link from a table but retain the data, click within a cell of the table. Then right-click on the selected cell and choose Data Links > Detach Data Link from the menu. The text becomes static and will no longer synchronize with the spreadsheet.
Starting with an Existing Table
As we have seen, you can create new tables that use the data from a linked spreadsheet. You can also insert linked data into an existing table.
Select the cell where you want to start the data insertion. From the ribbon, select Link Cell. This launches the Data Link Manager, where you can create a new link or select an existing link (just as when creating a table).
Data Extraction and Data Linking … Is It Possible?
Data Linking is merging spreadsheet data with drawing data (like within a table). Data matching links pieces of data: extracted drawing data and spreadsheet data.
For example, you have a parts list in the drawing that lists the part numbers, descriptions and quantities. You want to include supplier price list information in the drawing, which is contained in an Excel spreadsheet and maintained by your procurement group.
Link External Data is available during the Data Extraction process (Step 5 – Refine Data). In the Link External Data dialog, select an existing data link or launch the Data Link Manager to create a new link.
With the link selected, use the Data Matching options to set the Drawing and External columns to match. Click Check Match, which validates the configuration.
In the part list example, you match the drawing Part Number column with the Stock Number in the external spreadsheet. Because there is at least one matching cell in the drawing and external data columns, AutoCAD can tie the two together. The resultant table lists the parts (blocks) in the drawing, extracting the relevant cost from the linked spreadsheet.
Pro Tip: Check Match is important. It compares the drawing key column data with the matching external key column data. The external data must be unique across all rows and the key column and there must be at least one data match between the two for the check to be successful. Only then can you proceed.
In Summary
Microsoft Excel is a valuable tool, which is relied on in the engineering realm. Why? It’s flexible and accessible. Anyone can build a spreadsheet.
AutoCAD tables are simple in comparison to spreadsheets. By using Data Links, you get the best of both worlds: structured data presented professionally in your AutoCAD drawing and the power of spreadsheets to build the data.
