Cad to Excel

[ReMark]

Reading this might help you understand the process a little bit better. It discusses how to use a script, a batch file and a lisp routine.

Changing Hundreds of AutoCAD Drawings in a Hurry by Dan Abbott, Chris Linder and John Jordon. This was an AutoDesk University 2005 course.

 

http://www.widom-assoc.com/AU-CP12-3L.pdf

[Lee Mac]

Remark,

 

Just an FYI, that article recommends using a Batch file (.bat) to run the Script (which then calls the LISP) on each drawing in a directory.

 

To my knowledge, this process will unnecessarily reopen the AutoCAD application for every drawing and is hence very slow.

 

I would instead recommend using only a Script to open each drawing, run the LISP, save / close the drawing, then open the next drawing since this will all occur within a single instance of the AutoCAD application, and is consequently much quicker.

 

Lee

[Lt Dan's legs]

(defun putexcel ( _file _lst flag / _excelapp _ofile _workbooks _ws _tbs _cells )
 (if 
   (and
     (setq _excelapp (vlax-get-or-create-object "Excel.Application"))
     (setq _workbooks (vlax-get _excelapp "Workbooks"))
     (or
       (and (findfile _file)
         (setq _ofile (vlax-invoke _workbooks "Open" _file))
       )
       (and (setq _ofile (vlax-invoke _workbooks 'Add))
         (vlax-invoke _ofile 'Saveas _file)
       )
     )
   )
   (progn
     (vlax-for w (vlax-get _excelapp "Sheets")
       (setq _tbs (cons w _tbs))
       (cond 
         ( (eq (vla-get-name w) _sheet)
           (setq _ws w)
         )
       )
     )
     (if 
       (and (or _ws (setq _ws (last _tbs)))
         (setq _cells (vlax-get _ws 'cells))
       )
       (foreach x _lst
         (apply
           (function vlax-put-property)
           (append (list _cells 'item) x)
         )
       )
     )
     (vlax-invoke _ofile 'Save)
     (if flag
       (progn
         (vlax-invoke _ofile 'close)
         (vlax-release-object _excelapp)
       )(cons _excelapp _ofile)
     )
   )
 )
)

(defun getexcel ( _file _sheet flag / _workbooks _ws _tbs _cc _l )
 (if 
   (and
     (setq _file (findfile _file))
     (setq _excelapp (vlax-get-or-create-object "Excel.Application"))
     (setq _workbooks (vlax-get _excelapp "Workbooks"))
     (setq _ofile (vlax-invoke-method _workbooks "Open" _file))
   )
   (progn
     (vlax-for w (vlax-get _excelapp "Sheets")
       (setq _tbs (cons w _tbs))
       (cond 
         ( (eq (vla-get-name w) _sheet)
           (setq _ws w)
         )
       )
     )
     (if (or _ws (setq _ws (last _tbs)))
       (progn 
         (setq _cc 
           (vlax-get 
             (vlax-get (vlax-get _ws 'UsedRange) 
               'Columns
             ) 'Count
           )
         )(setq _n 0)
         (
           (lambda ( / _v _tmpl )
             (vlax-for x (vlax-get _ws 'UsedRange)
               (setq _v (vlax-get x 'value))
               (cond
                 ( (not _v)(setq _v ""))
                 ( (numberp _v)(setq _v (rtos _v 2)))
               )(setq _tmpl (cons _v _tmpl))
               (if (<= _cc (setq _n (1+ _n)))
                 (progn
                   (setq _l (cons (reverse _tmpl) _l))
                   (setq _tmpl nil _n 0)
                 )
               )
             )
           )
         )
         (and flag 
           (progn
             (vlax-invoke _ofile 'Close :vlax-false)
             (vlax-release-object _excelapp)
             (setq _ofile nil _excelapp nil)
           )
         )
       )
     )
   )
 ) (reverse _l)
)

 

 

_$ 
(GETEXCEL 
 "C:\\specs.XLS" ;;filename
 nil                   ;;sheet (if nil, uses sheet1)
 t                     ;;t to close and release excel, nil to leave file & excel open
)
;;returns
;;(("testing a1" "testing b1") ("testing a2" "testing b2"))


_$
(putexcel 
 "c:\\specs.xls" ;;filename
 (list               ;; a list of items to put in
   (list              
     4              ;; row
     4              ;; column
     "final test"  ;;value
   )
 ) 
 nil                ;;leave open 
)


;; if nil, returns
(#<VLA-OBJECT _Application 242ceeb4> . #<VLA-OBJECT _Workbook 135cfd3c>)

[ReMark]

Thank you Lee Mac and Lt. Dan. Your advice should prove useful to the OP. Consider my input only as a catalyst. Regards.

[Lt Dan's legs]

I've taken these from other codes I have so somethings may not make sense.. but I'm sure you get the idea.

 

I did this one a while ago for fun

(defun c:test ( / *error* cad ap asheet allp sha p1 p2 p3 p4 pt1 pt2 flag gr pt fgr )
 (vl-load-com)
 (defun *error* ( msg )
   (or (wcmatch (strcase msg) "*BREAK,*CANCEL*,*EXIT*")
        (princ (strcat "\n** Error: " msg " **")))
   (cond
     ( (and sha (list sha))
       (mapcar (function vla-delete) sha)
     ) ( sha (vla-delete sha) )
   )(vlax-release-object ap)
   (princ)
 )
 (defun cad nil
   (setq *acad*
     (cond ( *acad* )
       ( (vlax-get-acad-object) )
     ) 
   )
 )
 (defun allp ( pt )(mapcar (function (lambda ( x )(if (minusp x)(- x) x))) pt))
 (setq ap (vlax-get-or-create-object "excel.application"))(lib "excel")
 (if (not (setq asheet (vlax-get ap 'activesheet)))
   (setq wb (vlax-get ap 'workbooks)
         wb (vlax-invoke wb 'add)
         asheet (vlax-get ap 'activesheet)
   )
 )
 (vla-put-windowstate ap acmax)
 (vla-put-windowstate (cad) acMax)
 (setq w (vla-get-width (cad)))
 (setq h (vla-get-height (cad)))
 (vla-put-windowstate (cad) acNorm)
 (vla-put-width (cad) (* 0.5 w))
 (vla-put-height (cad) h)
 (vla-put-windowtop (cad) 0.)
 (vla-put-windowleft (cad) (- (* 0.5 w) 2))
 (setq w (vla-get-width ap))
 (setq h (vla-get-height ap))
 (vla-put-windowstate ap acnorm)
 (vla-put-top ap 0.)
 (vla-put-left ap 0.)
 (vla-put-width ap (* 0.5 w))
 (vla-put-height ap h)
 (vla-put-visible ap :vlax-true)
 (vla-put-visible (cad) :vlax-true)
 (vla-zoomwindow (cad) (vlax-3d-point (list 0. 0. 0.))(vlax-3d-point (list 500. -450. 0.)))
(while (eq 5 (car (setq gr (grread t 12))))
 (and (not sha) (prompt "\nSpecify first point: "))
 (and sha (mapcar (function vla-delete) sha))(setq sha nil)
 (setq p1 (allp (polar (cadr gr) (* 0.5 pi) 12.)))
 (setq p2 (allp (polar (cadr gr) (* 1.5 pi) 12.)))
 (setq p3 (allp (polar (cadr gr) pi 12.)))
 (setq p4 (allp (polar (cadr gr) 0 12.)))
 (setq sha 
   (list
     (vla-addline (vlax-get asheet 'shapes)(nth 0 p2)(nth 1 p2)(nth 0 p1)(nth 1 p1))
     (vla-addline (vlax-get asheet 'shapes)(nth 0 p3)(nth 1 p3)(nth 0 p4)(nth 1 p4))
   )
 )
 )(setq fgr (cadr gr))
 (setq gr (allp (cadr gr)))
 (setq pt1 (nth 0 gr) pt2 (nth 1 gr))
 (mapcar (function vla-delete) sha)(setq sha nil)
 (setq flag 3)
 (while 
   (eq flag 3)(prompt "\nSpecify next point: ")
   (while (eq 5 (car (setq gr (grread t 12))))
     (and sha (mapcar (function vla-delete) sha))(setq sha nil)(setq pt (allp (cadr gr)))
     (setq sha 
       (list (vla-addline (vlax-get asheet 'shapes) pt1 pt2 (nth 0 pt) (nth 1 pt) )
         (line fgr (cadr gr) "acc" t)
       )
     ) 

   )
 (if (eq (setq flag (car gr)) 3)
   (setq sha nil
         fgr (cadr gr)
         gr (allp (cadr gr))
         pt1 (nth 0 gr) pt2 (nth 1 gr)
   )(and sha (mapcar 'vla-addline sha))
 )
 )(vlax-release-object ap)(princ)
)

[Mobain]

thanks to all you guys for all the help.

I have a lot of studying to do to figure out how this all works. I dont seem to know what I'm doing, by the time i figure it out it would probably be quicker to just run the lisp routine on each drawing manually, at least I get payed by the hour

[odyseus8]

Sorry to bring out an old topic but I have some questions regarding text extraction from autocad to excel. I'm a newbie too and want a lisp to run for multiple dwg files in a folder. I assume that i should use a batch program like scriptpro but couldn't figure out how to apply a lisp in a script. Also if the name of the file is converted too it would be great. Can anyone help me regarding this topic

Thanks in advance