Excel Datalink Formatting

[aaron.jonesSAP83]

Hoping someone can help me out with a workflow solution.  I've tried using both tables from datalinks and OLE links, but both of them have serious problems.

First the datalink issue - The schedules I'm importing from have lots of hidden columns that contain formulas and intermediate steps that we don't want to plot on the sheet.  When adding a table with the datalink, these rows & columns come in regardless of whether they're hidden in Excel.

Borders also don't match Excel formatting at all.  I have the "Use Excel formatting" box checked, but all borders come in at the same linetype, whether I have borders on thick, thin, dotted, or off in Excel.  Number formatting also doesn't match Excel. I have the cell format set to "General", and an integer displays with 6 decimal points (which don't show in Excel).  I also have a custom format set to add units to the end of a calculated number, and it shows in some places but shows with 6 decimal points and no units in other places.

Now the OLE link issue - it plots like garbage.  I've set to all three quality settings, and all of them are garbage.  I zoom in as far as I want in AutoCAD, and I see clean, crisp lines.  I plot with the objects set to high quality, and there's a yellow aura around all of the text because it's converting to raster.  If it's set to monochrome rather than high quality, black & white get reversed and it looks like a Windows 3.1 Tetris game.  The quality setting in the middle is about halfway in between those two horrors (text is at least black, but it still looks like Tetris).

Is there a setting I'm missing somewhere?  I'm sure I'm not the only one who's run into these problems.

[nikm42Q9N]

are you copying and pasting the OLE chart from excel?

Try Paste special it makes a better chart

[aaron.jonesSAP83]

I am using the Paste Special and setting it as a link (for OLE).  It looks perfect on the AutoCAD screen, but converting to PDF is the problem.  I've attached a screenshot of what it looks like in AutoCAD vs in PDF (both zoomed in to about the same level).

[nikm42Q9N]

Under Paste special use the tick the box for paste link then select AutoCAD Entitles it should come in how you have it in excel and make into AutoCAD lines. I would bring it in under a layer with no LW then use your border layer around the imported chart to make the border 

[aaron.jonesSAP83]

Doing that presents all of the same problems as using the datalink.  Rows & columns hidden in Excel are showing in AutoCAD, and every cell gets a thin border, regardless of what it's set to in Excel.  In the Excel screenshot, you can see that there are only 7 columns showing before the Φ column.  In the AutoCAD screenshot, all columns, regardless of being hidden, are showing.  It also brings in a border for every cell, regardless of borders being turned off in Excel.  I did zoom in really deep on the PDF, and I noticed that thick borders in Excel actually come in with a thinner lineweight in AutoCAD.  Is there a mapping menu I'm missing somewhere that says "border weight X in Excel equals lineweight Y in AutoCAD"?

[nikm42Q9N]

If you could post a example chart maybe blank ill give it a shot 

[aaron.jonesSAP83]

I've attached an Excel file that should incorporate the issues I've described (I can't attach the spreadsheet I'm using since it has custom formulas and the forums won't let me upload it).  Interestingly, I tried using the paste without a link, and the hidden column issue didn't manifest itself.  It looks like pasting as a link causes all of the columns to unhide.  The border issue manifests itself regardless of whether it's pasted as a link or an import.  The number formatting issue also manifests itself.

[nikm42Q9N]

Sorry ive been at a job site.

So I've never had to get rid of borders on a chart before. Don't know why you want them gone but that's not my business. When you get the chart in Acad Highlight all the cells within in the main border of the chart. (don't selected the whole chart just the cells) once all are highlighted right click and select borders, on the bottom above the black box there are four options hat look like excel border options select no borders. then highlight the groups you want borders in and do the same thing in reverse. sucks it cant just be imported like you want (im sure it can somehow) but this workaround is only a few clicks 

To Change the Appearance of Cell Borders | AutoCAD 2016 | Autodesk Knowledge Network

[cwr-pae]

Acad tables do not use the column hide from spread sheets. As a work around:

Add a new 'tag' or 'page' to the excel file, it may have to be the first one in the file. Use data base look up formulas to  extract only the required data to this tab. Save the excel file for future editing. Save the excel file as a *.csv to strip all formatting. In Acad, create a data link to the *.csv, create a table style to format the style as required. Create a table with the style and data link. Manage the table appearance in Acad.When changing the excel file, update the data tab if required, and save over the older *.csv file and update the data link in excel.

[aaron.jonesSAP83]

That solution seems like it'll work for the hidden rows/columns issue (might have to write a few VBA subs to get it to copy properly, but shouldn't be too tough).

My issue with the borders isn't a "they're there and I don't want them there" issue.  The problem is that they're everywhere, I only want them somewhere, and I want several different areas to have different thicknesses/linestyles.  I don't have a ton of experience with them, but from what I can tell, tablestyles are a one-size-fits-all kind of border control.  I typically have several of these schedules in a project, and it would be incredibly cumbersome to change the border styles for each schedule to look uniform.

[pendean]

@aaron.jonesSAP83 It appears you are ready to just reference/ live-link your Excel documents inside your AutoCAD files, keeping both separate nd using all that Excel has to offer without resorting to the limited AUtoCAD Tables objects.

[aaron.jonesSAP83]

That’s exactly what I’m looking for.  Is there a workflow that would allow me to do that?  I don’t need the ability to edit the data within AutoCAD.  I just want the excel sheet to print on my drawings the same way they print from excel.

[aaron.jonesSAP83]

What?