Populate DWGPROPS from excel

[dario_jukica]

Hi.

I have an excel spreadsheet in which I keep a log of all my projects.

A1 is project number

B1 is investor

C1 is investor address

etc.

I've created custom fields in DWGPROPS corresponding to the info from the excel.

Would it be possible to write a lisp that prompts for a project number and then fills in the DWGPROPS with data?

Thank you.

[pendean]

Have you had a chance to explore these identical posts yet

https://www.google.com/search?q=autocad+lisp+to+Populate+DWGPROPS+from+excel 

and

https://forums.autodesk.com/t5/autocad-forum/use-lisp-to-import-excel-to-dwgprops/m-p/10588465#M1060... 

[dario_jukica]

Thank you for your reply.

Tried the lisps suggested but they are too complex for me to adapt them to my scenario.

I've got one Excel document always stored in the same place (D:\GROMATIK\Upisnici\2024\G-PREDMETI 2024.xlsx)

In the Excel file, I've got a first tab named Popis which is the data I need.

Column A - project number

Column B - project type

Column D - title

Column H - investor name

Column I - investor address

Column J - investor ID

Column P - date

Column U - region

Column V - region ID

Column AG - detail number

Column AN - ZOP

Ideally, I'd like a LISP that once prompted asks for the project # (which corresponds to the row # in the Excel table above) and fills the DWGPROPS.

Custom properties names in the DWGPROPS correspond to the descriptions above.

Thank you

[Moshe-A]

@dario_jukica  Hi,

Attached xls2dwgprops.lsp that answer your goal (plus sample xlsx file)

Huge credit to:

1. Terry Miller

2. Gilles Chanteau

who gave us these beautiful functions to work with real excel files from AutoLISP\ActiveX without them this lisp was never born 😀

The command starts by opening the standard file selection letting you to select excel. by default it looks for xlsx type but you can also change it to old xls. next you'll be ask for sheet name (default "sheet1") plus the max range (default "AN2")

your fields from the excel file will be written to the custom tab \ custom properties of DWGPROPS.

please, do not hold the excel file open when running this command.

enjoy

Moshe

(defun c:xls2dwgprops (/ _fcn askString  ; local functions
		         adoc SummaryInfo fname sheet range value)
 ; anonymous function
 (setq _fcn (lambda (c n) (strcat c (itoa n)))) ; format cell name

 (defun askString (msg def)
  (princ (strcat "\n" msg " <" def ">: "))
  (if (eq (setq ask (getstring)) "")
   (setq ask def)
   (setq def ask)
  )
 ); askString 

  
 ; here start c:xls2dwgprops
 (setq adoc (vla-get-activedocument (vlax-get-acad-object)))
 (setq SummaryInfo (vla-get-summaryinfo adoc))
 
 (if (and
       (setq fname (getfiled "Select excel file" "" "xlsx;xls" 8))
       (setq sheet (askString "Sheet name" "sheet1"))
       (setq range (askString "Range" "AN2"))
       (GetExcel fname sheet range)
     )
  (foreach Col '("A" "B" "D" "H" "I" "J" "P" "U" "V" "AG" "AN")
   (if (vl-catch-all-error-p (vl-catch-all-apply 'vla-GetCustomByKey (list SummaryInfo (GetCell (_fcn Col 1)) 'value)))
    (vla-addCustomInfo  SummaryInfo (GetCell (_fcn Col 1)) (GetCell (_fcn Col 2)))  
    (vla-SetCustomByKey SummaryInfo (GetCell (_fcn Col 1)) (GetCell (_fcn Col 2)))
   )
  ); foreach
 ); if

 ; dispose memory
 (foreach o (list SummaryInfo adoc)
  (if o (vlax-release-object o))
 )

 (command "._dwgprops")
 
 (princ)
); c:xls2dwgprops

[Sea-Haven]

I started with getexcel but added more direct functionality like (getcell row column) much easier to work with than "A23" the code by the great FIXO, like Gilles Chanteau and Terry Miller, started around the same time. The other function was getexcel tends to read the entire spreadsheet rather than desired cells, so another 2 functions by Fixo are get range by selecting within Excel very smart and get the full range of the current sheet, which then uses the Gille code to convert to a row column answer. There is also a get sheet names so can pick. Add images plus a few more special defuns.

Your welcome to use any of the functions.

[sbalasubramanianJJDFU]

Hi , Can you help to modify the above xls2dwgprops.lsp to let the user define the sheet name and range of cells to pull data from xlsm file? Many thanks!

[Sea-Haven]

For the sheet name part. Replace the myxl with your application variable name.

; set current worksheet
(defun setsheet ( sheetname / )
  (setq xlSheets (vlax-get-property myxl "Worksheets"))
  (setq x 0)
  (repeat (vlax-get-property xlSheets 'count)
   (setq curSheet (vlax-get-property xlSheets "Item" (setq x (1+ x))))
   (setq sname (vlax-get-property cursheet 'Name))
   (if (= sname sheetname)
    (vlax-invoke-method curSheet "Activate")
   )
  )
)

"range of cells to pull data from xls" you have to be more specific is it like A23:F23 or all over the place A23,B23, D23, E23 and so on. If the latter can pick the excel cells and update CAD.

Alos post a dwg and Excel as samples.