How do I re-path data links using VBA, without compiling any special .NET code?

[Anonymous]

Hello all,

I'm really struggling with this. I know I can do this with .NET code (via the connectionString property), and I believe I can probably even compile .NET code on the fly from within VBA for this however, my specific project requirements incline one to believe that the user would like to avoid .NET code (& also C/C++ code).

The closest I've come to solving this issue can be partially seen by examining the following code that I have been progressively drawing up:

    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

So 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 lisp list that is for a data link entity (as an example) gives you an idea of the unusual structure (specifically notice how the path appears three times):

(
  (-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")

I can't get my head completely around this structure, and it appears it isn't fully documented by Autodesk. Does anyone know any different?

I've had a look at the CAO library (I believe that the suggestion in the post here, advises that this library [Connectivity Automation Object Library 1.0 type library] be used for my issue) 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? I'm wondering whether the author of the just mentioned post meant a different library. Perhaps they meant the AcObjClassImp 1.0 type library; unfortunately, this other library doesn't appear to be documented by Autodesk at all.

I've looked at possibly using the NETLOAD command with the .NET API ObjectArx library/libraries but it doesn't appear that you can access .NET functionality in a straight-forward way by using this command; instead, it seems that when you make use of the NETLOAD command for this problem, that you should compile .NET code for what I'm trying to do (which is something I want to avoid if I can).

Am really struggling with this so would very much appreciate some help on this.

Thanks,

Mark

[norman.yuan · Accepted solution]

I have responded to you in the other thread, regarding my old reply to the OP only partially correct. In spite you are looking for solution in VBA, I did mention AutoCAD .NET APIs do provide direct support to DataLink object, which may re-pathing very simple. As you suggested, I post the .NET code here for possible future viewers.readers of this forum who may face the same issue.

using System.Collections.Generic;
using System.Linq;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.Runtime;
using CadApp = Autodesk.AutoCAD.ApplicationServices.Application;

[assembly: CommandClass(typeof(UpdateExcelDataLink.MyCommands))]

namespace UpdateExcelDataLink
{
    public class MyCommands
    {
        [CommandMethod("ListDataLinks")]
        public static void GetDataLinks()
        {
            var dwg = CadApp.DocumentManager.MdiActiveDocument;
            var ed = dwg.Editor;

            try
            {
                var links = GetDataLinksInDb(dwg.Database);
                if (links.Count()>0)
                {
                    ed.WriteMessage("\nExcel Data Link(s) found in current drawing:");
                    foreach (var link in links)
                    {
                        ed.WriteMessage($"\n\t{link}");
                    }
                    ed.WriteMessage("\n");
                }
                else
                {
                    ed.WriteMessage("\nNo Excel Data Link found in current drawing.\n");
                }
            }
            catch (System.Exception ex)
            {
                ed.WriteMessage("\nError:\n{0}.", ex.Message);
            }
            finally
            {
                Autodesk.AutoCAD.Internal.Utils.PostCommandPrompt();
            }
        }

        [CommandMethod("UpdateDataLink")]
        public static void RepathDataLink()
        {
            var dwg = CadApp.DocumentManager.MdiActiveDocument;
            var ed = dwg.Editor;

            string linkName = "Test_Excel_Link";
            string newSource = @"C:\Users\norman.yuan\Documents\Visual Studio 2017\Projects\AutoCAD2018\MiscTest\UpdateExcelDataLink\CadDataLink_New.xlsx";

            using (var tran = dwg.TransactionManager.StartTransaction())
            {
                var link = GetDataLink(linkName, dwg.Database, tran);
                if (link!=null)
                {
                    var source = link.ConnectionString;
                    CadApp.ShowAlertDialog($"Data Link: \"{linkName}\"" +
                        $"\nCurrent Link Source: \"{source}\"");

                    var suffix = "";
                    var pos = source.IndexOf("!");
                    if (pos>0)
                    {
                        suffix = source.Substring(pos + 1);
                    }

                    if (!string.IsNullOrEmpty(suffix))
                    {
                        newSource = newSource + "!" + suffix;
                    }

                    if (!link.IsWriteEnabled) link.UpgradeOpen();
                    link.ConnectionString = newSource;
                    link.Update(UpdateDirection.SourceToData, UpdateOption.AllowSourceUpdate);
                }
                
                tran.Commit();
            }
            
        }

        private static IEnumerable<string> GetDataLinksInDb(Database db)
        {
            var links = new List<string>();

            using (var tran = db.TransactionManager.StartTransaction())
            {
                var dic = (DBDictionary)tran.GetObject(db.DataLinkDictionaryId, OpenMode.ForRead);
                foreach (DBDictionaryEntry entry in dic)
                {
                    links.Add(entry.Key);
                }

                tran.Commit();
            }

            return links;
        }

        private static DataLink GetDataLink(string linkName, Database db, Transaction tran)
        {
            DataLink link = null;
            var dic = (DBDictionary)tran.GetObject(db.DataLinkDictionaryId, OpenMode.ForRead);
            foreach (DBDictionaryEntry entry in dic)
            {
                if (entry.Key.ToUpper()==linkName.ToUpper())
                {
                    link = (DataLink)tran.GetObject(entry.Value, OpenMode.ForRead);
                    break;
                }
            }

            return link;
        }
    }
}

[Anonymous]

I'm so close...

When I use appload to load this code, I get a successful message.

However; I immediately get the error message:

Command: APPLOAD
VBA.lsp successfully loaded.
Command: ; error: no function definition: UPDATEEXCELDATALINK

This must be user error.  Can you please help out?

[Anonymous]

Hello m.hamilton,

Sorry, I cannot help. It would be unethical for me to divert resources to
helping you, given my other current commitments.

I'm not actually experienced with Autodesk or Lisp. It sounds like either
you are trying to invoke a function that doesn't actually exist on any
proper level, or that you are missing certain libraries / files that
provide the referred to function.

If you are paying for support, just use them.

Kind regards,

Mark Fernandes

[Anonymous]

I forgot to mention that I am using AutoCAD Electrical 2020

This is most likely ignorance on my behalf about loading, formatting, calling this code as a VBA

I had to download the VBA add-in from Autodesk - I have it successfully installed.

Now I can use the VBALOAD command.

... I just want a command that will update all the excel datalinks in my drawing.  It is one file with multiple datalinks connected to various cell ranges.

[norman.yuan]

When you say "When I use appload to load this code, ...", which code you referred to? the VBA code from the original post of @Anonymous or the code I posted (since your post is a reply to me). Neither of the codes is to be loaded with command "appLoad". So, you need to provide more information on what you are trying to do.

Also, as the OP indicated, he might have not successfully achieved his goal with VBA (mixed with LISP by SendCommand), while my post explained that the task can be done quite easily with AutoCAD .NET API. So, if you want to do the same thing, running AutoCAD ,NET API code would be the best bet.

[cadabyss]

Thank you Norman.  I have been struggling for a long time to find a way to repath Excel datalinks. The C# solution you posted worked perfect!

Thanks again!

Steve Doman

[Thomasverbanck]

Dear Norman,

I need to re-path the data link excel file for over 2000 drawings.

In the excel file there are 'BOM-lists' that are shown on the drawings.

These BOM-list are linked by a named view

I was looking in to your solution.

I installed visual studio 2019 (because we still use autocad 2023) / objectARX SDK 2023 / autocad.NET wizard

I created a new project using the autoCAD 2023 - Csharp plug-in

I deleted everyting in myCommand.cs and changed it by your code

But when i build this solution, load it in autocad using the NETLOAD command, nothing happens

I think i need to change some lines in your code, but i have no clue what it means 

I changed this hoping it would work, but it didnt 🙂

Thanks,

Thomas

[norman.yuan]

Not sure your knowledge level of AutoCAD .NET API. Is this your first .NET API project? There are a few reasons of "Nothing happens after NETLOAD command", but usually it should be "nothing happens", because NETLOAD only loads the .NET app dll into AutoCAD. If the DLL does not implement IExtensionApplication interface, nothing should happen until you execute one of the commands defined in the DLL (i.e. CommandMethod).

In your case, you enter "ListDataLinks", or "UpdateDataLinks" at command line after NETLOAD to load the DLL into AutoCAD. Have you done that? Or are you saying nothing happens after entering one of the 2 commands?

[Thomasverbanck]

Thanks for you answer.

For me its the first time using the AutoCAD . NET API.

I did load the DLL, but i didnt understand how to activate them.

The "ListDataLinks" is recognized, in the command line the datalink name is visible

The "Updatedatalink" is also recognized, but i does nothing.

I tried changing some lines, but i guess i don't know exactly what i need to fill in 

What should the 'string linkName' refer to?

What should the 'string new source' refer to? => i guess the new file location?

Thanks!!!

Thomas

[norman.yuan]

The variable linkName is the data link's name when it is created, which represent a range of data of a sheet (or whole sheet) of a Excel file, in your case, if you want to manually edit/repath it, you run command "DataLink", you would a see a list of nodes underneath the "Excel Links":

In your case, according to your previous post, it should be "P01010" (or others, if the drawing has more than one data links.

And yes, variable newSource is the Excel file's full path.

When you re-path the datalink, it can be different source file with the same or different data range name, or it could be the same source file, only the data range name changes. That is, either data range name or source file path changes the data link needs to be re-pathed.

Since you are pretty new in .NET API, I strongly suggest you learn how to debug the code, so that you can step through the code line by line, not only because the code execution could run into unexpected errors, but also watch what happens as the result of execution of each line of the code, thus learn why using these code.

[Thomasverbanck]

I will check this out tommorow and keep you informed! I'm @ home right now (7:40 PM over here).

I really appriciate your effort to answer my question! 

And if i understand correctly i will need to change the 'linkName'  in the code for every drawing and different datalink connected to a drawing?

[norman.yuan]

Since your task is to re-path, so, no, I do not need to change the data link name. What you need to, is to find the data links in a drawing (probably only one, but there could be multiple, if the drawing has multiple tables linked to different Excel data). You can reuse/modified the code in "ListDataLinks" command for this purpose. And then, for each found Datalink (again, it could be only one in your drawing), you now know its name and you can use this name to update/re-path the data link as the command "UpdateDataLink" command show (i.e. you only need to modify the code minorly for the datalink name and source file path.

However, since you have 2000+ files to update, you probably do not want to open each one and run your custom command, then close and open next... You may have to learn how to update drawing file as side database (i.e. opening drawing file in memory only as database, not visible document), or learn how to run the DLL against AutoCAD core console for match processing. This could be an quite advanced learning topic, though. You may focus on getting the datalink re-pathed in a opened drawing in AutoCAD first.

[Thomasverbanck]

Hi Norman,

This is very clear now!

And it works like a charm!

Thank you so much

I will, as you suggested i will also check your advice concerning the 'update drawing file as side database' 

Have a great day!

Greetings Thomas