[Search tip detail and code files using keywords, tip number, author name, etc ]
View Linked Spreadsheets in AutoCAD
Tip# 3386 By Jack Foster On 05-Apr-2010
Rated By 1 users
Categories : OLE Files
Software type : AutoCAD 2010
Rename File To : No Files to download.
Display Excel spreadsheet files in AutoCAD so that they will update when the spreadsheet is expanded.

Jack Foster shares with us his work-around method of displaying Excel spreadsheet files in AutoCAD so that they will update if new cells, rows, or columns are added to the spreadsheet.

"This may not be the easiest way, but for me it is the least aggravating way to attach Excel files and have them automatically update. I set the font type and size in Excel that will be the closest to what I want them to be in AutoCAD. I highlight all of the rows and columns I want, plus about 10 extra blank rows at the bottom. Then, I Paste Special into AutoCAD as AutoCAD entities so they are in a table. I save this drawing with 'Excel' in the filename so I know what it is. In the drawing where I actually need the table, I xref this drawing in at 1=1. I create a paperspace viewport, scale that viewport to 1=1, and size the viewport so my table is visible. At the bottom of the viewport, the bottom line of the table just barely shows.
"Now when the table has rows added or deleted, I open the drawing containing the linked Excel file, and I open the Excel file through the data links. (I could probably just open the Excel file directly, but why push my luck?) After I have modified the Excel file and saved it I go back to the drawing, and the alert tells me to update the link, so I do. Now when I save this drawing, and when I open my main drawing, the xref is automatically updated. The only thing I have to do is resize my viewport so all the rows and columns show properly. If a column gets wider and it makes the cell become two lines of text, I will Undo and then edit the linked table, making it wider than necessary. Re-update the link and then narrow or resize the table. Now I just resize the viewport again so all that shows is my table.
"If I fail to paste in the extra blank rows at the start of this process, then when any rows are added to the Excel file, I have to paste it back into the drawing again. If I had resized any part of it previously, I would have to resize it again. The cool part of this technique is adding the extra rows at the bottom just in case you need them."

Notes from Cadalyst Tip Patrol: The issue being addressed here is the fact that if you copy and paste a spreadsheet into AutoCAD as a link, then additional or deleted cells won't update in the AutoCAD file. Jack plans ahead here by adding the space for the new cells as empty cells. He saves the spreadsheet in a stand-alone AutoCAD file that he references in his drawing file. He displays the data through a viewport, which also allows him to hide the extra cells. This process is cumbersome, but if you don't want to repaste the spreadsheet, you won't have to.


Average Rating:

User comments
Comment by Anonymous
Posted on 2010-04-05 13:59:46
I am responding to many of the Excel file tips. One thing that many of the users seem to be overlooking is the advantage of “embedding” the file into AutoCAD….without the link. I have been doing it this way since 96-97. You don’t have to worry about destroying the link….AND….you can edit the file by double clicking it. Plus…if the size changes (more rows or columns)…just re-paste the file….from the temporary file that is open in Excel while you are editing. (Double Click on the file in AutoCAD, which opens up Excel…with your temporary file in it. Highlight and copy to your clipboard the new block of cells you want. Switch back to AutoCAD and delete the previous copy….then paste in the new one. Then close the Excel file.) Works great with Microsoft Word also. Jerry L. Peck
Comment by Brinton,Mark
Posted on 2010-04-05 15:49:30
Why no mention of linking to AutoCAD Tables? You get much more consistent results and a better looking "table". It also allows you layer functionality.