Hyperlink in AutoCAD table from Excel data link?

[dingleboy]

Hi everyone, is there a way to transmit an hyperlink in an Excel cell into an AutoCAD table? I have successfully (surprisingly) set up data links but I'd like to get Excel hyperlinks to transfer as such, then come out active in a published PDF. 

 

In the Data Link manager, I've tried all three Cell Contents options but none convey the hyperlink.

 

Any ideas?

 

[SLW210]

Are you wanting a LISP?

[dingleboy]

27 minutes ago, SLW210 said:

Are you wanting a LISP?

If that's what it would take, I suppose so, yes - maybe a hint on how to modify tbl.lsp to identify excel hyperlinks if it's even possible? Or a suggestion on how to set up a data link to inherit Excel hyperlinks in a way I haven't discovered yet.

 

 

[Steven P]

51 minutes ago, dingleboy said:

If that's what it would take, I suppose so, yes - maybe a hint on how to modify tbl.lsp to identify excel hyperlinks if it's even possible? Or a suggestion on how to set up a data link to inherit Excel hyperlinks in a way I haven't discovered yet.

 

 

 

Have you got a copy of tbl.lsp, a link maybe that you can share the one you are thinking off?

[SLW210]

AFAIK, the only way to set up Hyperlinks in a Table is with Fields and then IIRC, only link within the same drawing.

 

You might try a Copy and Paste Special as AutoCAD Entities and see if the hyperlink survives.

 

What are you trying to Hyperlink?

[dingleboy]

20 hours ago, Steven P said:

 

Have you got a copy of tbl.lsp, a link maybe that you can share the one you are thinking off?

 

Copy of tbl.lsp attached, with my edit to ignore excel formatting:   (cons 91 1179649);old value=1179649; 1835009=keeps excel formatting

 

If Plan A doesn't work - if Data Links don't want to pass hyperlinks as AutoCAD fields - then what about Plan B? I could just leave the excel link addresses as text in each cell, data link into autocad, then do a find and replace on all text in the drawing to replace the address text with a Field containing a Hyperlink. I did that manually in a test table (pdf screenshot below) and all my pdf printing engines preserved the hyperlink as in "try1" in row 3 successfully. There is light at the end of the tunnel.

 

Is there a way to automate this find and replace task? For example:

 

-search selected table objects (or whole drawing?)  for "http:*",

-if found, replace that address string with a field, hyperlink with 1) the address = the found http string and 2) display the text as "LINK" or something?

 

Thank you all very much for looking at this with me.

 

Tbl.lsp

[BIGAL]

Looking at a hyperlink from excel direct, I think need to find all hyperlinks in the workbook and find details of matching cell. Bit like images in Excel are grouped in collection Shapes.

try it with a Excel open that contains hyperlinks.

