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:
-
Launch the TABLE command.
-
Click “From a data link“, and launch the “Data Link Manager” by clicking the button (illustrated).
-
Click “Create a new Excel Data Link”
-
Name the Data Link – something intuitive.
-
Browse for your Excel Table
-
The “New Excel Data Link” dialogue can be expanded by clicking the
button in the bottom right corner. -
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.
-
Press the [Ok] button 3 times to get out of the command, and insert your linked table.
- 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.
Popularity: 26% [?]
Excellent Article. I have a question becuase I haven’t seen it posted anywhere. For the record, I am playing with ADT 2007 with 2008 Arch scheduled to be installed soon. (should be the same - I think)
Can the table pull data from objects and feed that data to an excel spread sheet? It doesn’t seem to that is an option?
Krushert - glad to hear you enjoyed the article. You pose an interesting question that I must admit made me curious. Just for testing purposes, I drew a line to see if I could get its length into the table, and back into Excel. The short answer is that it can be done.
As outlined in my post – I unlocked the cell. After doing that and clicking into I right-clicked, selecting “Insert Fieldâ€. I configured my field to look at the length of the line I drew, and its length was placed in my table. Finally I selected my table, and specified “Write Data Links to External Sourceâ€. The length of my line was in the Excel table when I opened it in Excel. Pretty novel find, thanks for the suggestion.
That’s an awesome way to do it! But I’m wondering how to actually rotate some parts of the table. For example, I want to be able to plot turning movements for an intersection…NBL, NBT, NBR, SBL, SBT, SBR, EBL, EBT, EBR, WBL, WBT, WBR with their proper orientation and everything, and make it for multiple intersections. But it should also allow for a user to make changes.
thanks in advance for your help.
I also have a question:)
Can I make a crossection, which data (coordinates )will be written in excel and then plot a polyline in autocad drawing along with table data..
I am trying to figure out how to retain the formatting of the autocad table and still get the info from the excel table into it. Simply unclicking the retain excel formatting button does not work.
how can i make dmg read only
I have one question. The question is whether it (to export data from excel on autocad) is applicable in new version of autocad ie. autocad-2008 or other version too.
The Excel linking feature is only available in 2008. Pre-2008 you would have to fall on a 3rd party package of some type. Another option is to create a linked OLE object.
Thanks for the information, but I can’t even get a table into CAD let alone edit it. I keep getting asked if I want to save the changes I have made to my specified excel file, and when I say yes, it trys to save a copy of the file, not the actual file. Even the preview is not coming in when I give it the correct name range and sheet name. Do you know what the problem could be? Thanks.
i am wanted to link excel data in to autocad drawing in the form of cross section of the river or canal i.e the elevation versus chainages in the x and y axis respevtively by changing the data in the document file it would have to change automaticaly in the autocad drawing itself that is the profile change. but i could not it? can you explain me the required problem through my e-mail please.
I noticed your note:
Comment by Donnie Gladfelter on October 29, 2007 @ 7:06 pm
The Excel linking feature is only available in 2008. Pre-2008 you would have to fall on a 3rd party package of some type. Another option is to create a linked OLE object.
However, the OLE object is not close to what we are sssking.
In your suggestion, you proposed seeking “a 3rd party package….” Please know I do not consider your positive response any endorsement, but can you please recommend one or 3 such 3rd party packages that I probably should check out so I can use this feature now before having access to 2008 in years to come?
I am trying to create an autocad image with data points imported from excel. I am digitizing some automotive components and would like to see a picture of the data points displayed on the screen.
Is the data linking feature compatible with excel 2007 or just 2003?
I am using AutoCAD 2008 and of course I took advantage of great new linking possibilities. I have linked number of cells (about 70 links) from single XL file to AutoCAD file. XL file is a template file with some calculations which can be used in different projects; therefore I need to create copy of this XL file and copy of AutoCAD file as well. Copy of AutoCAD file stays linked to original file (which is logical). My next step is to edit each data link and specify new source file (cell location in XL remains as in original file). By using Data Link Manager I can do this task one by one, it takes so long. Is there any better way to tell AutoCAD that new data will be linked with different file with same cell locations?
Thank you for your help
Richard - AutoCAD 2008 does not seem to have an option for XLSX files. You’ll need to save your Excel tables as a XLS (2003) file.
Ilia - Per my e-mail, instead of switching the reference, try switching the file. Keep the reference unchanged, and simply backup the un-changed version of the file in a separate location. Then update the original.