Repath the excel reference through VBA

[Anonymous]

Hi, everyone.

I'm trying to repath the external excel data reference through VBA, the first step to do is reading the path, I try to get the path by using this code:

Dim iBlk as acadblock
for each iblk in thisdrawing.blocks
on error resume next
if iblk.isxref then
debug.print iblk.path
end if
next

but got nothing output. Is that an excel data link not a xref? 

I'm so confused about that because I could see many xref info. when I used the xref command. it would be very helpful if anyone give me an example. Thanks for advanced.

[Anonymous]

could you post your dwg (AutoCAD 2010 version) to let us help you?

[dbroad]

Where are you looking for output from debug.print?  Do you have the immediate window open?

[Anonymous]

Hi, thanks for the notice.

I can't upload the dwg file because it's kind of confidential.

But what I could tell is that I used Insert-> data link  to create an excel linking data, using table command-> from link data, to put a table in my drawing.

Then I used xref command , could be able to see those external references I quoted. (I want to change the path by running VBA once. )

I've tested that If I used Insert-> attach to quote reference dwg , then my VBA could get the path & name of the external ref. object. But still nothing form the Excel reference.

I'm pretty sure I had the immediate window open.What I want to know is that, is there any difference between the referecne dwg and reference excel data in VBA for Autocad, how am I supposed to get the path or name of the excel data?

Thank you.

[dbroad]

I'm not sure where datalinks are stored but they would not be considered an insert.  If they are tables, you might try looking for the table objects.  The link and the associated data would probably be attached as a reactor and as a dictionary entry.

[norman.yuan · Accepted solution]

You original question isn't very clear: Excel sheet can be inserted into drawing as OLE object (embeded or linked), or can be referenced as DataLink. However, in either way, it is not an AcadBlock, so your code shown in your original post does not work, as you already know.

Now that you have clarified it is a DataLink to an Excel file, then the Excel reference is actually a dictionary entry in named dictionary of the document's database.

Since there is no generic UI to access named dictionary, AutoCAD shows the DataLink in Xrefernce manager, thus mislead you to think it is an Xreference.

If you want to re-path the Excel file of the DataLink, you need to go to ThisDrawing.Database.Dictionaries and loop through the dictionary entries for the DataLink dictionary with name/key as "ACAD_DATALINK". Then within the "ACAD_DATALINK" dictionary, you search the entry that has name/key as the DataLink's name. That entry represents an AcdbDataLink object, which does not have COM object in AutoCAD object model. You need to use CAO library to access it, which makes things a bit complicated. 

[Anonymous]

hi,

i am trying to create datalinks. can you explain how to do that with code snippets please. i search the internet but couldnt find anything. it would be greate help. thanks in advance.

[Anonymous]

I'm trying to do the same, and I'm nearly there however, I've reached a stumbling block.

I'm able to modify the data definition for data link entities but the entity data-definition structure for data links appears to be quite unusual so I'm unsure exactly how to set the connection string.

The following code shows the approximate progress I've made so far for this:

    Dim linkName As String
    linkName = "test"
    Dim DataLinkDictionary As AcadDictionary
    Set DataLinkDictionary = AutoCAD.AcadApplication.ActiveDocument.Dictionaries.Item("ACAD_DATALINK")
    Dim DataLinkIterator As AcadObject
    For Each DataLinkIterator In DataLinkDictionary
        
        If (UCase(DataLinkDictionary.GetName(DataLinkIterator)) = UCase(linkName)) Then
            With AutoCAD.AcadApplication.ActiveDocument
                .SendCommand _
                    "(setq datalinkdefinition (entget (handent " & """" & DataLinkIterator.Handle & """" & ")))" _
                        & vbCrLf
                .SendCommand _
                    "(setq datalinkdefinition (subst " & _
                        "(cons 302 ""C:\\Users\\mark.j.fernandes\\OneDrive\\Documents\\employment\\21.6.19 AutoCAD project\\TestTableSpreadsheet.xlsx!Sheet2"") " & _
                        "(assoc 302 datalinkdefinition) datalinkdefinition ))" & vbCrLf
