Having problem identifying the function for this code any tips to stop error.

[thomas_huntFMPXR]

(defun c:2 ( / file dlg datalinkName datalinkDict datalinkObj insPt range )

  (vl-load-com)

  ;;select Excel file

  (setq file (getfiled "Select Excel File" "" "xlsx" 0))

  (if file

    (progn

      ;; Get Datalinks dictionary

      (setq datalinkDict (vla-GetDataLinks (vla-Get-ActiveDocument (vlax-get-acad-object))))

      ;; Create new datalink

      (setq datalinkObj (vla-Add datalinkDict datalinkName file))

      ;; Optional: prompt for range

      (setq range (getstring T "\nEnter Excel range (e.g., Sheet1!A1:D10) or press Enter for full sheet: "))

      ;; Set the data range if provided

      (if (> (strlen range) 0)

        (vla-SetDataLinkRange datalinkObj range)

      )

      ;; Prompt for insertion point

      (setq insPt (getpoint "\nSpecify insertion point for the table: "))

      ;; Insert table from datalink

      (command "_-TABLE"

               "DATALINK"

               datalinkName

               (if (> (strlen range) 0) "Y" "N")

               (if (> (strlen range) 0) range "")

               insPt

      )

    )

    (prompt "\nNo file selected.")

  )

  (princ)

)

[Moshe-A]

@Sea-Haven hi,

op said we have about 10 datalink, so i thought he wants to choose?!

[Sea-Haven]

Once the excel is open can visually pick the range of the datalink using lisp. Once get a datalink working, could then add that function about selecting range visually. or use All.

[Sea-Haven]

