Ready to Take the Next Step?
Announcement
June 26, 2013

Posted on June 26, 2013 by Darin Green, Synergis Lead Helpdesk Technician:
Being able to link measurement values to Microsoft® Excel® has always been a challenge for AutoCAD users. Earlier versions of AutoCAD (2005, 2006, and 2007) require users to import the Excel file as an OLE object. Those OLE objects were nothing more than an Image with a link that opens the .xls file with Microsoft Excel by double clicking the image.  AutoCAD 2008 was the first release to include Data Links, a linking mechanism which relies on AutoCAD’s table feature. AutoCAD tables favors Excel in many ways, and like Excel, these tables can leverage just about any object and/or file geometry into its cells while minimizing manual input from the users.
In the following example, we are going to create a Data Link using an existing Excel file; Create a AutoCAD Table using the Data Link; Extract dimension values into the AutoCAD Table; and have AutoCAD write the values to the Excel file.
1) On the Insert tab, choose Data Link from the Linking & Extraction panel.dl1
2) This will open the Data Link Manager
3)  Click create a new Excel Data Link and give it a name.
4) After clicking OK, the New Excel Data Link dialog will appear.  Browse to the Excel file.
5) Under Link options, leave the default setting to “Link entire sheet”

6) Click the  button to expand the dialog box for Cell options.

7) Adjust your options to match the image above.

Cell Contents
Options in this box will determine how data is imported into your drawing from your external source.

Convert Data Formats to Text, Solve Formulas in Excel
Imports Microsoft Excel data as text with data calculated from formulas in Excel (supported data formats not attached).

Allow Writing to Source File
Specifies that the DATALINKUPDATE command can be used to upload any changes made to linked data in your drawing to the original external spreadsheet.

Cell Formatting
Use Excel Formatting
Specifies that any formatting specified in the original XLS, XLSX, or CSV file will be brought into your drawing. When this option is not selected, the table style Table dialog box

Keep Table Updated to Excel Formatting
If the option above is selected, updates any changed formatting when the DATALINKUPDATE command is used.

8) Click OK to confirm settings, then you will return to the Data Link Manager.  (You should now see your excel link in the list, if not repeat steps 1 through 8)

9) Click OK in the Data Link Manager to return to drawing window.

Now we’re going to create a table using the Excel link.

10) From the Annotate tab, choose Table located on the Tables panel.

11) Select “From a data link” and choose the new data link you just created within the Insert Options, then click OK.

12) Click anywhere in the drawing window to place the table.

13) Click into a cell within your table, right-click and choose Insert > Field.

14) Within the field selection window, scroll down the left side of the dialog to locate “Object“, then click the Select Object button.

15) Select a dimension that you would like to extract data from.

16) After selecting the Dimension, you will be taken back to the Field dialog.  Select “Measurement” property to display the dimension value, then click the OK button to return to the drawing window.

17) Click OK to return to the drawing window and note the cell now has the value 2.6707.

18) Now that cell has the value of the dimension, you can push this data to the Excel file (Data Link) by using the “Upload to Source” command located on the Annotate tab > Tables panel.

19) Select the edge of the AutoCAD table, and hit enter.

20) Note the command line reads the following:

21) Open the Excel file to see the new value added.

If you run into problems, call the Synergis Helpdesk at 800.836.5440, or email us.  It may be something popular that we can share on our blog.

-Darin

Check out some of these additional posts by Darin:

Darin Green is one of our Helpdesk Leads and provides tips and information in our monthly Helpdesk Newsletter.  Darin has over 10 years of CAD experience and in his time at Synergis he has provided support for over 700 customers to help them stay ahead.  He previously worked with Ritter and Plante Associates where he performed various zoning, project duties, and hands-on field work, along with surveying.  Darin has a broad range of experience with many CAD applications including AutoCAD, Revit, AutoCAD Civil 3D, Map 3D, among others.

Have a question for us?  Contact us via email, phone (800.836.5440) or on our website.