topic Re: Question on how to grab filepath for linked Excel Spreadsheets in .NET Forum

Question on how to grab filepath for linked Excel Spreadsheets

Anonymous — Fri, 22 Jan 2021 16:18:53 GMT

Hello there, I have a question concerning extracting information from a AutoCAD Drawing. This drawing has a link to an Excel Spreadsheet, and my company wants to automate a way to grab data from that excel file and place it in a database. I can do that part, but I'm not all the familiar with using vb.net code to find things within the drawings; my question is there a way through VB to find out if a drawing has anything linked to a outside Excel file and if so, to grab that Excel file's filepath?

Thanks in advance for the help

Re: Question on how to grab filepath for linked Excel Spreadsheets

norman.yuan — Fri, 22 Jan 2021 16:54:44 GMT

There are multiple ways to "link" Excel sheet to a drawing:

1. The old fashion of OLE linked/embedded; which is not recommended any longer years ago, but we could still see people use it to show data in drawing as a table.

2. DataLink, which sets up the drawing to link a Excel sheet/sheet region, and then the data link is used as data source of AutoCAD table.

3. An Excel sheet can be connected to a drawing with ODBC via "DBConnect" command (Data template/Label template). This approach is also not very popular lately.

Let me assume you are talking "linked" by either 1, or 2, but more likely be 2.

1. For Ole linked, you would write code to search drawing for Old2Frame entity, once found, you can test its peoprties "LinkName" and "LinkPath"

2. For DataLink, you loop through DataLinkDictionary of the drawing database to gain access to each DataLink object, which has a "ConnectionString" property, which provides the information about the Excel sheet file.

HTH

Re: Question on how to grab filepath for linked Excel Spreadsheets

Anonymous — Fri, 22 Jan 2021 18:04:31 GMT

Thank you for replying!

For example then, if I open a drawing in VB with this:

Dim cadApp As AcadApplication = New AcadApplication

Dim dwg As AcadDocument = cadApp.Documents.Open(filename, False)

I'd do something like

For each DataLinkDictory in dwg

to loop to find if any ConnectionStrings end in .xls and then grab that Excel filepath to open?

Re: Question on how to grab filepath for linked Excel Spreadsheets

norman.yuan — Fri, 22 Jan 2021 20:02:00 GMT

Firstly, this forum's main focus is on AutoCAD .NET API, which can only be used INSIDE AutoCAD (DLLs netloaded into AutoCAD).

If you are to access data in drawings from EXE/stand-alone app, you can only use AutoCAD's COM API, which, unfortunately, does not have direct API access to DataLink. So, if your task is to deal with DataLink, you need to write AutoCAD plugin DLL using AutoCAD .NET API, which runs inside AutoCAD. I also feel you may need to firstly learn basic AutoCAD .NET API programming, to begin with.

Also, I am not sure how much you understand DataLink in AutoCAD. A DataLink set up in AutoCAD does not necessarily mean that the data in a Excel sheet is actually presented in the drawing, as Table entity, it is just a data link configuration. The data in Excel sheet would only be loaded into drawing and presented if one or more tables use the DataLink (also a table could use more than one DataLinks). So, depending on your actual business needs, you might actually want to search tables in AutoCAD drawing, and then to see if each table uses a DataLink or not. Again, you need to be able to write/test .NET API code to search drawing, for table entities.

You may have rethink your solution's structure/workflow, now that you cannot directly have access to DataLink from extenal app via AutoCAD COM API.

There were a couple of previous discussions on this topic that may be of help:

https://forums.autodesk.com/t5/net/get-datalink-path-from-closed-drawing-c/m-p/6349525#M48773

https://forums.autodesk.com/t5/net/autocad-table-and-datalink/m-p/9473409#M65469

Re: Question on how to grab filepath for linked Excel Spreadsheets

Anonymous — Fri, 22 Jan 2021 20:06:02 GMT

I apologize, I think I posted this topic in the wrong section, I'm sorry.

I think now the more appropriate place for this topic is AutoCAD Customization.

Re: Question on how to grab filepath for linked Excel Spreadsheets

norman.yuan — Fri, 22 Jan 2021 20:08:56 GMT

I think you posted in CORRECT forum, because, as I said, you CANNOT have access to DataLink via AUtoCAD COM API.

Re: Question on how to grab filepath for linked Excel Spreadsheets

Anonymous — Fri, 22 Jan 2021 20:17:48 GMT

Hmm...must be another way then within VB to just simply grab the connectionstring without going into DataLink. I did use earlier code to do ATTOUT and extracted information that way, is there a command I can give AutoCAD to make a .txt with the connectionstrings from the DataLink in it?

Or a more simple way with vb code to get to it?

Re: Question on how to grab filepath for linked Excel Spreadsheets

norman.yuan — Fri, 22 Jan 2021 21:25:20 GMT

@Anonymous wrote:

Hmm...must be another way then within VB to just simply grab ...

You seem to think that VB (or whatever programming language) can do anything to AutoCAD without using/understanding AutoCAD exposed APIs (be it COM API, or .NET API). Think again: with knowing/using AutoCAD API, VB as programming language can do nothing meaningful with AutoCAD/AutoCAD drawing. DataLink in AutoCAD is exposed as DataLink class with .NET API, but is not exposed in AutoCAD COM API, so your said VB app (which is very vague/confusing way to call your app: it should be either EXE app, or DLL loaded into AutoCAD) cannot have access to DataLink, because you use AutoCAD COM API.