Data Link Not Working

[Bill Tillman]

I created a new data link to a range of cells in an Excel file and was able to place a table in the model space. All was well. I needed to make a change to the range of cells which proved problematic and the only way I found to do it was to start over from scratch. Now when I try to insert a new table using the Insert Table window, I select the data link source and click OK, but then nothing happens. The Insert Table dialog just sits there.

 

I've been reading that Excel data links are hit or miss at best. In the past I've used them fine but suddenly, there's no proof in the pudding. Been testing and retesting with this many times and sometimes it works fine, and other times the Insert Table window just totally misbehaves. Nothing gets inserted, the menu window closes and not a single error message shows up to indicate what went wrong. Then there are other times when the Insert Table window does nothing but sit there, no matter how many times I click OK. Only the Cancel button works and since it just cancels, I'm back where I started with the file. This is the kind of stuff which is just unpredictable.

 

Edited October 14, 2018 by Bill Tillman

[Cad64]

Have you tried running an Audit on your drawing? I have found that sometimes when trying to copy and paste from one drawing to another or even from one layout to another, the copy/paste will fail. But after I run the Audit and fix whatever errors are present, then everything works fine. I know that copy/paste is not what you're asking about, but it's one of those things that happens and leaves you scratching your head. So maybe there is an error that's keeping the data link from functioning?

[Bill Tillman]

I think I have resolved this one. It appears that if there are merged cells which do not get included completely in the Import Range, things will go south. This means massive reformatting of the Excel file so that I can get it to appear properly. Now let me explain that point fully. What I seem to be finding is that the cells A1:R1 are merged into a single cell. The last column in that range is empty, Column R, so I deleted it. That did get rid of the column but AutoCAD seem to choke on it nonetheless. So I unmerged the cells in the excel file, deleted the column and then remerged and centered the cells in the first row of the range, which is serving as a title. This appears to resolve the issue, but now instead of bragging to the client that I have it all completed in tomorrow's meeting I will have to explain it's not finished due to reformatting needed in about 12 Excel files. They don't tell you this in the help screens or the YouTube HOWTO videos.

[Cad64]

What a pain in the neck. At least you figured out what's causing it though. Now you just have to reformat all those Excel files. Fun times.

[SLW210]

What version of Excel do you use?

I had a similar problem, but the original was created in a much older version (newer data and saved to new format by someone else), when converted to use with Excel 2016, it would hang when using Data Link. I believe it was the merged cells. Worked fine using the older Excel format, 2003 I believe.

[Bill Tillman]

I'm using Excel 2007 here, with AutoCAD 2018 on a somewhat decent Xeon CPU computer. I just created a new worksheet in the Excel file and was very careful to make sure any merged cells were completely within the range I gave AutoCAD. Trouble is when I click on OK in the Insert Table window, it just sits there. No table is inserted. I can click the Cancel button and that works. Several other tables I inserted this morning using Data Links worked fine. But this one is a no go.

 

Honestly, I really hate this kind of stuff. AutoDesk pretty much charges for the air you breath while you're using their software these days and yet it's still full of bugs. Lot's of Dev's on this forum will disagree, but tell that to my client who has no understanding about bugs in a software app he paid $2K for and he expects results, PDQ.... Oh well, same B.S., different day.

Edited October 15, 2018 by Bill Tillman

[Jef!]

datalinks/tables are one of the buggiest things autodesk created... even much more unreliable in the past. on cad 2015 with excel 2016, it is slow as hell. I never use them. BUT one tip I can share that might save some hassles to you guys. If You create a table linked to a data link ranged like A1: a4, in my previous job 1 thing I saw often was that changing the range of the data link to a1:a5, the table would not update from displaying 4 lines to displaying 5 lines. It would still display only the first 4. However creating a table on a named range, redefining the named range in excel would update the table in cad. 1 sure thing, I prefer redefining a named in excel rather than redefining a data link in CAD. Also if multiple drawings refer to the same excel spread sheet, it could mean redefining named ranges of 1 excel rather than the range of multiple data links in multiple dwgs.

 

I just tested out, both methods worked "fine" (if few seconds delays can fall in the "fine category"), but both had another bug. On table insertion, all cells are left aligned. When lines are added in a table by selecting a bigger range, all new lines appeared with a centered justification. Either you fix them 1 by one with their clunky editing, or you erase the table and add a new one. Basically, few years back because of a bug you had to redo the tables to show the new lines, in 2015 you have to redo the tables because the text justification gets messed if you dont. Whats new? Updated bugs. 

[SLW210]

99.9% of the time they work just great for me, 100% if I have been the only one touching the Excel file.

[mrgalfield]

I got the same issue and I worked after uninstall and reinstall Microsoft office and restart the PC.

[MarilynSparks]

On 10/14/2018 at 9:25 AM, Bill Tillman said:

I think I have resolved this one. It appears that if there are merged cells which do not get included completely in the Import Range, things will go south. This means massive reformatting of the Excel file so that I can get it to appear properly. Now let me explain that point fully. What I seem to be finding is that the cells A1:R1 are merged into a single cell. The last column in that range is empty, Column R, so I deleted it. That did get rid of the column but AutoCAD seem to choke on it nonetheless. So I unmerged the cells in the excel file, deleted the column and then remerged and centered the cells in the first row of the range, which is serving as a title. This appears to resolve the issue, but now instead of bragging to the client that I have it all completed in tomorrow's meeting I will have to explain it's not finished due to reformatting needed in about 12 Excel files. They don't tell you this in the help screens or the YouTube HOWTO videos.

 

You have single handedly saved me weeks of frustration. I had to signup for this forum simply to thank you.

[BIGAL]

Not sure about reading a merged cell excel range but can make a Table by reading direct from excel, using a select range, I have that thanks to FIXO, could you post a sample Excel with 2 sheets one sheet with merged the second sheet unmerged. Or 2 Excell's.

 

Its not a ole link but a hard make a table.