By Geoff Woodhead
I recently had the need to display an Excel spreadsheet in a drawing. I could have done this as an OLE object by simply copying the data in Excel and pasting it into AutoCAD. This would have successfully displayed the data in my drawing (see fig. 1) but in my experience I have found it can be difficult to customise the style of the object. Additionally if I were to update the Excel spreadsheet then I would have to manually remove the OLE object from my drawing and then reinsert it. I would much rather have an object that will intelligently update when the spreadsheet is updated.
Fig. 1
This is where Data Links come in. AutoCAD provides us the ability to link to an Excel spreadsheet and visualise this information in an AutoCAD table. This provides us with a much greater degree of control over the style of the table and information contained within it. As the name suggests the table is also linked to the Excel spreadsheet meaning that any changes made to the data in Excel can be intelligently incorporated into the table.
To create a Data Link I’ve found the easiest way is to simply just start creating a table using the Table button on the Annotate tab of the Ribbon. Selecting this button opens the Insert Table dialog box we need to first tick the From a Data Link radio button and then click the Launch Data Link Manager button as shown in the red box on fig. 2 below.
Fig. 2
At the Data Link Manager dialog box we will place a click on the text that says ‘Create a new Excel Data Link’, we are then prompted to enter a name for this link and select OK. Having done this we will be provided with the New Excel Data Link: <NAME> dialog box where we need to select the file we would like to link to using the three dot button as highlighted in the red box on fig. 3 below.
Fig. 3
Selecting your Excel file will expand the dialog box to that shown in fig. 3 above giving access to the Link options panel. Here you can choose wether to link to an entire Sheet, named range or a specified range of cells. Choosing OK will generate an entry for the newly created data link in the Select a Data Link dialog box where we will select it and choose OK. We will be returned to the Insert Table dialog box where we will select OK and then specify a location in the drawing for the table to be inserted. We are now able to use familiar AutoCAD processes to control the display options for the table. If the Excel data is updated while you are in the drawing you will be provided with a balloon notification in the lower right hand corner of the application as seen in fig. 4 below.
Fig. 4
I feel Data Links are superior to OLE objects in every aspect, making my life a lot easier by allowing me to just design while they keep my data up to date. Hopefully this will blog will make your first experiences with Data Links more pleasant ones.
Comments
3 comments
I have done this exact thing more times than I can count over the past 5 years and am using all of the same files I've inserted in the past. I've never had a problem until recently. I upgraded from Win8 to 10 it worked fine for months. I am now getting the same error:
" only excel 2000, excel 2003, and excel 2007 file formats are supported. you will need to save the excel file into one of these formats."
I believe it is a result from a recent Microsoft windows 10 update. I've tried uninstalling, reinstalling, uninstalling deleting all local cad directories, uninstalling deleting all local cad directories AND uninstalling Microsoft C++ Redistributables (all of them). And I still can't get it to work. It's such an integral part of my drawings that I think it's worth the time, but I'm running out of ideas myself.
I have found a forum on this topic, however I didn't like the solution:
http://forums.autodesk.com/t5/autocad-lt-general-discussion/datalink-to-an-excel-spreadsheet/m-p/6290775/highlight/false#M137932
Please sign in to leave a comment.