linking Excel external data to AutoCAD data extraction

We can add information to block using attributes. It can be very handy to create a report/schedule items in your drawings. We can use data extraction to do it automatically. If you’re not familiar with it yet, check this tutorial how to create a schedule from AutoCAD drawings.

However, sometimes defining attribute value for each block can backfire to us. If we want to add many information to an object, we need to input data several times. For each block. If we have dozens or hundreds of blocks, using attributes and data extraction like this may not increasing our productivity, but reducing it.

If you have this condition:

1. The information are common for object with the same ID/part number.
2. We need to update attribute value regularly. It doesn’t make sense if we need to edit attribute value for each block, does it?

If that’s your situation, you can link AutoCAD data extraction to Microsoft Excel as external data. We only need to have one attribute as identifier in our block, then link it to a column in Excel.

It means we can have objects data separately, and link our drawings to that file. When we need to update the data, we only need to update it on Excel file. Then simply update the data extraction tables on our drawings.

Let’s see how we can use it.

Note: You need to have Microsoft Excel installed on your computer to use this feature

Preparing Excel file

Let’s prepare an Excel file. You need to have one column to match to an attribute in your block. In this example, I have door types, their prices, and several more data. I will use door types in my block as attribute. This value shouldn’t change regularly (unless I change its type). But door price can change often, right?

You may give style to your Excel file, like borders if you need to. It won’t affect linking data to AutoCAD.

Preparing AutoCAD block

Now let’s check our block. You need to have at least one block attribute to match the column in Excel. Here, I only have one attribute. I put other data in my Excel file. But you may have more attributes as necessary.

After you save your block, you can place it to your drawings.

Using data extraction

After you’ve done with the preparation and finish with your drawing, we can start using data extraction. Use it as usual (refer to this post as mentioned previously, if you’ve never done it before). When you get to 5th page (refine data) stop and follow steps below.

Linking data extraction to Excel file

In 5th page, you can see preview of your data extraction. Find link external data button on the right bottom of this dialog. Click it.

If you already link your drawing to an Excel file, you can choose the data link here. Since we haven’t create it, we need to create it first. Click launch data link manager in this dialog.

Create a data link to your Excel file. Give it a unique name and browse to file we prepared before. It should be straightforward.

After you finish, we are back to link external data dialog. Now your data link should be defined as source (1).

Match your attribute to your Excel column (2). You can try to check if your data are matched correctly.

You can choose which column in Excel you want to include or exclude in this report (3).

Click OK. You should see additional column in your preview. You can see column header for external data showing linked symbol.

Arrange the column as necessary and place table on your drawing (or external file). Very nice, isn’t it?

If you have a problem setting external file as described above, you can see this nice video by Donnie Gladfelter: http://youtu.be/QLGoxHMg8Zw.

Updating value in table

What if value in your Excel file changed? Unfortunately data link is not updated automatically. We have to update it manually.

After your data changed, you need to select your table, right click, then choose update table data links from contextual menu.

You can use DATALINKUPDATE from command line

Another trick to automatically update data link every time you open AutoCAD drawing is to put DATALINKUPDATE in your acaddoc.lsp. I will cover about it later.

Do you think external data will be useful? How you do you think you can use it?

About Edwin Prakoso

I work as a Sr. Consultant in PT Cipta Satria Informatika. I've been using AutoCAD since R14 and Revit since Revit Building 9. I occasionally write for AUGIWorld magazine and I am also active in Autodesk discussion forum. I'm a member of Autodesk Expert Elite, an appreciation for individuals who give contributions to the Autodesk community.
Connect with me on twitter or LinkedIn.