Need to change what excel named range an autocad datalink points to

Question

I have a large amount of similar DWG files, each one has a table linked to excel via a data link. those tables change in each file and over time.

What i want to do is go to each DWG file and change the data link so it points to the apropiate named range in excel.

so far i've managed to make it all work except change where the data link points to

note that send comand won't work since there is no way to control datalinks from the comand line

here is a post in the autocad forums that shed some light but i have no clue where to find or how to use the cao library https://forums.autodesk.com/t5/visual-basic-customization/repath-the-excel-reference-through-vba/td-p/5432417

   'change data link on floor to point to coresponding named range
          'here is where the issue starts, open to sugestions
       Dim activeDict As Object        'each dictionary to loop thru
       Dim activeDataLink As Object    'each data link in the file to loop thry
       For Each activeDict In dwgFile.Database.Dictionaries   'loop thru all dictionaries in the file
           On Error Resume Next   'some dictionaries don't have the "name" property
           If activeDict.Name = "ACAD_DATALINK" Then  'check if the active dictionary is the one for Data links
               For Each activeDataLink In activeDict    'loop thru all the data links in the dictionary
               Dim dictObj As AcadDictionary
               Dim datalinkObj As Object
               'another way to acces the data link dictionary
                   Set dictObj = acadFile.Database.Dictionaries.Item("ACAD_DATALINK")  ''test to see if the object is created, it is
               'another way to access the datalink in the dictionary
                   Set datalinkObj = dictObj.Item("SYSTEM SUMMARY NOTES")              ''test to see if the object is created, it is    'HERE HERE HERE HERE
                   'the data link i want to change is called "SYSTEM SUMMARY NOTES" and is present in every file
                   TEST = datalinkObj.Name 'doesn't work
                   TEST = datalinkObj.Value 'doesn't work
                   TEST = datalinkObj.PATH 'doesn't work HERE HERE HERE HERE
               Next activeDataLink
           End If
           On Error GoTo 0
      Next activeDict

the ideal result would change where the data link points to

update: this let's you loop thru all the dictionaries till you reach the datalink dictionary accesing thru CAO library. then loops thru all the datalinks (and i got stuck again )

Sub repathDatalink(dwgFile As AcadDocument, datalinkName As String, xlsFilePath As String, xlsNamedRange As String)
    Dim activeDict As Object        'each dictionary to loop thru
    Dim activeDataLink As Object    'each data link in the file to loop thry
    Dim test
    Dim caoLib As Object
        Set caoLib = AutoCAD.GetInterfaceObject("CAO.DbConnect.20")
            'For Each activeDict In dwgFile.Database.Dictionaries   'loop thru all dictionaries in the file
    For Each activeDict In caoLib.GetLinks.Document.Dictionaries   'loop thru all dictionaries in the file
        On Error Resume Next
        If activeDict.Name = "ACAD_DATALINK" And Not activeDict Is Nothing Then
            For Each activeDataLink In activeDict
                caoLib.GetLinks.Document.Dictionaries.Item(2).Item (0)
                activeDict              'dictionary with all the data links
                activeDataLink          'each data link in the datalink dictionary
            Next activeDataLink
        End If
        On Error GoTo 0
    Next activeDict
    End Sub

as far as i can tell it goes thru the active dwg (which i could handle)

Answer 1

After some research, it looks like you need to interface with the AutoCAD DbConnect object, however, after some experimentation using Visual LISP, whilst I can interface with this object, I cannot seem to acquire any data links created in an active drawing.

When interfacing with this object, you'll need to use a ProgID of either:

"CAO.DbConnect.20"

Or:

"CAO.DbConnect.16"

If using an earlier version of AutoCAD.