Thursday, April 21, 2011

Using a Spreadsheet Table in a Drawing Sheet

You have a MS Excel file (.xls or .xlsx) that you'd like to place in a drawing. You've tried to use the Insert Object tool, but the resulting OLE (Object Linked Entity) table is of poor quality and doesn't print well.  Is there a better way?
Example of the poor results you get using the Insert Object method.

This might not be the best way to insert the table.

Indeed there is a better way than using the Insert Object tool. You can use the General Table tool to do this. Unfortunately, many Inventor users seem to overlook this method. Let's have a look:

Click the General Table button (found on the Annotate tab of the drawing environment):

Click the Browse button to locate the spreadsheet file you want to use:

Once you have located the file you want to use click the OK button:

This will place the Spreadsheet on the drawing sheet as a table:

You'll see the link in the browser as a 3rd Party node, and a sheet node for the table:

To edit the spreadsheet you can right-click the 3rd Party reference and choose Edit:
(note too the Change Source option that allows you to swap out the link of the original spreadsheet with a link to a new spreadsheet)

In Excel you can change cell values as you normally would, then Save and Close the spreadsheet:

Back in Inventor, you can right-click on the table and choose Update:

This will pull the new values into the table:

You can format the table by right-clicking it and choosing Edit, in order to change the Table header, etc.

Note: A few things have been pointed out to me since originally creating this post: 
  • OLE object table will print ok, but it just doesn't display on the page very well. 
  • If you save drawings as PDF OLE's will still display poorly. 
  • If you have merged cells in your table then the OLE option might be best, since the General Table option doesn't honor the merged cells.