AutoCad LISP Automated Hyperlinker

[reb0265]

Ok I am computer savvy but not a coder by trade, so need some help to see if this is possible and maybe point me in the direction of how to do it. I searched the forum but couldn't find anything I could modify to work.

I am attempting to write a code that searches a .dwg for a text string, then adds a hyperlink to that text string. The text strings/associated hyperlink pairs are in an excel file. So I have been trying to write a LISP program to do the following.

1. Select path to excel file with text/hyperlink pairs

2. Select path to directory with all the .dwg files to crawl through for the text strings found in the excel file
3. Select a color to change the text to (so you know what has been changed)

4. Run so that it crawls through each .dwg in the folder, finds the text string, and if it does find it, add a hyperlink from the excel file and change the color.

5. The extra would be if i had a dialog box pop up... havent been able to get it to do this.

I first wrote this in python but my new job wont let me run python on my computer... and has stuck me with an older version of AutoCAD (2016) so it doesn't have a way to run python out of the box. I tried to write it in LISP and and keep getting errors as I am not familiar with the syntax... even tried chatGPT for some help and it couldn't get it either. I think my issue is with the .dcl code I am attempting to write, even trying to do it without a dialog box I still couldn't get it. I have burned a lot of hours trying to do this so I figured I would ask people who actually know what they are doing!

Anyways if anyone has some advice I would love the help! I have to hyperlink about 3000 documents and I really don't want to do it manually!

[vladimir_michl · Accepted solution]

You can use LookupXLS (and its Hyperlink mode) for this task. You can download this freeware LISP utility from https://www.cadforum.cz/en/lookupxls-populating-dwg-texts-from-excel-spreadsheets-tip13522

Vladimir Michl, www.arkance-systems.com  -  www.cadforum.cz

[reb0265]

Hey Vladimir!

This tool is awesome! Thank you for the help it is greatly appreciated and I feel it is close to working!

2 things. 

When I run the program it is adding symbols into the hyperlink coming from the xls file. I couldn't find anything on the site about the syntax of the input file. Also looked in the source code to see if I could modify it a bit and it said not to modify...

When it adds the hyperlink it messes up the syntax of they hyperlink and causes it to not load correctly. Seems to be adding  " at the beginning and end as well as an extra \ between each of the existing hierarchies. 

so if the hyperlink in the xls was: 

C:\Users\bitter\Desktop\REFORMER.UNI\V2451.dwg

it would show up as

"C:\\Users\\bitter\\Desktop\\REFORMER.UNI\\V2451.dwg"

Secondly is there a way to do this on all objects on every .dwg file in a directory? This seems like i can get it to work per drawing, but still feels like I can automate it a bit further! I feel like it might be possible to create a lisp program to open each drawing in a folder run the LookupXLS file, load the same xls file every time, select all objects on the page, and run it, save, and repeat on the next one.

3 weeks ago I didn't even know any of this was possible, and have loved reading up what people have done on this forum. Yall are amazing! Thanks again!

[vladimir_michl]

Thanks for your feedback. There are several points:

- the "\\" code is just the LISP way to represent a backslash, you can ignore that, Hyperlink click will handle that properly

- there were additional quotes added to the hyperlink which caused problems when linking files, not web URLs - it is fixed now (re-download please)

- LookupXLS now handles just the current DWG but you can use scripting tools like ScriptPro to apply the same procedure to multiple DWG files in a given folder

Vladimir Michl, www.arkance-systems.cz  -  www.cadforum.cz

[reb0265]

The updated hyperlink function works! This is amazing.

My work has my computer locked down so I will have to get approval to add and use ScriptPro. But will let you know once I can run it!

Still working on selecting all text strings on a page without having to drag or click on the page. One issue I see with this method is that any text string selected will now how a hyperlink, if it is not on the CSV the hyperlink displays as nil.

A few Questions:

Is there a way that if it does not find a match in the .csv to not add a hyperlink at all? As stated above currently if you select all and only 3 text strings have a hyperlink designated in the .csv, all text strings get a hyperlink with "nil" and the others get their specified hyperlinks.

Now as I try to automate for multiple files:

