topic Re: Excel DataLink Updating error when adding rows in Excel Link in AutoCAD Forum

Excel DataLink Updating error when adding rows in Excel Link

Laurie1 — Wed, 19 Feb 2020 22:45:09 GMT

Hello all, I have tried this both ways by defining a range name in excel and linking to it and also using link to range in the Modify excel Link dialogue in Autocad. I am using AutoCad 2020, I have also tried this in 2017, 2018. It has never worked correctly for me, I hope there is some easy way of making this work. If you look at the images below you see the row doesn't update correctly, the extra row is on line 24 and DatalinkUpdate puts the extra row at the bottom and does not shift other rows down accordingly. I am using Version 16 excel also if that makes any difference. Thank you.

Updated after adding row, not adjusting rows correctlyExcel doc after adding row, not where it should be in the datalink

Re: Excel DataLink Updating error when adding rows in Excel Link

Laurie1 — Thu, 20 Feb 2020 21:14:55 GMT

Re: Excel DataLink Updating error when adding rows in Excel Link

Anonymous — Thu, 20 Feb 2020 21:32:21 GMT

I've used a lot of excel/acad datalinks for quite a while, and having to insert or remove rows or columns in existing ranges is always a headache, especially if there are merged cells. The only thing I have found that works is to create a new table from the datalink. Sometimes "miraculously" the 1st (previously existing) table will then update correctly, but this is rare. Mostly I just replace the table in ACAD & move on...sorry this isn't much help (or none if you want to be precise).

Re: Excel DataLink Updating error when adding rows in Excel Link

ChicagoLooper — Fri, 21 Feb 2020 01:22:52 GMT

As an experiment, put some dummy numbers or text in a cell on row 24 so the AutoCad table will have some ‘data’ to link onto. Later, if it links up, fingers crossed, you can delete the dummies.

Re: Excel DataLink Updating error when adding rows in Excel Link

Anonymous — Fri, 21 Feb 2020 16:33:05 GMT

OP- if this works, let us know exactly what you did- I've inserted rows into Excel linked range, and populated the cells with necessary data, and still get glitches when updating the linked ACAD table....would be awesome if someone solves this!

Re: Excel DataLink Updating error when adding rows in Excel Link

Laurie1 — Fri, 21 Feb 2020 17:33:56 GMT

Okay thank you all, I am working on solving this. Will update if I ever get it figured out. It can be time consuming and frustrating to have to detach unload and reload the range in the spreadsheet if you have to add a row. I appreciate your suggestions. I am going to try some things...

Re: Excel DataLink Updating error when adding rows in Excel Link

Anonymous — Fri, 21 Feb 2020 20:11:01 GMT

Maybe I am misunderstanding- I never have to detach and re-attach the link, just insert a new table in ACAD using the same datalink. I assume you use a named range in Excel(?)

Highlight all cells in the range and type a name in the name box...see attached pdf

In the datalink manager select link to a named range....see page 2 of pdf

If you insert or delete rows or columns within the named range it will expand to include the new cells

The issue arises (for me) when updating the link in ACAD, the table will experience any of several glitches, including broken merged cells, text size shrinking (or growing), etc. Sometimes "repair" is possible using format painter, mostly not, and I have to insert a new table using the same datalink, re-do any re-sizing of rows/columns, and delete the broken table.

Maybe you use a different process(?)

Re: Excel DataLink Updating error when adding rows in Excel Link

Laurie1 — Fri, 21 Feb 2020 21:28:06 GMT

That is something I was trying today is just detach the link from the table but not actually delete the data link definition, yes I have used the named range and also range like A1:H28, then just re-copy/paste the cells like you said and either reattach to the link like you said that I left or I have deleted and created new link. I have been trying many different ways to figure out how to make it work right. Yes, still get weird glitches as you mentioned. Trying to find the right combination.

If i use data link as excel file and not AutoCad entities it works perfectly but then I don't get the control of Colors and so on.... I would like, and am determined to figure out how to make linking the excel range as AutoCad entities work correctly when updating.

Re: Excel DataLink Updating error when adding rows in Excel Link

ChicagoLooper — Fri, 21 Feb 2020 23:58:00 GMT

Works for me. I was able to add a blank row.

First I inserted a row in Excel and SAVED the spreadsheet. The 'save' is important. Then I did this:

Go to Annotation Tab=>Tables Panel=>Datalink Icon (Image 1)
Right click on the Data Link to be edited and select EDIT from the shortcut menu. (Image 2)
Verify Excel file path is correct, then Link to Range to modify the NEW range in Excel. OK to Exit. (Image 3)
Go to Annotation Tab=>Tables Panel=>Datalinkupdate (Image 4)
On command line select 'D'. Alternatively you may you may select 'K' (Image 5)

Image 1

Image 2

Image 3

Image 4

Image 5.

A blank row was inserted into the AutoCad table in the correct location. (Note: The table used is an AutoCad entity not an OLE.)