Jump to content

Excel to ACAD Table - Font question


jwkilgore

Recommended Posts

We just upgraded to AutoCAD 2009 (from 2007) this week. We have a license subscription, but our primary client only recently upgraded to '08 and doesn't want files generated by any version newer than '09. I've learned to not ask why, just go with it.

 

I was really excited about the ability to dynamically link Excel files into AutoCAD. I'm working on a project with over 1500 footings, requiring massive amounts of design data in many tables. I was dreading coordinating and checking all this using the old method of transferring Excel files into '07, mainly because the design criteria for the footings keep changing. Now I can just keep a couple of Excel files updated and I'm good to go. I've made a few practice runs and everything is looking good... except for the font. This client requires us to use a proprietary SHX font file in AutoCAD.

 

The linked table reads the font style from Excel instead of the AutoCAD Table text style. Actually, it appears as if the font is encoded with the text data, something like "{\fCalibri|b0|i0|c0;TEXTHERE".

 

If it were a standard Windows font I would just change the font in Excel and keep on going. I can unlock the cells and change each one individually (edit the text, change style), but 1500+ rows times 9 to 15 columns of data per row... that's some serious time. Plus each time the link is updated it overwrites the font changes. I've tried disconnecting the link (the final submitted CAD file won't be linked) which solves the overwrite problem, but still I can only edit one cell at a time. As of now, it looks like we'll just use TTF fonts until the very end then we'll detach the link and task a tech or two to sit there and blow through the table changing the font in every table. And then go through the tedious process of re-checking every cell in the table in case the value in one of the cells gets accidentally changed in the font change process.... ugg. :cry:

 

In order to avoid that, does anyone know how to:

1) Use AutoCAD to globally override the font that is included with the cell data in Excel;

or

2) Change it so either the font call is NOT embedded in the Excel cell value (or else change it so AutoCAD doesn't import this portion of the cell value);

or

3) Convince Excel to read and display an AutoCAD SHX font;

or

4) Convince a multi-billion dollar company that they need to change their standards so I can easily import tables into AutoCAD from Excel o:)

 

Thanks in advance!

Link to comment
Share on other sites

Don't know if this will help as I don't use dynamic linking I usually use lisp for all this sort of thing

 

If you are using the copy > paste special > paste Link > Autocad entities

 

You can change the font by clicking on the text then right clicking on the text and goto properties - then goto 'text style'

 

This will still be linked to the Excel file.

Link to comment
Share on other sites

Yes, but that requires changing every single cell one at a time... almost 20,000 of them. It actually goes fairly quickly. Double click to edit the upper left cell, click the Style drop down and re-pick the same style, and the text is fixed. Hit TAB, repeat with next cell. TAB-click-click-TAB-click-click... One finger on TAB and the mouse hovering over the drop-down. Hellooooo carpal tunnel.

 

Unfortunately, when you re-load the data from the Excel file it re-sends the same cell data which includes the encoded font call, so everything you've done is undone in a few seconds.

 

Right now the plan is to wait until the design is final in a few months and we're ready to submit the final ACAD files to the client. Then we'll detach the link, hand out a few ibuprofen tablets, change over all the cells, and send it.

 

Hopefully, though, someone knows of a better way. I don't know LISP, but I've recently dusted off my old college programming skills and written several V-BASIC programs for Excel. Maybe I can write a macro to run through the table and strip off the extra info. I'll look into that this weekend.

Edited by jwkilgore
Link to comment
Share on other sites

  • 2 weeks later...

Anyone else have any ideas? I never got the chance to check into VBasic, and it's not looking like I'm going to get the change.

Link to comment
Share on other sites

  • 6 months later...

JW, any luck?

 

If not, try the StripMTEXT LISP routine here: http://forums.autodesk.com/t5/Visual-LISP-AutoLISP-and-General/stripmtext-v5-0-Lisp-Routine/td-p/2673838

 

This outstanding routine, which belongs in the AutoLISP Hall of Fame, both for its excellence in programming and excellence in documentation, will remove those obnoxious embedded formatting codes that ACAD should have removed on its own as part of the Paste Special operation.

 

--> Shame on you, AutoDesk, for yet another example of sloppy, defective programming that wastes your users' time! :x

 

Save the LISP routine, drag 'n' drop it on the open drawing, type SMT, click a border line on the table, and hit Enter twice. Poof! The embedded Excel formatting is gone and now you can apply an ACAD Table Style using any font you like.

 

This also works on other objects with embedded formatting -- Mtext, Mleaders, Dimensions, and Multiline Attributes. Open the routine in a text editor to read all about it.

 

Many thanks and kudos to authors Steve Doman and Joe Burke. :notworthy:

Link to comment
Share on other sites

  • 10 months later...
  • 2 weeks later...
  • 4 months later...
Calibri is a True Type font just get a copy of it and add to your system\fonts, pretty sure 2009 used TTF.

 

[ATTACH=CONFIG]39441[/ATTACH]

 

Thanks! But I still can't change all the text fonts to a shape font at one time in AutoCAD.

Link to comment
Share on other sites

How to set all text in excel to system? What is the system font?

system font.jpg

 

One way of getting these fonts into AutoCad tables easily (with the desired font obviously set up to be used in the table and text style properties) is to uncheck the "Use Excel formatting button" when creating the datalink (obviously you lose any Excel formatting then, so it is a trade off).

 

link.jpg

 

result.jpg

Link to comment
Share on other sites

[ATTACH=CONFIG]39445[/ATTACH]

 

One way of getting these fonts into AutoCad tables easily (with the desired font obviously set up to be used in the table and text style properties) is to uncheck the "Use Excel formatting button" when creating the datalink (obviously you lose any Excel formatting then, so it is a trade off).

 

[ATTACH=CONFIG]39446[/ATTACH]

 

[ATTACH=CONFIG]39447[/ATTACH]

 

:DThanks a lot! There is no "System" font listed in my Excel, but I just input the word "System" in Excel's font combobox, and it's OK! Thank you very much!:thumbsup:

Link to comment
Share on other sites

  • 11 months later...
  • 2 years later...

I found an answer on another site that works! Copied directly from the AUGI forum, from user Capt. Computer Crasher:

 

"With Excel linked Tables in 2008, right click on a cell in the table, pick Data Links>>>>

Edit Data Link "Name of Data LInk". In the data link editor under "Cell Content" uncheck "convert data types to text" and under "Cell Formatting" uncheck "Use Excel Formatting" (these are on the right hand side if you do not see them click on the ">" at the bottom right hand corner next to the help button. this will expand the box). when the values set are right hit ok. then click on the table or highlight all cells, right-click and pick "Remove All Property Overrides". this will allow the table style in AutoCAD to have control.

 

To edit globally highlight all the CELLS in the Table (NOT the whole table). right click then choose properties or any other command under right click"

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...