Linking Dimensions and Parametric Values into Excel: AutoCAD

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.

15 comments

  • Pingback: Direct All Autodesk Applications to New Server for Obtaining Licenses | Synergis Engineering Design Solutions

  • Pingback: Autodesk Remote: What Is It and What Can It Do For Me? | Synergis Engineering Design Solutions

  • Pingback: AutoCAD® Mechanical: Custom Parts – Appending Pipe Lengths | Synergis Engineering Design Solutions

  • How can linking mutil dimension to exel? Only linking 1 dimension? Please, help me.

    Like

    • You have to link the dimensions to a table that is linked to an excel spreadsheet. It appears that you are linking directly to excel versus the table. If you link to the table, the dimensions will be controlled by the selected table cells which in turn is controlled by excel as noted in the blog article.

      I hope that make sense. If you’re still having trouble, email your drawing and excel file along with a description of what you’re trying to accomplish.

      synergiscad@synergis.com

      Like

      • File attach send email: synergiscad@synergis.com
        I have a drawing autocad (I’m using autocad 2014) (don’t file attach). I have list to excel. (result attach).
        Can you guide soloution best?
        I think my solution (use autolisp)
        I think have a solution (ideas):
        First: select dimension 1, select (Pick) other texts (group text 1) (1, H8.5, 2H1, H4…) and Mtext ( 1-4 8VX50 ((if applicable))—->row 1
        Then: right click (purpose to split into two lines in excel)
        Second: Continue select dimension 2, select (pick) group text 2 (2, H6,5)—->row 2.
        ……
        Just like that, Every time we select a group of them in a row in excel and finish (another line) by pressing the right mouse button.
        End: double click (2 enter).
        I hope to receive feedback and comments.
        P/S: with my case, only solution.
        Please guide me.!
        Thank you very much.

        Like

      • We are unable to find a solution for AutoLisp or VirtualLisp, however, I’ve created a short walk through video that will help you in the process for creating the lisp routine.
        http://www.screencast.com/t/C2kEO1o0

        Like

  • I think you misunderstood my intent. Here (in excel sheet), 1 is the number of columns, column 2 is the distance dimension. You have the wrong number of column 2 column order. While, just make your way to the few dimensions. With the drawing so much I think my self switching between autocad and excel maybe faster.

    Like

  • Hello, first, thanks for the tips.
    But I had a problem.
    Creating the data link with a fixed location (C: \ Documents, for example) I was unable to move any of the files (dwg or. xlsx.) without losing references. How do I update the location of bond without modifying the table created in AutoCAD?

    Like

  • Pingback: Synergis Blog: Best of 2013 | Synergis Engineering Design Solutions

  • Udhaya Kumar K

    Thanks for this. Very helpful.
    Similarly, is there a way that a dimension is controlled by an excel field?

    Like

    • No, you cannot control dimensions through the Excel link. There are ways to accomplish this through customer programming that you may want to research online, or contact Synergis Project Manager, Jim Law to discuss pricing and time frame for providing you a custom application that will do what you’re looking for.

      Like

  • Thanks for the guide!

    however i am having a reoccuring error when i try to update the table several times. The first time i set up the datalink and transfer several (area values) to excel everything works perfect. But when i am working in my excel sheet and use the imported data in formules it wont let me update again. i am getting the samen message over and over : 0 data link (s) wirtten out succesfully.
    The only way to fix this is to remove the values in excel en set up niew field values in autocad.
    What might this problem be ?

    Like

  • I’m not sure if you’re still checking this thread, but I’m having a problem uploading datalinks to the external source file and would love some help. I have (sort of) successfully gotten the upload command to work, but for some reason the units change when doing so, ie I have the table set up to list area values of various space objects in the drawing and these values show correctly in the table (in square feet) in the cad file, but they appear in square inches in the excel file once uploaded. For example, one cell reads 1,750 in the table but 252,000 in the excel.
    Any idea why this is happening and how I can fix it? The ultimate goal is to have the SF areas in the excel file, which is the project program, update automatically when the space in the cad file is changed. Thanks so much!

    Like

  • how to create autocad geometry from excel data

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s