As suggested by @Moshe-A it looks like the issue is in the Table command, The datalink part appears to be working using code above, but needs a couple of enhancements like select range, the only way I got it to work was make a table then select cell A1 etc and right click to do a datalink then it worked. I can see changes in the code some of the (cons 302 have incorrect path and sheet details. Compared to another table that was manually linked. Will still have a go though "Insertdatalink" command needed.

Select cell in table, right button mouse, then "insert Data Link"

[thomas_huntFMPXR]

@Moshe-A  I still getting the code error after I create the new link name.

[Moshe-A]

@thomas_huntFMPXR hi,

Do not expect to get another result if you are doing the same, go back and read message #19 

Moshe

[thomas_huntFMPXR]

@Moshe-A  can you clarify "I notice a problem (on and off) with inserting a table using TABLE command (not the lisp)..." 

[Moshe-A · Accepted solution]

@thomas_huntFMPXR ,

Attached TableFromDataLink.zip inside TableFromDataLink.vlx

extract the zip into your folder,

load AutoCAD, open a drawing with some DATALINKs

APPLOAD TableFromDataLink.vlx inside.

Command: TableFromDataLink <enter>

from the popup dialog pick link to insert

this is message #27 and you also must read message #19

if this doesn't work for you, the next knock on your door is me  🤣

Moshe

[Sea-Haven]

I noticed that if you just make a table and add datalink, it can have problems like Column widths are incorrect. So by reading the Excel 1st and getting that info, how many rows & columns and their widths. The result will be better working on it when have time. Added already some Excel functions. The range allows manual entry A1:D25, or select cells in the Excel, I have seen an Excel with 3 Areas of interest so they could become 3 individual tables.

@Moshe-A using Bricscad can not use the VLX. Hence going down lisp path.

[thomas_huntFMPXR]

@Moshe-A I tried it no errors, but table never showed in CAD. Also Is there a way we can change it to lisp format because I want to change the naming condition.

[Moshe-A]

@thomas_huntFMPXR 

What is naming conditions - give me example.

At bottom line the lisp activate table command an insert the picked datalink, i suggest you to try it manually and tell me if it work?

[thomas_huntFMPXR]

Naming convention instead of type out the full command Make a short abbreviation  like: "TFD_tablefromdatalink". Usually how I set up my lisps for people to use in the office. The issue that arrives is that manually. On the insert table window won't close or complete command after selecting "OK".

[Moshe-A]

@thomas_huntFMPXR hi,

To create shortcuts to a lisp program you can do something as the following example same as creating shortcuts to standard AutoCAD commands do you ask Autodesk for AutoCAD source?!

"On the insert table window won't close or complete command after selecting OK.

i think you are not giving AutoCAD enough time to complete the command (as i said it has a big delay).

try doing it with small xlsx file. if you do not have one, create it as test.

Moshe

; put these code lines in acad.lsp or acaddoc.lsp

(defun c:TFD1 ()
 (if (not c:TablefromDataLink)
  (load "TablefromDatalink.vlx")
 )

 (c:TableFromDataLink)
  
 (princ)
)

(defun c:TFD2 ()
 (if (not c:TablefromDataLink)
  (load "TablefromDatalink.vlx")
 )

 (c:TableFromDataLink)
  
 (princ)
)

[Sea-Haven]

This is a lisp version with two options "All" or "Select range" it only makes the datalink. It has an advantage of either select a range in excel or type the range Eg A1:B25. It uses the Multi radio buttons.lsp for the choice. But could be removed to a simple request, A or R. 

Any advice on how to link a new table via lisp would be appreciated. For now just make a table and  click on top left data cell, then right mouse, choose "Insert datalink" and pick correct data link from list. The (command "_-TABLE" "DATALINK" dlname (getpoint "\npick point for table ")) does not work the table command goes straight to Enter columns.

;; © Juan Villarreal 12.06.2011

; Modifed by Alanh July 2025
; All or range added


(defun c:ExcelData ( /  row col lst vertxldata CellProps CellItem)

; thanks to Fixo for the select option
(defun dorange ( / )
(alert "select Excel file range then select Cad again")
(vl-catch-all-error-p
	   (setq Rng
		  (vl-catch-all-apply
		    (function (lambda ()
				(vlax-variant-value
				  (vlax-invoke-method
				    (vlax-get-property myxl 'Application)
				    'Inputbox
				    "Select a Range: "
				    "Range Selection "
				    nil
				    nil
				    nil
				    nil
				    nil
				    8))))))
)
(setq rangecells (vlax-get rng 'address))
(repeat 4
(setq rangecells (vl-string-subst "" "$" rangecells))
)
(princ)
)

(Defun AddDataLink ( DataLinkName FilePath / ActDoc DatDict DlDate DatDictEname DataLinkList DLEM
                    TempTC SheetName TableContent)
					
(setq dc (rtos (getvar 'cdate) 2 20))

(setq ActDoc (vla-get-activedocument (vlax-get-acad-object)))
(setq DatDict (vla-add (vla-get-dictionaries (vla-get-database ActDoc) ) "ACAD_DATALINK"))
(setq datalink (list '(0 . "DATALINK") '(100 . "AcDbDataLink")))
(setq DLEM (entmakex datalink))
(setq EDL (entget DLEM))
(setq TempTC
(entmakex
 (list 
  (cons 0 "TABLECONTENT")
  (cons 100 "AcDbLinkedData")
  (cons 100 "AcDbLinkedTableData")
  (cons 92 0)
  (cons 100 "AcDbFormattedTableData")
  (cons 300 "TABLEFORMAT")
  (CONS 1 "TABLEFORMAT_BEGIN")
  (CONS 90 4)
  (CONS 170 0)
  (CONS 309 "TABLEFORMAT_END")
  (CONS 90 0)
  (CONS 100 "AcDbTableContent")
 )
)
)

(setq DatDictEname (vlax-vla-object->ename DatDict))
(entmod (subst (cons 330 DatDictEname)(assoc 330 (entget temptc)) (entget temptc)))

(setq DataLinkList
(list
 (assoc -1 edl)
 (cons 0 "DATALINK")
 (cons 102 "{ACAD_REACTORS")
 (cons 330 DatDictEname)
 (cons 102 "}")
 (cons 330 DatDictEname)
 (cons 100 "AcDbDataLink")
 (cons 1 "AcExcel")
 (cons 300 "Excel")
 (cons 301 "")
 (cons 302 rangecells)
 (cons 90 2)
 (cons 92 1)
 (cons 170 (atoi (substr dc 1 4)));Year
 (cons 171 (atoi (substr dc 5 2)));Month
 (cons 172 (atoi (substr dc 7 2)));Day
 (cons 173 (atoi (substr dc 10 2)));Hour
 (cons 174 (atoi (substr dc 12 2)));Minutes
 (cons 175 (atoi (substr dc 14 2)));Seconds
 (cons 176 (atoi (substr dc 16 2)));Milliseconds
 (cons 177 3)
 (cons 93 0)
 (cons 304 "")
 (cons 94 0)
 (cons 360 TempTC)
 (cons 305 "CUSTOMDATA")
 (cons 1 "DATAMAP_BEGIN")
 (cons 90 3)
 (cons 300 "ACEXCEL_UPDATEOPTIONS")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_CONNECTION_STRING")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 4)
 (cons 1 rangecells)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 300 "ACEXCEL_SOURCEDATE")
 (cons 301 "DATAMAP_VALUE")
 (cons 93 2)
 (cons 90 1)
 (cons 92 16)
 (cons 94 0)
 (cons 300 "")
 (cons 302 "")
 (cons 304 "ACVALUE_END")
 (cons 309 "DATAMAP_END")
)
)
(entmod DataLinkList)
(entmod (append (entget datdictename)(list (cons 3 DataLinkName) (cons 360 DLEM))))

(vlax-invoke-method wb-collection 'Close)
(vl-catch-all-apply 'vlax-invoke-method (list myxl 'Quit))

(princ)
)

;; starts here 

(alert "Select the excel file to use ")

(setq FilePath (getfiled "Select File:" (getvar 'dwgprefix) "xls*" 2))
(setq DLName (getstring T "Data Link Name: "))
(setq myxl (vlax-get-or-create-object "excel.application"))
(setq wb-collection (vlax-get myxl "workbooks")
  arq    (vlax-invoke-method wb-collection  "Open" FilePath)
  sheets (vlax-get arq "sheets")
  sheet1 (vlax-get-property sheets "item" 1)
  sheetname (vlax-get-property sheet1 "Name")
)
(if (not AH:Butts)(load "Multi radio buttons.lsp"))
(ah:butts 1 "V"  '("Please select" "All" "Select Range"))
(if (= but 2)
 (progn
  (vla-put-visible myXL :vlax-true)
  (dorange)
  (setq rangecells (strcat filepath "!" sheetname "!" rangecells))
 )
 (setq rangecells (strcat filepath "!" sheetname))
)
(AddDataLink DLName nil);or use filename in place of nil
(princ)
)

(c:exceldata)

[Sea-Haven]

I thought why not ask Copilot. And looked good but the setdatalink does not exist. So any help would be appreciated.

  ;; Bind the DataLink to the Table
  (vla-SetDataLink tableObj linkObj 0) ;; 0 = the whole table
  (vla-UpdateDataLink tableObj linkObj)
  (vla-UpdateTable tableObj)
  (princ "\nLinked table inserted.")
)

Once I have this can read the excel and set the table to match say columnwidths etc.

[CGBenner]

@thomas_huntFMPXR & @Sea-Haven 

Are you both still in need of help here?  Should we split this into two topics r are they both related? (I don't speak LISP... LOL)

[thomas_huntFMPXR]

on my end this was beneficial I have no further questions this was a major topic. 

[CGBenner]

@thomas_huntFMPXR Would you mind marking the post (s) that helped you as Solutions so that others can find them in the future?  Thank you and have a great day!