Is there a way to define the variables? I see on the website you can define a few using the "setq" funciton for defining LISP variables. I did not see one for selecting a .csv destionation path. Is that possible?
How can I select all for the .dwg page. I have been typing "all" into the command prompt, just trying to automate all the button clicks.

I was working on a LISP function to appload LookupXLS, run the command, and save.

Getting close to getting it to work but as I was testing it I started having an issue.

When running the LOOKUPXLS command, the first input was:

XLS: Filename of the Lookup table (.XLSx, .CSV):

Previously when I started messing with it this morning this would pop up a dialog box to input the CSV file, but now it will not let me, and not sure of the syntax to type in the correct file name. (I tried a lot of variations...) Any advice on the input here? I tried restarting AutoCAD and even my computer and it did not fix the issue.

[reb0265]

Found out the answer to the last part. Accidently changed my FILEDIA from <1> to <0>
Dialog box is opening up again my bad...

[reb0265]

Sorry for the continued back and forth. I think I got the LISP program to run on script pro!

I realize it is not elegant but it works. I tried to do it quite a few other ways but this is the only one I could get to work.  The questions above are mostly still valid. Thanks again.


See Code Below:

(defun c:automatelookupxls ()
(load "C:\\Users\\bitter\\Documents\\Autocad hyperlink code\\LookupXLS.VLX")
(vl-load-com)
(setq thisdrawing (vla-get-activedocument (vlax-get-acad-object)))
(vla-SendCommand thisdrawing (strcat "LOOKUPXLS" "\n"))
(vla-SendCommand thisdrawing (strcat "C:\\Users\\bitter\\Desktop\\REFORMER.UNI\\Copy of Reformer ISO Links.xls" "\n" "\n" "\n" "\n" "\n"))
(vla-SendCommand thisdrawing (strcat "all" "\n" "\n" "\n" "\n"))
(vla-SendCommand thisdrawing (strcat "h" "\n"))
(princ)
)

[Sea-Haven]

Just a suggestion I have all my lisps saved in directories near the top of the drive eg c:\mylisps. You add the directory to your support paths and trusted paths, then you can use (load "LookupXLS.VLX") I do the same for data etc, but in a different directory stops desktop clutter.

To add, Options, Files you will see "Support" and "Trusted".

[vladimir_michl]

Hello,

the "nil" link was fixed. I would not recommend to use SendCommand to drive the parameter input. Use rather a script (.SCR text file) which is the "native" method for ScriptPro.

Vladimir Michl, www.arkance-systems.cz  -  www.cadforum.cz

[reb0265]

Well I have been working on this all day/night and it seemed to work on the very simple drawings, but just tried it on  the more complicated ones dwg's... I am getting a few texts recognized per drawing and it is difficult to find what is hyperlinked. The most important text that is missing is the links to the one below. Nothing in these arrows will link and I have tried everything outside of manually doing it.

The process I went through to build the excel table was to do a data extraction on all drawings being processed to get all the text values and gave each of them hyperlinks (took forever...)

I am still new to AutoCad so am I missing something? Maybe because it is in an attribute it is not getting picked up by the LOOKUPXLS?  

Thank you again everyone for your help.

[vladimir_michl]

I will probably need the DWG file (or a part of it) to investigate. The displayed block seems to be pretty standard but there may be locked layers or some other obstacles in the way.

Vladimir Michl, www.arkance-systems.cz  -  www.cadforum.cz

[reb0265]

I sent you a message! 

[reb0265]

Just to give an update. I did manage to get it working and Vladimir has been extremely helpful, responsive, and open to potential improvements. I personally could not get it to work with my files using only a script. I used script pro to run a script that ran a LISP routine that ran lookupXLS and forced the keystrokes. Overall not an eloquent code but it did work! I did have to add a workaround. Since a lot of my data is house in attributes in various blocks I had to run the lookupXLS program multiple times but looking for different attributes to link. The lookupXLS code allows for you to look for two attributes, for example the first time I ran it, I had it look for DWG and Title 1, the second time, I had it look for Title 2 and %%UPUMP... ect... 
Took a little longer but still worked. I have been running this on 2013 autocad, so hoping it will work on the newer version as I should be getting upgraded to it next week.
Honestly populating the spreadsheet took longer than modifying and running the script. This is an awesome community and I am very thankful for the help!