insert the leader at the coordinate use the text and coordinates in excel

[Bin2009]

I have an excel file which have the X,Y coordinates of a pipeline, and I have some text also contained in the excel file at these coordinates. Is there any way to insert the leader at the coordinate and use the text in the excel file as the leader text?   Any help appreciated

Thanks

Bin

[hak_vz]

Attach sample drawing and data set written as .csv or excel.

[leeminardi · Accepted solution]

Assuming you have an Excel file with columns for the leader text and for the x,y positions of the start and end of the leaders you can create a VLISP statement that you can copy and paste into the command line in AutoCAD.

For example, given this Excel layout:

You can copy-down this expression in cell F4:

=CONCATENATE("(command ",CHAR(34),"_MLEADER",CHAR(34)," ",CHAR(34),B4,",",C4,CHAR(34)," ",CHAR(34),D4,",",E4,CHAR(34)," ",CHAR(34),A4,CHAR(34),")")

Note that ASCII character 34 (quote) is used in the concatenate command to generate the quotes needed in the VLISP statement to distinguish from the quotes that are needed in the Excel concatenate function.

I used x and y offset values in cells A1 and A2 to calculate the leader end points that are in columns D and E.

You can copy and paste the contents of column F (F4:F6) into the AutoCAD command prompt to generate the series of leaders or you can use it to create a .scr file.

[JBerns]

@Bin2009,

Lee offers a clever suggestion to build the AutoLISP commands inside Excel.

I just recently had a need to read Excel files, so I used the GetExcel utility by Terry Miller. I don't think Terry is maintaining his original website, but you can find the code in several places. Here are a few:

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/getexcel-lsp-changing-cell-color-of-... 

https://github.com/cadavre/MTZ-BA-11_lisp/blob/master/sz/LSP/getexcel.lsp 

The utility opens Excel, reads the specified range from the specified sheet. The data is returned as a list of strings. Now you would need to process the list.

What is your programming experience? Solutions can be offered, but this forum always encourages those to learn the coding process. 

As @hak_vz recommends, a sample XLSx file would be helpful. If the XLS format is not required, a CVS file format would be much easier to read. It just depends how you maintain your pipeline points.

I hope this is helpful. We look forward to your sample file(s).

Regards,

Jerry

[Bin2009]

Hello Minardi,

Thank so much for your formula, which solved my problem, works perfect!

I copied and pasted the contents of column F (F4:F6) into the AutoCAD command prompt to generate the series of leaders, because I am not good at create lisp or .scr file.

Have a nice day!

Bin

[Bin2009]

sample file added.

[Bin2009]

Hello JBerns,

Thank so much for your help, I read the GetExcel file, looks it have powerful function,

Unfortunately, I have little programming experience, and not good at create lisp, so I am unable to use the code at this moment, but defiantly I will learn.

So I will use Lee’s excel formula solve my current problem.

I added sample excel file.

Have a nice day!

Bin

[Bin2009]

Hello hak_vz,

Thank so much for your interesting, I added sample excel file..

Have a nice day!

Bin

[Inspection2.resolution]

I've tried copying your example but I get the following error from autocad.

Command: (command LISP command is not available.
Command: "_MLEADER" Unknown command ""_MLEADER"". Press F1 for help.
Command: "3.1,4" Unknown command "1,4"". Press F1 for help.
Command: "4.6,4.5" Unknown command "6,4.5"". Press F1 for help.
Command: "ID3")
Unknown command ""ID3")". Press F1 for help.

I'm not that good on CAD so appreciate any help. 

[leeminardi]

@Inspection2.resolution Excel is used here to create a bunch of AutoCAD and Autolisp statements using Excel's concatenate command to combine the values from various cells.  The statements AutoCAD will use are in column F.  To use these statements in AutoCAD you have two options.

Method #1. In Excel, select cells F4 down to the last row that is not empty. Use Ctrl-C to copy the contents of these cells to the Windows clipboard then go to AutoCAD and paste (Ctrl-V) the command into where you would normally type a command.  Sometimes this method has problems.  Not sure why.

Method #2 In Excel, select cells F4 down to the last row that is not empty. Use Ctrl-C to copy the contents of these cells to the Windows clipboard.  Open Notepad and paste the commands in the blank file.  Save the file as a txt file.  At this point you will have a file that will look something like MyStuff.txt.  To use this file as a script the file extension must be .scr.  Rename the file MyStuff.txt (or whatever you named it) to MyStuff.scr.  Now, in AutoCAD give the script  command and then select the file you just named.  Note, Notepad will not let you give a text file the extension .scr so that is why you have to take the default, .txt, and rename it.  Consider downloading Notepad++.  It is a better text editor and will let you save a text file with the extension .scr.

[Amriya_Exe]

I tried but value not coming up and Text falling so far.

[Sea-Haven]

Post error messages we have no idea otherwise.