Pasting Excel 97 spreadsheet ignores style settings

[eamon.fleming]

I am using paste special to insert an Excel 97 spreadsheet. I have setup a table style with the settings I want but when I paste the spreadsheet it ignores the style settings (title txt size, etc).

It seems to only use the data info from the style & ignores the title settings.
is there any way to add a title to the table after the import?

is there any way to force the style settings? or retain fromatting like excel?

thx

Eamon

[RobertB]

Formatting applied in Excel usually comes in ok when doing a paste special. However, your version of Excel is decrepit. I wonder if that is the cause?

[eamon.fleming]

Robert no doubt it is decrepit! asked the boss yesterday if we could upgrade to Excel XP/2003 & he told me to call IT & see if anyone in the building had a copy we could "borrow" until I get the drawings done

Can anyone confirm that formatting comes across with Excel Xp ---> 2006 when paste special to AutoCAD entities is used?

thx

Eamon

[RobertB]

Formatting does, within limitations. IOW, if the spreadsheet is very fancy, it might not all come across. But text colors & attributes and basic cell formatting are fine.

[Steve Johnson]

I don't think this is dependant on Excel version. A program I wrote to import Excel stuff into AutoCAD supports Excel 97, 2000, XP and 2003 using exactly the same code for getting at the cell formatting information. However, you may be seeing this bug/limitation/design oversight:

When I paste special / entities from Excel XP (2002) to AutoCAD 2006, for cells that have not been individually formatted, the Excel default font is ignored, the current text style is ignored, and the text style defined in the current table style is ignored. Therefore, the text in those cells come in using text style STANDARD. If you set text style STANDARD to use Arial (or whatever), then the spreadsheet usually comes in looking OK. Otherwise, you get the fun job of changing the fonts in a whole bunch of individual table cells.

[RobertB]

... the Excel default font is ignored, the current text style is ignored, and the text style defined in the current table style is ignored. Therefore, the text in those cells come in using text style STANDARD.

Good point Steve. We use a non-Arial default font in Excel, and see this very behavior. But then, we usually use OLE links. (Have you reported this bug to Autodesk?)

[Steve Johnson]

But then, we usually use OLE links.

Don't get me started on OLE! (Too late!)

Ugh, I hate OLE in AutoCAD! (Which is why I wrote my own Excel import program). OLE has been half-baked from the start, and a traditionally rich source of material for Bug Watch. OLE objects in AutoCAD lack a whole range of fundamental features, things that all objects should have. Almost all AutoCAD commands ignore them altogether!

If I am given a drawing from an outside source, containing a linked OLE spreadsheet, just think of the problems I have in maintaining that drawing. To maintain the link I have to have a copy of the spreadsheet file, naturally, but I also have to put it in the exact same location it was at insertion time. If it was a network location, I have to recreate that location, which "normal" users are not usually empowered to do. So, I need to find out what that location is. But AutoCAD won't tell me! OK, I'll write something in LISP to find out. What's this? No API for OLE objects? Grrr!

I'll tell you what I have to do to programatically find out where that spreadsheet has to go. I save the drawing as a DXF file (and resave as DWG so I don't confuse my user later). I read the DXF file, looking for an OLE2FRAME object. Then I look through stuff like this:

310
8055886E58D7DE197840D019959D464B7D400000000000000000A0879BC48ADF
310
7F40D019959D464B7D400000000000000000A0879BC48ADF7F40483CF21DA47C
310
7A400000000000000000886E58D7DE197840483CF21DA47C7A40000000000000
310
00009330AC110100000000010000010000000100000001000100000000600000
310
D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900
310
0600000000000000000000000100000001000000000000000010000002000000
310
01000000FEFFFFFF0000000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
310
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

With a little sleight of hand, I can locate the path in that mess. It's not stored as plain text, it's in among the numbers, which need to be converted from hex to ASCII codes. I have to cross my fingers and hope that this horrible undocumented kludge keeps working with the next releases of AutoCAD and Excel, as well as other OLE-compatible apps.

(Have you reported this bug to Autodesk?)

I'm not 100% sure I'd call it a bug. I'll have to ponder that one. I think it should go in the wish list, though.

[eamon.fleming]

Steve/Robert looks like I'm knackered either way? I was thinking on the weekend I might try exporting the Excel sheets into Access I try the Table/Field link thing. That way I can do my formatting in the Table & just pull the data from Access?

Think this would work?

Eamon

[RobertB]

Steve/Robert looks like I'm knackered either way? I was thinking on the weekend I might try exporting the Excel sheets into Access I try the Table/Field link thing. That way I can do my formatting in the Table & just pull the data from Access?

Think this would work?

Eamon

I do not know.

[RobertB]

If I am given a drawing from an outside source, containing a linked OLE spreadsheet, just think of the problems I have in maintaining that drawing. To maintain the link I have to have a copy of the spreadsheet file, naturally, but I also have to put it in the exact same location it was at insertion time.

Why don't you "simply" change the source via the OLELinks dialog? (Granted, I wish I had more API access.)