Data linking an excel document with a relative path?

[Anonymous]

I'm trying to use a datalink with an excel document on an AutoCAD LT template I'm making. What I'm trying to do is use the Excel document to put in basic information that is getting copied across multiple layouts to reduce the amount of redundant work I do. As well as perform some frequently used calculations that need to be displayed and included in the CAD prints. 

Where I'm getting stuck is that when we start a new project we take a general template folder and copy it to our active projects folder which we then rename with a client number. So what I need to be able to have happen is AutoCAD LT able to recognize the data linked file even after being moved. I've been able to occasionally get it to work but not 100%, so I'm not sure where I'm going wrong.

I'm using AutoCAD LT 2020 

[pendean]

Unless I am mistaken, what you are describing is already built into SheetSet Manager along with the use of FIELD command, no need to use Excel for the tasks.

Can you post a sample Excel and a sample DWG file showing what goes where?

[Anonymous]

Sure, here's an example of what I want to do. I tried looking into what options were in the FIELD command and while I did find some things that provided exactly what I was hoping to accomplish I could not find anything that addressed what I wanted to do for many others.

I've had no trouble getting Excel to link, and auto-update on single documents. It's when I try to use that as a template and copy and paste it into a new location is when things break.

[pendean]

FIELDs do not work in copy/paste scenarios, and AutoCAD/LT doesn't really use templates in that way either, they are just stand-alone DWG files with a T instead of a G to stop you from accidentally saving to it directly.

Thanks for sharing your files: you can ignore my previous post about SSM, you are doing something else altogether.

[Anonymous]

Understood, thanks for taking the time to look.

[steven-g]

You would have to change the source file path if you start moving documents to different folders, especially if you are working on a server. It might help if the drawing file and the excel file are in the same folder, but I can't test that as I'm only on a local machine. I would, however, warn against using 'merge and centre' in excel, it is the cause of countless problems in excel and should be avoided.

I did manage to change the source to my local folder and the data updated in AutoCAD just fine. You talk about 'fields' in AutoCAD but the excel data in your drawing has been inserted as an OLE link, is that just how it appears as you sent the files or do you use OLE links? Data linking uses tables and you can link fields to table cells but only for numerical data, they won't read text or other characters.

[Anonymous · Accepted solution]

Hi Steven, thanks to your comment about merged cells I was able to determine the reason why AutoCAD was having issues. I have dealt with Excel quite a bit and it struck me as pretty odd that Excel would have problems with merge across, so I made the assumption that it was a typo and you meant to write "AutoCAD" instead. 

Here's what I found out. When Excel merges cells it gets treated as a single cell both visually and by the programming functions of VBA so if you merge A1:C3 it just gets treated in Excel as A1. AutoCAD, on the other hand, does not seem to be able to translate this so for a merged cell comprising of A1:C3 if you try to import a table using A1 as the range in the same manner you would reference that location in Excel it does not translate it correctly. However, if you tell it A1:C3 as the range for a table on a merged cell that was made from those cells in Excel AutoCAD will correctly display the merged cell. 

How this was causing me problems is I was referencing the table selection in AutoCAD as though I was doing it in Excel, which made some cells display correctly, but the merged cells show up blank. Now that I know how AutoCAD needs to be told where to look for the merged cells referencing an Excel sheet with a relative file path works exactly as I wanted it to. 

I've attached a DWG file with the Excel file that shows how to correctly reference the table. With a relative file path you can copy this to anywhere you please as long as the Excel file remains with it (even a network location, I tested it) and all that needs to happen once you open the new file is update the datalinks.

[steven-g]

---

@Anonymous wrote:

Hi Steven, thanks to your comment about merged cells I was able to determine the reason why AutoCAD was having issues. I have dealt with Excel quite a bit and it struck me as pretty odd that Excel would have problems with merge across, so I made the assumption that it was a typo and you meant to write "AutoCAD" instead. 

---

Actually I did mean Excel, there are a great many people who advise against merged cells in excel, for a number of reasons. But it's nice to see you have managed to find a way to get this working.