Community
AutoCAD Forum
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Data Link Excel sheet issue

20 REPLIES 20
Reply
Message 1 of 21
dmmeehan
7148 Views, 20 Replies

Data Link Excel sheet issue

I have made a data link to an excel file. Then I bring it in as a table. I then adjust the text height and remove all formatting from the table. Unfortunately, after closing and re-opening the file, the table changes. I lose formating/cell alignment/text height. It isn't even consistent across the table. The lost formatting is only on the 2nd split of the table, the first side retains the corrected formatting. I have selected the option to not update excel formatting. I don't even get a notification the table is updating. There haven't been any changes to the excel sheet either....Please help!

20 REPLIES 20
Message 2 of 21
dbroad
in reply to: dmmeehan

Try repeating the tasks to figure out what's happening.

 

1) Set up a data link.

2) Use the table command to create a table from that datalink.

3) The only formatting should be the one in the tablestyle settings.

4) Select the cells you want to format.  Choose the cell style you want.

5) Save, close and reopen the drawing.  Does it not look the same?

6) Modify the excel file to change fonts there.

7) Update your data links in your drawing.  The formats should remain as you set them in the drawing.

 

Architect, Registered NC, VA, SC, & GA.
Message 3 of 21
dmmeehan
in reply to: dbroad

I needed to set up the table style. The second half of the table was assuming the default tablestyle. After making a new table style that replicated what I already set up in the excel file and data linked table, the error no longer occurs. It's still a bug though. The table should remain as I left it...

Tags (1)
Message 4 of 21
dbroad
in reply to: dmmeehan

I'm not sure I would call it a bug.  Datalinks depend on table styles. I always design the style and then create the datalink table. 

 

So is this problem solved?  If you feel like a bug report is necessary, it would do more good at autodesk.com/submitbug.

Architect, Registered NC, VA, SC, & GA.
Message 5 of 21
dmmeehan
in reply to: dbroad

Then why doesn't the tablestyle apply to the first half of the split table? It is only the split side that assumes the tablestyle. Not only that, but if I try and remove formatting from the table while it is split, the formatting is only removed from the first part.

Message 6 of 21
dbroad
in reply to: dmmeehan

If you want to continue this thread, it would be helpful to have a sample to converse about.  Attach a drawing and excel pair.  This is the first you mentioned a split table. 

Architect, Registered NC, VA, SC, & GA.
Message 7 of 21
Volker_C
in reply to: dmmeehan

Hi,

Can you post the files as suggested by Dbroad? It would help with troubleshooting.

 

Cheers,

Volker

Please let us know if your questions have been answered by selecting, "Accept as Solution"


Volker C.
SR Technical Support Specialist, KDE
Autodesk Customer Council | Only One AutoCAD | AutoCAD Blog

Message 8 of 21
msmith
in reply to: Volker_C

It's definitely an ongoing bug, since 2012/13. If you split a table, everything besides the first table section will lose text formatting on an update (I've noticed it when using SSM sheet list tables).

 

Another thing, I never get a prompt/alert if the Excel file has been modified. I did back in 2008, but not the last few versions. Kind of defeats the purpose if it doesn't auto-update and doesn't let you know it needs updating.

 

This is the post I made on the topic:

http://forums.autodesk.com/t5/AutoCAD-2013-2014/datalink-Cell-forced-to-excel-font-quot-Start-with-E...

 

Never got an answer, I just kind of assume the stupid thing is completely broken.

Message 9 of 21
Jay_B
in reply to: msmith

"Another thing, I never get a prompt/alert if the excel file has been modified. I did back in 2008, but not the last few versions.
Kind of defeats the purpose if it doesn't autoupdate and doesn't let you know it needs updating."

Check to see what the System Variable DATALINKNOTIFY is set to.
http://docs.autodesk.com/ACD/2010/ENU/AutoCAD%202010%20User%20Documentation/index.html?url=WS1a91938...
C3D 2018.1
C3D 2016 SP4

Win 7 Professional 64 Bit
Message 10 of 21
msmith
in reply to: Jay_B

It's set to "2".
Message 11 of 21
dmmeehan
in reply to: msmith

Mine is also set correctly (2). We have also experienced this issue while linking to an excel sheet that is on our sharepoint server. I'd post an example but the documents in question are proprietary... I've considered requesting the software package from Dotsoft but haven't had time to test the demo for functionality.

Message 12 of 21
dbroad
in reply to: dmmeehan

You can't recreate the problem with dummy data?  Come on now.

Architect, Registered NC, VA, SC, & GA.
Message 13 of 21
msmith
in reply to: dbroad

Attached are my sample files.  The only time I've noticed the split table formatting issues are with Sheet Set Manager "Sheet List Table".

 

For the record, here is the text of my previous post documenting other DL issues:

_______________________________________________________________________________________________________________________________

For background, I've been using datalinked excel tables since their inception in 2008.  I found them buggy initially, but they functioned wonderfully in 2013.

 

For all of our projects, we have been creating them with the formatting option "Start with Excel Format, do not update".  This function appears entirely broken now.

 