' NEED TO DO MORE THAN SIMPLY CHANGE THE ELEMENT WITH KEY 302--more complicated than that.
                .SendCommand _
                    "(entmod datalinkdefinition) " & vbCrLf
'                .SendCommand "DATALINKUPDATE" & vbCrLf
            End With
            Exit For
        End If
    Next

You may need to set a VBA project reference for the 'AutoCAD 2019 Type Library' library (stored @' C:\Program Files\Common Files\Autodesk Shared\acax23enu.tlb' on my computer), to get the above code to work.

Can anyone help me further with this?

I've had a look at the CAO library (as suggested in this thread in the post here) but it doesn't seem to be applicable to data links; instead, it seems to possibly apply to label links. Has anyone successfully used the CAO library for this? 

If I had the source code to find out specific details on how the .NET ConnectionString property of the DataLink class worked, or if I could get the full specification for how DataLink entities are stored in the DXF format (or in the Lisp data-definition list structure), then I think I could probably figure this out. Alas, these things don't seem to be forthcoming from my fairly intensive internet research.

Any help?

Thanks,

Mark

P. S. The possibly unusual entity definition-data structure for the DataLink entity can be seen from the following dump of a  DataLink entity's definition data: 

(
  (-1 . <Entity name: b6e2b56360>)
	(0 . "DATALINK")
	(5 . "216")
	(102 . "{ACAD_XDICTIONARY")
		(360 . <Entity name: b6e2b56420>)
	(102 . "}")

	(102 . "{ACAD_REACTORS")
		(330 . <Entity name: b6e2b56350>)
	(102 . "}")

    (330 . <Entity name: b6e2b56350>)
	
	(100 . "AcDbDataLink")
	(1 . "AcExcel")
	(300 . "")
	(301 . "Data Link\ntest\nC:\\Users\\mark.j.fernandes\\OneDrive\\Documents\\employment\\21.6.19 AutoCAD project\\TestTableSpreadsheet.xlsx\nLink details: Entire sheet: Sheet1")
	(302 . "C:\\Users\\mark.j.fernandes\\OneDrive\\Documents\\employment\\21.6.19 AutoCAD project\\TestTableSpreadsheet.xlsx!Sheet1")
	(90 . 2)
	(91 . 68943873)
	(92 . 1)
	(170 . 2019)
	(171 . 6)
	(172 . 23)
	(173 . 11)
	(174 . 37)
	(175 . 18)
	(176 . 0)
	(177 . 0)
	(93 . 0)
	(304 . "")
	(94 . 0)

    (360 . <Entity name: b6e2b56370>)

	(305 . "CUSTOMDATA")
		(1 . "DATAMAP_BEGIN")
			(90 . 3)
			(300 . "ACEXCEL_CONNECTION_STRING")
				(301 . "DATAMAP_VALUE")
				(93 . 2)
				(90 . 4)
				(1 . "C:\\Users\\mark.j.fernandes\\OneDrive\\Documents\\employment\\21.6.19 AutoCAD project\\TestTableSpreadsheet.xlsx!Sheet1")
				(94 . 0)
				(300 . "")
				(302 . "")
			(304 . "ACVALUE_END")
			(300 . "ACEXCEL_SOURCEDATE")
				(301 . "DATAMAP_VALUE")
				(93 . 2)
				(90 . 8)
				(92 . 16)
				(310 . "E3070600000017000C00200003000000")
				(94 . 0)
				(300 . "")
				(302 . "")
			(304 . "ACVALUE_END")
			(300 . "ACEXCEL_UPDATEOPTIONS")
				(301 . "DATAMAP_VALUE")
				(93 . 2)
				(90 . 1)
				(91 . 68943873)
				(94 . 0)
				(300 . "")
				(302 . "")
			(304 . "ACVALUE_END")
		(309 . "DATAMAP_END")

[norman.yuan]

Yeah, my reply to the OP back almost 5 years ago only correct in the first half (DataLink information being stored in NamedDictionary "ACAD_DATALINK", but wrong on the last half - CAO has nothing to do with DataLink :-(.

I admire your effort of digging out DataLink's data structure in DXF, and trying to use SendCommand to run LISP code to achieve the goal of re-pathing DataLink to different Excel file. This is one of the thing AutoCAD VBA/COM APIs that sucks: COM APIs do not support AutoCAD DataLink.

However, AutoCAD .NET APIs do have a DataLink class to make this task (re-pathing) very easy (another reason to leave VBA behind and move to AutoCAD .NET API programmin!). I can provide sample code doing it with .NET API, if you, or the OP, is interested in. 

[Anonymous]

Hello Norman,

Thanks for looking at this thread again. You've cleared up my suspicion that the CAO type library doesn't help with this problem.

I'm trying to solve this problem for a project I want to bid for. The people who have specified the project, haven't explicitly said that .NET code can't be used, but their spec. inclines one to believe that they probably want a solution that doesn't use custom .NET code. I'm currently leaving using .NET code as an if-all-else-fails solution.

Thanks for the offer re. .NET code. You can post it to this thread (& the new thread I've started here) just in case someone is interested in the code--could help someone. Not sure whether I'll personally need it but could be helpful.

So strange that solving this problem is so difficult with 'pure' VBA or 'pure' lisp. Autodesk seem to have some big holes in their AutoCAD programming documentation and/or AutoCAD ObjectARX COM API.

Thanks,

Mark

[Anonymous]

I’m working on a LISP routine and I was wondering if your wisdom can be called upon?

In my Generic AutoCAD I have some datalink tables from an excel file.

Sometimes we rev the excel file and the name changes, but the referenced cells do not change.

The datalink path is stored in a library file.

You can find the list of the Library files with this Lisp:

(defun c:ListDictionaries ( / ed ed1)

(prompt "\nDictionaries in current drawing: ")

(foreach ed (entget (namedobjdict))

(progn

(cond ((= (car ed) 3)

(prompt (strcat "\n" (cdr ed))))

((= (car ed) 350)

(progn

(foreach ed1 (entget (cdr ed))

(if (= (car ed1) 3)

(prompt (strcat "\n " (cdr ed1)))

)

)

))

)

)

)

(princ)

)

LISTDICTIONARIES

Dictionaries in current drawing:

ACAD_CIP_PREVIOUS_PRODUCT_INFO

ACAD_COLOR

ACAD_DATALINK

Hello

ACAD_DETAILVIEWSTYLE

Imperial24

ACAD_GROUP

ACAD_LAYOUT

Layout1

Layout2

Model

……. Etc…

This shows the name of the Datalink I made (It is called “Hello”)

Furthermore I can look into the database format with the following:

(setq newdictlist (dictsearch (namedobjdict) "acad_datalink"))

((-1 . <Entity name: 2207b6e9e70>) (0 . "DICTIONARY") (5 . "237") (102 . "{ACAD_REACTORS") (330 . <Entity name: 2207b6eb8c0>) (102 . "}") (330 . <Entity name: 2207b6eb8c0>) (100 . "AcDbDictionary") (280 . 1) (281 . 1) (3 . "Hello") (360 . <Entity name: 2207b6e9e80>))

The sub-object (3 . “Hello”) is listed.

Now in my mind I should be able to dig deeper into 3 . “Hello” and possibly pull up the file name it was created from… Then I should be able to tunnel down into the file path…

I know you cannot use ENTGET on a Library file entry so start the tunneling to find and eventually change the file name / path.

Do you know how I can do this?

[Anonymous]

Hello m.hamilton,

Unfortunately, I cannot help you.

I delved into VBA and Lisp programming for the Autodesk software for a
particular short project that I was bidding on.

I don't have a commercial Autodesk licence that appears to be mostly
necessary to software develop for the Autodesk software. When I checked,
the licence seemed very expensive (too expensive for my enterprise).

One thing I would warn you about, is that Autodesk don't appear to provide
documentation for how they store things in lisp objects, which seemed
really weird to me. If you are paying for support from them, it might be
something to ask them about.

Kind regards,

Mark Fernandes