Linking Data in Tables
You can create tables that link to an Excel spreadsheet.
One easy way to do this is to copy the data from Excel, and then use Paste Special to paste the data into a drawing as a link. This pastes the Excel data into the drawing as a table object. The program automatically creates a data link between the drawing and the Excel spreadsheet.
Here is an Excel spreadsheet that contains a part list. Select all of the cells and then copy them to the Windows clipboard.
Switch back to your drawing. On the Home ribbon, in the Clipboard panel, expand the Paste split button and choose Paste Special. The program displays the Paste Special dialog box. Choose Paste Link to paste the contents of the clipboard as a link. You then have the choice of pasting the link as a Microsoft Excel Worksheet or as AutoCAD Entities.
Select AutoCAD Entities, and then click OK to paste the data from Excel as a table object. The program prompts you to specify the insertion point. Click to place the table. You can now see all of the data that was copied from Excel.
Since you pasted the data as a link, the data in the table is linked to the Excel spreadsheet. Switch back to Excel. Select the unit cost for item 1 (the Name Plate), change it to 400, save the change in Excel, and then close the spreadsheet. When you switch back to your drawing, the program displays a balloon in the lower-right corner informing you that a data link has changed. The balloon includes a link. If necessary, move the command line out of the way. When you click the link in the balloon, the data in the table automatically updates to reflect the change you made in Excel.
Switch back to Excel, open the spreadsheet, and make some other changes. For example, select the unit cost for item 4 (the Foot), and change it to 400. Then select the unit cost for item 1 and change it back to 500. Save the changes in Excel, close the spreadsheet, and switch back to your drawing. Again, the program displays a notification balloon telling you that the data has changed. When you click the link in the balloon, the table updates to reflect the changes you made in Excel.
You can also make changes to the table in the drawing and then push those changes back to the Excel spreadsheet. But before you can do that, you must enable this capability.
First, you must unlock the table cells so that you can make changes. By default, table cells linked to external data are locked. In order to be able to change the data, you have to unlock those cells.
When you select the table and then move the cursor over its cells, the program displays a lock badge adjacent to the cursor, along with a tooltip showing information about the data link. This tooltip also shows the current lock state.
To unlock individual cells, select the cell. Then, on the Table Cell ribbon, in the Cell Format panel, expand the Cell Locking split button and choose Unlocked. Now, when you move the cursor over that cell, the tooltip shows you that the cell is unlocked. But when you move the cursor over any other cell, the tooltip indicates that those cells have locked content.
To unlock the entire table, click the upper-left corner of the table to select all the cells in the table. Then, in the ribbon, expand the Cell Locking split button and choose Unlocked. Now, when you move the cursor over any cell, the tooltip indicates that the data cell is unlocked.
Next, you must enable the ability to write back to the Excel spreadsheet. With the table already selected, on the Table Cell ribbon, in the Data panel, click the Link Cell button. The program displays the Modify Excel Link dialog. In the File group box, the field already includes the path to the Excel spreadsheet. Click the arrow in the lower-right corner to expand the dialog box. In the Cell contents group box, select Allow writing to source file, and then click OK.
There is another way to reach this dialog box. Press ESC to deselect the table. Then, on the Annotate ribbon, in the Tables panel, click the Link Data button to open the Data Link Manager dialog. Select Excel Data Link 1 (the link to the Excel spreadsheet) and right-click to display a shortcut menu. Note that you can click Rename and then give the data link a more descriptive name if you wish. To edit the data link settings, choose Edit… from this shortcut menu. This opens the same Modify Excel Link dialog. Since you have already changed the setting to allow writing to the source file, you can click Cancel to close the dialog.
Now that you have unlocked the cells and enabled writing to the source file, any changes you make to the table can then be exported back to Excel. For example, double-click the unit cost for item 4 and change its value back to 500. If you find that the cell is locked, simply use the tool on the ribbon to unlock it again. After changing the value, close the text editor.
To push the change you just made back to the Excel spreadsheet, on the Annotate ribbon, in the Tables panel, click Upload to Source. The program prompts you to select objects. Select the table and then either right-click or press ENTER. The program displays an alert balloon to inform you that a data link has changed. Click the link in the balloon. When you do, the program indicates on the command line that 1 object was found and 1 object was updated successfully.
Switch back to Excel and reopen the spreadsheet. The unit cost for item 4 is once again 500, reflecting the changes you made to the table in your drawing.
Source: Autodesk
