Excel table to Custom Dwgprops in AutoCAD LT

[ebandian]

Hi its me again, I've been searching different forum for this lisp but I don't have any luck.

The lisp I found only works on Autocad full version, and not works on AutoCAD LT.

I just want to import data from the excel to DWGPROPS (custom properties) with just using a lisp to make things easier and time efficient.

[ec-cad]

I don't know if this Lisp will help. It is programmed to set the Custom Properties per your sample drawing.

I would like you to load it and run [just type GO] at the Command Prompt after loading. That will test your

AcadLT to see if it's even possible to do. IF it works, then we can go on to 'reading' the Excel and

populating those title blocks as well. Also, I don't see a connection between the MText's in the title block

and the 'data' in the Custom Properties.. but I'll not concern myself at this time..

Here's the Code, and attached Lisp program. Let me know what happens.

;; TestCustom.lsp
;; Intended to 'test' the AcadLT to see if those properties can be adjusted via Lisp.


(defun GetCustomInfo ( / Info Num Index)
 (and
  (vl-load-com)
  (or *acad* (setq *acad* (vlax-get-acad-object)))
  (or *doc* (setq *doc* (vlax-get *acad* 'ActiveDocument)))
  (vlax-property-available-p *doc* 'SummaryInfo)
  (setq Info (vlax-get *doc* 'SummaryInfo))
  (setq Num (vla-NumCustomInfo Info))
  (setq Index 0)
   (repeat Num
    (vla-getCustomByIndex Info Index 'ID 'Value)
    (setq Custom (cons (cons ID Value) Custom))
    (setq Index (1+ Index))
   );repeat
 );and
  (if Custom (reverse Custom))
); defun


(defun C:GO (); main Function to call

;; Note: the following would be drawn from the Excel file:
(setq xProjectID "Project Number 100")
(setq xProjectName "Very Big Project indead")
(setq xAddress "123 Main Street")
(setq xCity "Chicago")
(setq xState "Illinois")
(setq xZip "22202")
(setq xDesigner "Designers Name")
(setq xDateDrawn "2024-08-20")

;; Check what's in the Custom Properties presently..
(getCustomInfo); sets 'info object and returns contents
(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties BEFORE changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if

;; Now, let's put new info into the Custom Properties Object 'info
(if info
  (progn
   (princ "\nApplying new data to Custom Properties:")
   (vla-SetCustomByKey info "Project ID" xProjectID)
   (vla-SetCustomByKey info "Project Name" xProjectName)
   (vla-SetCustomByKey info "Address" xAddress)
   (vla-SetCustomByKey info "City" xCity)
   (vla-SetCustomByKey info "State" xState)
   (vla-SetCustomByKey info "Zip Code" xZip)
   (vla-SetCustomByKey info "Designer" xDesigner)
   (vla-SetCustomByKey info "Date Drawn" xDateDrawn)
  ); progn
 ); if

;; Now, let's check those Custom Properties again..
(getCustomInfo); sets 'info object and returns contents
(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties AFTER changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if
(princ)
); function C:GO
(princ)
(princ "\nType GO to check Custom Properties:")

ECCAD

[Sea-Haven]

Your welcome to use this. Make sure correct Excel is open before running. Note some cells are seen as numbers others as strings depends on a real excel with cell values provided. Can check Type so convert to string if required.

;;	Thanks to fixo			;;
(defun getcell2 (row column / )
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
(setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

(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 vals '())
(setq row 2 col 2)
(repeat 8
(setq vals  (cons (getcell2 row col) vals))
(setq row (1+ row))
)
(setq vals (reverse vals))

(setq xProjectID (nth 0 vals))
(setq xProjectName (nth 1 vals))
(setq xAddress (nth 2 vals))
(setq xCity (nth 3 vals))
(setq xState (nth 4 vals))
(setq xZip (nth 5 vals))
(setq xDesigner (nth 6 vals))
(setq xDateDrawn (nth 7 vals))

[ec-cad]

Thanks Sea-Haven,

Might be handy (IF OP ever gets back to us on this posting).

If the OP wants to fill-in the MText's within the title block image they sent, I 

think that's not a good method. I would suggest just plain Attributes, so they can be

easily populated with the data. And for the data - make it a .csv file, with Header Line,

and layout Horizontal by Row, rather than Vertical by Column.

So, it would look like:

'drawingname, projectid, projectname, ... etc. 

That way we could read the .csv and fill-in the correct title block for 'this' drawing by

matching the drawingname.

I've done that before with great success.

I don't see what filling in the Custom Properties has to do with the title block with the Mtexts ?

Maybe OP just wants to do the Custom data, and sent the drawing as a sample of what's there.

ECCAD

[Sea-Haven]

@ec-cad Probably using fields that are linked to the custom properties, a pretty common method. You are right the excel would be better as dwgname in column A then all values in B C D etc. You can read all column values in Excel, find the dwgname then correct values. Again no need for CSV's. 

Part 2 have select a range in Excel so could limit the search rather than entire spreadsheet. Or even pick dwgname in Excel.

[ebandian]

Thanks for your reply guys, I will check on this later and will update if it is working on my end.

I really appreciate your responds! 

[ebandian]

This don't work on my end.
"error: bad argument type: VLA-OBJECT nil" show after I load the lisp file,

I am using Autocad LT, maybe thats the issue

[ec-cad]

ebandian,

Which Lisp did you load that gave you the error ?

IF it was the one posted by Sea-Haven, it probably would give you that error.

That code was to be 'included' as a module in my lisp to test whether the Custom Properties

could be read / written with your version of LT.

IF it was the Lisp I posted 'TestCustom.lsp', you would 'load' it or drag/drop it into an open drawing,

and then at the Command Prompt, type GO to run it.

Which Lisp did you try ?

ECCAD

[Sea-Haven]

Acad LT2024 25 does not support Get application. So you can not read an Excel file, my bad sorry about that. So @ec-cad has provided you a solution. I use Bricscad which does support Excel and Libre calc. Why Autodesk left out functions when adding lisp who knows.

[ebandian]

I tried both lisp.

for the lisp you sent. 

here is what i got. 

the values on dwg props does not changes

[ec-cad]

Hmm, curious.

Looks like you loaded and ran the GO function correctly.

It (did) get the Custom Information, since it was able to echo the list

to the screen, in (both) cases, before / after.

I would then suspect that the lines  (vla-SetCustomByKey ....

are not working at all on LT for some reason. I do not have LT to test it on,

so, I guess I'm out of ideas on this one, maybe someone else can modify the code to

acturally change those values, maybe with an 'Index' to that list ?

ECCAD

[Sea-Haven]

Just copy this to command line as a test.

(vla-SetCustomByKey (vlax-get (vlax-get (vlax-get-acad-object) 'ActiveDocument)'SummaryInfo) "Project ID" "xxxxctID")

 You must have the key "Project ID" already existing. If not add then test, the keys can be added as a check for do exist.

If it does not work then it may be the get summaryinfo is not supported in LT.

[vladimir_michl]

You need to call

(vla-AddCustomInfo oSInfo "Project ID" "ABCD")

or

(vlax-invoke-method oSInfo "AddCustomInfo" "Project ID" "ABCD")

if that custom property does not yet exist. All these calls do work also in AutoCAD LT (2024+).

See also https://www.cadforum.cz/en/bulk-editing-of-drawing-properties-dwgprops-from-excel-tip13757

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

[ec-cad]

ebandian,

Seems that the last program I posted doesn't want to run in your LT.

So, I have a new test program that uses the 'index' to the Custom Properties Object.

Places the same data I had in first go around, but uses a different method.

Here's the Code. Please try it out, to see if it sets those Custom Properties..

;; TestCustom.lsp
;; Intended to 'test' the AcadLT to see if those properties can be adjusted via Lisp.
;; First test, not working in LT 24/25
;; 2nd test 'GO2 function, uses set index via (vla-SetCustomByIndex info KeyNum KeyName Value)
;;   where KeyNum is the 'index' number to the Custom Properties object
;; New TestCustom.lsp, Rev 2, 08-21-2024
;;  By: ECCAD

(defun GetCustomInfo ( / Num Index)
 (and
  (vl-load-com)
  (or *acad* (setq *acad* (vlax-get-acad-object)))
  (or *doc* (setq *doc* (vlax-get *acad* 'ActiveDocument)))
  (vlax-property-available-p *doc* 'SummaryInfo)
  (setq Info (vlax-get *doc* 'SummaryInfo))
  (setq Num (vla-NumCustomInfo Info))
  (setq Index 0)
   (repeat Num
    (vla-getCustomByIndex Info Index 'ID 'Value)
    (setq Custom (cons (cons ID Value) Custom))
    (setq Index (1+ Index))
   );repeat
 );and
  (if Custom (reverse Custom))
); defun



(defun C:GO2 (); new test

(getCustomInfo); sets 'info object and returns contents
(setq nc (vla-numcustominfo info)); number of Custom ID's

 (princ (strcat "\nNumber of items in info: " (itoa nc)))


 (setq keys (list) numitems (list))
 (setq num 0)
 (foreach ID Custom
   (setq key (car ID))
   (setq keys (cons key keys)); Key Name List
   (setq keynums (cons num keynums)); Key Index Numbers
   (setq numitems (cons num numitems))
   (setq val (cdr ID))
   (princ (strcat "\nKey= " key "  Val= " val ""))
   (setq num (+ num 1)); index
 ); foreach

(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties BEFORE changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if

;; Note: the following would be drawn from the Excel file:
(setq xProjectID "Project Number 100")
(setq xProjectName "Very Big Project indeed")
(setq xAddress "123 Main Street")
(setq xCity "Chicago")
(setq xState "Illinois")
(setq xZip "22202")
(setq xDesigner "Designers Name")
(setq xDateDrawn "2024-08-21")

;; Try just 'adding' the info if Key Name doesn't exist presently
(if info
  (progn
   (princ "\nApplying new data to Custom Properties:")
   (if (not (member "Project ID" keys))
    (vla-AddCustomInfo info "Project ID" xProjectID)
   ); if
   (if (not (member "Project Name" keys))
    (vla-AddCustomInfo info "Project Name" xProjectName)
   ); if
   (if (not (member "Address" keys))
    (vla-AddCustomInfo info "Address" xAddress)
   ); if
   (if (not (member "City" keys))
    (vla-AddCustomInfo info "City" xCity)
   ); if
   (if (not (member "State" keys))
    (vla-AddCustomInfo info "State" xState)
   ); if
   (if (not (member "Zip Code" keys))
    (vla-AddCustomInfo info "Zip Code" xZip)
   ); if
   (if (not (member "Designer" keys))
    (vla-AddCustomInfo info "Designer" xDesigner)
   ); if
   (if (not (member "Date Drawn" keys))
    (vla-AddCustomInfo info "Date Drawn" xDateDrawn)
   ); if
  ); progn
 ); if
;; Now, if all the Key Names are there, place data into each Key by Index
 (setq C 0)
 (foreach keypair keys
  (setq KeyName (nth C keys))  ; get Keyname
  (setq KeyNum (nth C keynums)); get KeyNumber
  (repeat nc
   (if (= KeyName "Project ID")
    (vla-SetCustomByIndex info KeyNum KeyName xProjectID)
   ); if
   (if (= KeyName "Project Name")
    (vla-SetCustomByIndex info KeyNum KeyName xProjectName)
   ); if
   (if (= KeyName "Address")
    (vla-SetCustomByIndex info KeyNum KeyName xAddress)
   ); if
   (if (= KeyName "City")
    (vla-SetCustomByIndex info KeyNum KeyName xCity)
   ); if
   (if (= KeyName "State")
    (vla-SetCustomByIndex info KeyNum KeyName xState)
   ); if
   (if (= KeyName "Zip Code")
    (vla-SetCustomByIndex info KeyNum KeyName xZip)
   ); if
   (if (= KeyName "Designer")
    (vla-SetCustomByIndex info KeyNum KeyName xDesigner)
   ); if
   (if (= KeyName "Date Drawn")
    (vla-SetCustomByIndex info KeyNum KeyName xDateDrawn)
   ); if
  ); repeat
  (setq C (+ C 1))
 ); foreach

;; Now, let's check those Custom Properties again..
(getCustomInfo); sets 'info object and returns contents
(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties AFTER changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if
(princ)
); defun C:GO2

;; NOTE: The following Function works for other than LT, uses (vla-SetCustomByKey CustomObj "KeyName" "Value to place there)
;;  Seems to NOT work in LT

(defun C:GO (); main Function to call

;; Note: the following would be drawn from the Excel file:
(setq xProjectID "Project Number 100")
(setq xProjectName "Very Big Project indeed")
(setq xAddress "123 Main Street")
(setq xCity "Chicago")
(setq xState "Illinois")
(setq xZip "22202")
(setq xDesigner "Designers Name")
(setq xDateDrawn "2024-08-21")

;; Check what's in the Custom Properties presently..
(getCustomInfo); sets 'info object and returns contents
(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties BEFORE changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if

;; Now, let's put new info into the Custom Properties Object 'info
(if info
  (progn
   (princ "\nApplying new data to Custom Properties:")
   (vla-SetCustomByKey info "Project ID" xProjectID)
   (vla-SetCustomByKey info "Project Name" xProjectName)
   (vla-SetCustomByKey info "Address" xAddress)
   (vla-SetCustomByKey info "City" xCity)
   (vla-SetCustomByKey info "State" xState)
   (vla-SetCustomByKey info "Zip Code" xZip)
   (vla-SetCustomByKey info "Designer" xDesigner)
   (vla-SetCustomByKey info "Date Drawn" xDateDrawn)
  ); progn
 ); if

;; Now, let's check those Custom Properties again..
(getCustomInfo); sets 'info object and returns contents
(princ "\n")
(princ "\nHere's a List of what is in the Custom Properties AFTER changes:")
(princ "\n")
(if Custom
 (progn
  (princ Custom)
  (setq Custom nil); for next check
 ); progn
); if
(princ)
); function C:GO

(princ)
(princ "\nType GO2 to check Custom Properties:")

ECCAD

[mietek_markuszewicz]

Hi EC-CAD

Your script TestCustom.lsp (GO2) works fine, but could you please take it a step further and show how to import from an xls file, or at least from a csv file, into Custom properties? I am using ACAD LT2025.

[ec-cad]

Sure,

Can you provide a sample .csv file to test on ?

Best, if format kinda looks like:

Column A, Column B

PROJECTID,PROJECT IDENTIFICATION

PROJECTNAME,VERY BIG PROJECT

ADDRESS,123 MAIN STREET

CITY,CHICAGO

STATE,ILLINOIS

ZIP,22202

DESIGNER,DESIGNERS NAME

DATE,2025/02/18

That way, I can read a line at a time, and set proper fields. I can use Column A (strcase Col_A) to get the 

column B info placed correctly. If any 'blank', just skip that entry.

ECCAD

[ec-cad]

Here's a sample that will read the .csv (provided it is per previous posting).

New Function  C:GO3

Cheers,

ECCAD

[ec-cad]

Forgot the .csv I used.

ECCAD

[mietek_markuszewicz]

Wow, works perfect on your csv data . My test_custom1.csv file returns error.

[ec-cad]

Your format is OK. GO4 will read that and apply the Custom info.

Last model was looking for certain KeyWords to be in Column1.

This model just makes them 'new' Keys.

If you want, I can remove all the unused code.

If you do not want the Header line, I can adjust for that also.

ECCAD