Linking Excel and AutoCAD with Data Links

Arguably the most valuable tool in the modern-day engineer’s toolbox is Microsoft Excel. People love Excel so much that after its launch in 1985 Microsoft redesigned the rest of the Microsoft Office programs to look more like it. Microsoft Excel’s dominance is undoubtedly tied to its flexibility. After all, I think it’s fair to say that nearly all of us have at least one Excel table that is “wickedly complex”. The introduction of OLE objects, and more recently AutoCAD Tables were noble attempts to synchronize AutoCAD with Excel, but still fell short for many real-world needs. What engineers wanted and needed was bidirectional synchronization between AutoCAD and Excel. Among the new features packed within AutoCAD 2008 is just that capability through use of Data Links.

To set up Data Links:

  1. Launch the TABLE command.
  2. Click “From a data link“, and launch the “Data Link Manager” by clicking the button (illustrated).

  3. Click “Create a new Excel Data Link”

  4. Name the Data Link – something intuitive.

  5. Browse for your Excel Table

  6. The “New Excel Data Link” dialogue can be expanded by clicking the button in the bottom right corner.
  7. Under “Cell Contents”, uncheck “Convert data types to text”, and check both “Retain Formulas” and “Allow writing to source file”. Checking those options will make it so your Excel formulas will be preserved, and that you will be able to make edits to the table in AutoCAD, and they be populated to the origional Excel file.
  8. Press the [Ok] button 3 times to get out of the command, and insert your linked table.

  9. Assuming you checked the “Retain Formulas” option when setting up the Data Link, you’ll notice fields (text with a grey background) for any calculated value.

After inserting your Excel table, any updates made in the source Excel file will be updated in AutoCAD. By default however you will not be able to update data from AutoCAD, and have it update in the source Excel document. To modify a table value in AutoCAD, select a cell, right-click, and navigate down to “Locking”, and select “Unlock”.

After unlocking a cell, you can click into a cell, change a value. As a deliberate checks and balances, to update the Excel file, you must right-click once again, and select “Write Data Links to External Source“.

So there you have it, a quick run-through on how to harness the new Data Links in AutoCAD 2008. Stay tuned for more in AutoCAD 2008.

Update: If you found this post helpful, you might also be interested in my other post titled “Quickly Link Excel Tables to AutoCAD“. You can find it and all of my posts related to Microsoft Excel by clicking the Excel tag below.

About Donnie Gladfelter

Donnie is author of the book and Autodesk Official Training Guide, AutoCAD and AutoCAD LT: No Experience Required, a columnist for AUGIWorld Magazine, Autodesk University speaker, and former member of the AUGI Board of Directors.

  • Ffelix

    Is there a field that will extract data from one cell in a spreadsheet without using a table? Also, is there a way to insert a datalink table with the same cell dimensions as the original Excel file?

  • http://pulse.yahoo.com/_ARCOF4MTGQWP7PJBHRJUFBEA5U boo hoo

    Hi Donnie,
    I have around 10 excel links from excel sheets to autocad tables, for about 100 drawings. As in, I have just as many excel SHEETS as Autocad DRAWINGS, all named accordingly.

    To do this I made a block with these links, and using a script, inserted the same block into every drawing. All I have to do is open each drawing and change each of the 10 links to match the drawing/sheet which is open. (i am struggling to explain)

    I was hoping there would be a way of selecting all links at once and changing all 10 SHEETS to the correct one. (they are all pointing to the first sheet, because they are all the same exploded block).
    Believe it or not even changing each one individually, it is still saving me a lot of time but i was hoping it could be done a bit faster.

    Thanks
    Sigurd

  • Javier

    Donnie, thaks for sharing this post, i found it really useful. I have a problem uploading my changes to the excel table inserted in autocad to the original excel file. I tried to create a field with the area of a pline and I can upload it but when I modify the poliline and the field shows the new area, I click on update to source file and get the message:
    1 object(s) found.
    1 data link(s) could not be written out.
    0 data link(s) written out successfully.
    It happens only when the object to which the field is referred is modified after creating the field.

    Thanks

  • Pascal

    I work with Windows 7 with VMware Fusion on Mac.
    My Excel is the Mac-version.

    I always get the message:
    The data link cannot be created because Excel cannot be found.

    I don’t know what to do… : (

    • http://thecadgeek.com/ Donnie Gladfelter

      Hi Pascal – Unfortunately Excel installed alongside AutoCAD (on the
      same machine) for this method to work.

  • Joe Ybarra Jr

    Here is an update and the fix to my problem and hopefully it helps someone else in turn . . .
    http://usa.autodesk.com/adsk/servlet/ps/dl/item?linkID=9240617&id=11753432&siteID=123112

  • JoeyY

    Problem I am having with the datalinking is that I have to open the excel spreadsheet and save and then open the drawing(s) I’m working on. If I dont do this the datalink within autocad will not work/ show correct up to date information.
    I’m using just a basic now() function for a date stamp to our project managers and engineers.
    It all works correctly except for this ‘hicup’.
    Please advise

    - Using AutoCad 2010

  • http://thecadgeek.com/ Donnie Gladfelter

    ChaseS – Thanks for your comment. I'm not sure if it will help you get any closer to your goal, but have you looked at my more recent blog post entitled “Dynamically Combine AutoCAD Data Extraction Tables with Excel Tables” (http://thecadgeek.com/blog/2010/04/28/autocad-d…). Hope that helps – Donnie

  • ChaseS

    I am looking for a way to link values from individual cells of the spreadsheet to text boxes inside an Autocad 2010 dwg (I have used this tutorial to successfully link spreadsheet from excel to autocad that will actively update, but I am having trouble pulling data from the speadsheet within the autocad dwg). I can use the “Formula” option from “insert field” in some cases but in some places I simply need for Autocad to duplicate a name or text filled cell. (This is where I have normally been able to use “insert field”, selected “object”, and used the “click selection icon” to tell Autocad what “text” or “multiline text” data to duplicate). This works between for linking “text” and “multiline text” boxes within the dwg will not acknowledge cells of the spread sheet that contain text rather than number values.

    Essentially I am setting up an excel spread sheet and will eventually link it to multiple Autocad dwg's that will need to pull data from the “master” excel file. When data is changed in a cell on the spread sheet it will be updating place values over multiple dwg files. (like I started to explain above, I can get the spreadsheet from excel to the multiple dwg's but I haven't been able to link data, mostly text data and oddly formated numbers that should be treated as a text entity, to designated places within the autocad dwg.)

  • Pingback: Autodesk AutoCAD 2006 | Sms hide Blog

  • Ahaugen

    Has anyone figured out a way to link an Excel range that has hidden columns into acad2010 and successfully create a table that does not include the hidden cells? For example, my data range is A2:M22, but I do not want columns B-F to be included…is there any way to do this? I saw an earlier post suggesting pivot tables but that does not seem to work in this situation.

    A Second, unrelated question, has anyone scaled a linked table in acad only to need to insert rows into the table and have the new text come in at the original scale when updated? Is there a way to set the table so updates will come in at the proper scale?

    Any help would be greatly appreciated, thanks!!