(defun hyp ( / )
(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(setq xlSheets (vlax-get-property myxl "Worksheets"))
(setq curSheet (vlax-get-property xlSheets "Item" 1)); 1 is 1st sheet need names of sheets somehow.
; (vlax-get cursheet 'Name) ; returns name
(setq hyp (VLAX-GET CURSHEET 'HYPERLINKS))
(setq hyp1 (vlax-get-property hyp "Item" 1))
(setq linkname (vlax-get hyp1 'Name))
(setq desc (vlax-get hyp1 'texToDisplay))
(alert (strcat linkname "\n\n" desc))
(princ)
)
(hyp)

 

Using a getcell can get the desc from a cell so could compare the two descriptions for a match.

 

Not sure if this is helpful. 

 

The real problem is if you change in Excel the cell details there is no update. The only other way is by reading a Ole2frame internals in some way. You would have the hyperlinks as separate items in the dwg somewhere. I have a suspicion that a ole2frame object really holds no info about what is inside rather pulls all info from the excel on open. 

 

 

 

 

 

 

[dingleboy]

15 hours ago, BIGAL said:

Looking at a hyperlink from excel direct, I think need to find all hyperlinks in the workbook and find details of matching cell. Bit like images in Excel are grouped in collection Shapes.

try it with a Excel open that contains hyperlinks.

(defun hyp ( / )
(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(setq xlSheets (vlax-get-property myxl "Worksheets"))
(setq curSheet (vlax-get-property xlSheets "Item" 1)); 1 is 1st sheet need names of sheets somehow.
; (vlax-get cursheet 'Name) ; returns name
(setq hyp (VLAX-GET CURSHEET 'HYPERLINKS))
(setq hyp1 (vlax-get-property hyp "Item" 1))
(setq linkname (vlax-get hyp1 'Name))
(setq desc (vlax-get hyp1 'texToDisplay))
(alert (strcat linkname "\n\n" desc))
(princ)
)
(hyp)

 

Using a getcell can get the desc from a cell so could compare the two descriptions for a match.

 

Not sure if this is helpful. 

 

The real problem is if you change in Excel the cell details there is no update. The only other way is by reading a Ole2frame internals in some way. You would have the hyperlinks as separate items in the dwg somewhere. I have a suspicion that a ole2frame object really holds no info about what is inside rather pulls all info from the excel on open. 

 

 

 

 

 

 

 

 

BIGAL, thank you for taking the time. I edited a line of the code above as follows:

 

(setq desc (vlax-get hyp1 'textToDisplay)

 

to avoid the error: "; error: ActiveX Server returned the error: unknown name: "TEXTODISPLAY""

 

This is the result with Excel set up with two hyperlinks:

 

 

 

 

Given your input, I think I'm going to shoot for Plan B - convert hyperlinks within AutoCAD table text objects into fields with hyperlinks.

 

[BIGAL]

Yes fixed typo missing "t" thanks. 

 

The code at moment only gets hyp 1 so hyp 2 would get Link2, that is why I hinted need to compare the hyperlink to description to find match.

[dingleboy]

Hey folks, I'm posting to request some guidance on Plan B as described above. Is there a way to automate the find and replace task? For example:

 

-search selected table objects (or whole drawing?)  for "http:*",

-if found, replace that address string with a field, hyperlink with 1) the address = the found http string and 2) display the text as "LINK" or something?

 

Thank you for looking.

[Steven P]

This is the best one I found online, the discussion goes onto modify it for tables, runs from the command line - and so with LISP without a dialogue box. It w

 

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/find-and-replace-text/td-p/5649883

 

-EDIT-

Cutting and pasting from this link to give this text search via the command line - change 'old_string' and 'new_string' for the hyperlinks to see it working.

 

I'd set old_text to val to change the whole text string if val contains "http:" -I've never needed to LISP with Hyperlinks, would need to read up on it but I made a comment below where to put in the hyperlink.

 

not sure if this will work?

 

(defun c:tabletext ( / js )

  (if (setq js (ssget "_X" '((0 . "ACAD_TABLE")))) ; If table exists. Amend filters to suit.
    (progn
      (repeat (setq n (sslength js))
        (setq ename (vlax-ename->vla-object (ssname js (setq n (1- n)))))
        (cond
          ((vlax-property-available-p ename 'Rows) ; table has rows
            (tabl_subst ename)
          )
        )
      ) ; end repeat
    ) ; end progn
  ) ; end if table exits
  (princ) ; exit quietly
); end defun


(defun tabl_subst (obj / eRows eColumns R C val nbs tmp_nbs old_string new_string)

;;Edit these to hyperlinks as required
(setq old_string "Old_String")
(setq new_string "New_String")

  (setq
    eRows (vla-get-rows obj)
    eColumns (vla-get-columns obj)
    R 0
  )

  (repeat eRows
    (setq C 0)
    (repeat eColumns
      (setq val (vla-GetText obj R C)
            nbs 0
            tmp_nbs nil
      ) ; end setq
      (while nbs
        (if (setq nbs (vl-string-search old_string val (setq tmp_nbs nbs)))
          (setq
            val (vl-string-subst new_string old_string val tmp_nbs)
            nbs (1+ nbs)
          )
        )
      )
      (vla-SetText obj R C val) ; this line here to add in the hyperlink
      (setq C (+ C 1))
    )
    (setq R (+ R 1))
  )
)

 

Edited June 28, 2023 by Steven P

[dingleboy]

You know what, this is turning into way too much brain damage. If tables are DATALINKed they're locked in autocad so modifying cell contents is that much harder - or impossible. 

 

Edited to say: unless I'm wrong. Is it possible to modify the contents of a DATALINK table in autocad at all? Say, by suspending the data link in order to modify the "http:*" within a cell into a field that contains the http:* address as I originally posted?

Edited June 28, 2023 by dingleboy

[SLW210]

You can unlock the cells. 

 

Select one or more table cells to edit> Cell Format panel, click Cell Locking Unlocked.

 

OR...

 

lisp to unlock table cells (the whole table at once) - AutoLISP, Visual LISP & DCL - AutoCAD Forums (cadtutor.net)