Issues I've found:

  1. There is no longer a pop up dialog to alert the user if a datalink needs updating
  2. If you override the text style in a cell (I've tried making it STANDARD, ARIAL, VERDANA, SIMPLEX, ROMANS), the displayed font does not change and appears frozen to whatever was saved in the excel file.
  3. If you update the datalinks in the drawing, even if "start with excel format, do not update" is selected, any font format changes in the excel file will come into the drawing (in acad, if the cell has been forced to text style with a width factor, that width factor will display).
  • Tried unlocking (content and fomat) a cell to see what was going on - it appears though it takes on the text style specified in the table style (or whatever was selected in a cell override), the actual font has been forced to whatever was last saved in the excel file.

 

Removing Property Overrides will reset the cell to the font size and style specified in the table style BUT the table will still only display the font last saved in the excel file.

 

FYI, tried all of this with an XLSX file, same results.  I am using C3D 2014 SP1 x64; MS Office x86 (32-bit) - don't think that is the problem since it didn't cause a problem with C3D 2013 x64?

________________________________________________________________________________________________________________________________

Message 14 of 21
dbroad
in reply to: msmith

Thanks for your detailed explanation.  That should be enough to get Joseph looking into it.  I can see your point about the fonts.  Trouble with fonts in tables though is how those fonts might be modified.  If the content is locked, it could actually affect the font since the table cell text entities are mtext and since the font codes can be embedded.

 

For example, if I select the cell that contains the simplex text, change the cell type to unlocked and then highlight the text, the font can be changed in the mtext editor of the cell.  Once the cell is format is changed, the cell can be re-locked but Excel updates swap the text font codes back.

Architect, Registered NC, VA, SC, & GA.
Message 15 of 21
msmith
in reply to: dbroad

any updates/solutions?
Message 16 of 21
vikfann
in reply to: dmmeehan

I can link Excel spreadsheets to a table just fine, it's the cell formatting in excel that doesn't come out properly in AutoCAD.

 

I have a lengthy spreadsheet with several colums an rows. Most of the data looks fine, but other data doesn't follow the cell format I've set up in the spread sheet.    The cell formatting seems to have a mind of it's own once linked to a table in AutoCAD.  I've unlinked the excel file, re-linked it, tried using AutoCAD table command to modify yhr table unsecussfully.   Kinda bummed, thought I knew this stuff!!

 

Any thoughts?

 

Frustrated Designer in SoCal!!

 

 

 

 

 

 

Message 17 of 21
Sportyyyy
in reply to: vikfann

All of your issues were documented in my post above (msmith).  It's a known bug and there is no solution except to get the font formatting exactly as you want it in Excel.  Remove all borders so that you can assign table styles in Excel.  Attached is write up I had to do for my department but I've summariz the Excel essentials below.  If you're looking for more control over cells in ACAD, create "CELL STYLES" - you can assign these as overrides in the Table properties.

 

  • Aesthetic formatting should be kept to a minimum
  • Do not use “General” format for cells containing numbers.  AutoCAD will recognize those cells as numbers and apply the drawing’s precision settings to them.
  • Fonts must be set to Arial.
  • Column/Rows should not be “Hidden”.

Do not apply formatting anywhere outside of table – AutoCAD will interpret it as data and attempt to include it if “whole sheet option” is checked.

Message 18 of 21
msmith
in reply to: msmith

As a solution for the "Sheet List table" from Sheet Set manager (split tables, cells will lose any property overrides once updated), create a CELL STYLE to force Justification, Color, Bolding, whatever.  Create however many you need.

Apply those cell styles to the cells in the split section of the table (this is technically a property override but it seems to hold cell style overrides, just not individual font, justification, etc. overrides), then update the Sheet List Table. Formatting will then hold after updates.

I believe this will only work for sheet list tables since they are not linked to a formatted excel table.

Message 19 of 21
mariociaralli
in reply to: Sportyyyy

 I've tried and tried, to no avail, to get this set up.  I once again started from scratch, and used your method (and .doc write-up) and have once again run into formatting issues.

 

The crux of the issue is this: Once the excel data is set up, and the Data Link created in AutoCAD, everything looks great - its only after an edit (say, inserting a few rows and adding text) and then saving and updating the Data Link in AutoCAD where the formatting gets blown away.  At random, cells content will shift alignment, previous cells / data that was displayed correctly before the update, now have larger text height, cell height etc. 

 

The behavior is somewhat random, but usually always the same.  The last couple of cells and the text within them will get larger, and some text mid-table will lose its alignment.

 

One of the solutions that I though was working in the right direction was once the Excel table was set and the Data Link extablished, if any editing needed to be done to the Excel data it was done by inserting rows to the middle of the table, then shifting text around.  This might have been driven by the 'Set Print Area' method I was trying to get to work too.  Once the Print Area was set, I thought inserting to the middle of the table was a better way than adding data to cells below the original Print Area.  

 

Nothing has worked.  I dont mind a bit of a work around to get this done, as it would still be helpfull to keep our Sheet Indexes in Excel.. but I just cant get a method / workflow down.

 

Thoughts?

 

-MLC

 

Ps I'm using Excel 2013, AutoCAD 2009 and Revit 2015.  Ive tried using AutoCAD 2013, 2015 too.

 

 

Message 20 of 21
CVP25
in reply to: mariociaralli

Ya. no solution found till now.

 

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost