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.

excel_data

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.

block_attribute

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.

link_external_data

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.

launch_data_link_manager

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).

data_matching

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

external_data

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.

update_table_data_links

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?

Comments

  1. PepaR says

    BTW, nice feature for create live data link in both direction (AutoCAD Excel) is enable option for writing to source in properties of data link.

    • says

      Yes, it's a nice feature. Especially if datalink doesn't require Excel installed on our machine. However, if I have AutoCAD and Excel both on my computer, I prefer to use Excel. If I only have AutoCAD ability to edit it in AutoCAD would be nice…

  2. BBEERR says

    hy,

    When I use data extract I am able to create a table in excel and a table in my drawing.

    But.

    when I change my value ( in this case a lookup parameter) it change’s in my autocad table, but my excel sheet doesn’t update.

    (I am shure I checked allow update to source, and i have used the upload to source funcion)

    need answer soon ;)

    Greetings

Leave a Reply

Your email address will not be published. Required fields are marked *