Results 1 to 6 of 6

Thread: Lisp to read an excel and update attributes in title blocks

  1. #1
    Member
    Join Date
    2009-11
    Posts
    13
    Login to Give a bone
    0

    Default Lisp to read an excel and update attributes in title blocks

    Dear folks, I'm not expert in lisp but I can read some functions, I have a big problem in my job and have to revise more than 300 drawings until saturday. Using a script maybe I can solve my problem.
    I need a lisp to update title block from drawings reading information from a excel file (ldf.xls) and I need to insert a new block (c_sdk) with old information, I will try to explain using an example:
    I have the file 1505PA1-60-005-4-B and need to insert the block c_sdk with the scale of drawing (1:5 =5 in example) in the right side, then pick last information in revisons table (examle: B - E - DRAWING REALESED FOR MANUFACTURING WR WG BA WIM 15/11/12) and put these informations in block c_sdk inserted in the drawing (more explained in process.dwg). After it, the revison number goes to 1 instead B, from ldf.xls I have to drawing 1505PA1-60-005-4 new revision=1, so I have the number of drawing and the column with new revision number. Finally I have to save the file with the n° of contracted and with n° vale + the new revision number. For 1505PA1-60-005-4 the file will have 2 files saved as:

    1505PA1-60-005-04-1
    DF-1000KS-M-42122-M-00478-1

    Thanks a lot for futher support,

    Attachment 92162

    C_SDK.dwgLDF.xlsPROCESS.dwgPROCESS2.dwg1505PA1-60-005-4-B.dwg

  2. #2
    I could stop if I wanted to
    Join Date
    2012-11
    Location
    Brisbane, Australia
    Posts
    239
    Login to Give a bone
    0

    Default Re: Lisp to read an excel and update attributes in title blocks

    Hey mate.

    I feel your pain. I am currently developing a program to update titleblocks from an excel file but it is still incomplete.

    Lee Mac has written a program that might solve your problem for the time being.
    Check it out.
    http://www.lee-mac.com/updatetitleblock.html

    Cheers

  3. #3
    Member
    Join Date
    2009-11
    Posts
    13
    Login to Give a bone
    0

    Default Re: Lisp to read an excel and update attributes in title blocks

    Thanks for your support Cheers, I try this, but I need a way to batch export csv files from files,
    BR,

  4. #4
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: Lisp to read an excel and update attributes in title blocks

    Copy few drawing to separate folder and try this way in background
    This is not finished, I need to go asleep, sorry
    Code:
    (defun C:demo(/ *error *acapp  acsp adoc adocs attfound attitems attpairs atts attvalues blkdoc blkpath block  data dir
    	      doc  elapstime endtime files found nblock newrev num1 num2 oldrev pfs ppt seldata seltype sfar
    	      starttime tags title title_atts title_block t_atts values  xlapp xlrange)
      (vl-load-com)
      (defun *error* (s)
        ;|(vl-bt)|;
        (princ s))
      ;; Excel part follows
      ;; File "LDF.xls" must be open then select 926 rows in columns A-C, save file and minimize them, but do not close !
     (setq xlapp (vlax-get-or-create-object "Excel.Application"))
    (setq xlrange (vlax-get-property
    		(vlax-get-property xlapp 'selection)
    		'rows)
          )
    
    (setq data (vlax-safearray->list
    	       (vlax-variant-value
    		 (vlax-get-property xlrange 'value2)))
          )
    
    (vla-close (vlax-get-property xlapp 'activeworkbook):vlax-false)
    (gc)
    (vla-quit xlapp)
    (vlax-release-object xlapp)
    (gc)
    ;; parse excel data to readable list
    (setq values (mapcar '(lambda(x)(mapcar' vlax-variant-value x)) data))
    (setq attItems (mapcar '(lambda(x)(list (car x)(cadr x)(if (caddr x)(itoa (fix (caddr x)))"-")(itoa (fix (last x)))))(cdr values)))
    
      ;; AutoCAD parts is goes here
      
      ;; title block name 
      (setq title "A$C7E7E5F7C")
      ;; attributes to change of the title block 
    ;;;  (setq title_atts (list
    ;;;  (cons  "CONTRACTOR'S_NUMBER" "")
    ;;;  (cons  "NUMBER_VALE" "")
    ;;;  (cons  "REVISION" "")
    ;;;  (cons  "ESCALE" (strcat "1:" (itoa (fix(getvar "dimscale")))))))
      ;; insertion point of "c_sdk" block 
      (setq ppt (vlax-3d-point (list 2970.0000 0.0000 0.0000)))
    ;; attribute values of "c_sdk" block 
        (setq attvalues (list "B" "DRAWING REALESED FOR MANUFACTURING" "E" "15/11/12" "WG" "BA" "WM" "VSP"))
      ;; attribute tags of "c_sdk" block
      (setq	tags
    	 (list "1REVISAO"
    	       "1DESCR"
    	       "1TE"
    	       "1DATA"
    	       "1DES"
    	       "1VERIF"
    	       "1APROV"
    	       "1LIBER"
    	 ) ;_ end of list
      )
    
    
      ;;selection types 
      (setq seltype(vlax-safearray-fill
          (vlax-make-safearray vlax-vbinteger
            (cons 0 1)
          )
         (list  0 2)
        )
    	)
      ;;selection values 
      (setq seldata(vlax-safearray-fill
          (vlax-make-safearray vlax-vbvariant
            (cons 0 1)
          )
         (list  "insert" title)
        )
    	)
      
    
      (setq acApp(vlax-get-acad-object))
      (setq adoc (vla-get-activedocument acApp))
    (and (setq dir "C:/Users/BATCH/");; change directory path to your search path
    	   (setq files (vl-directory-files dir "*.dwg"  1))
          )
     (setq attItems (vl-remove-if-not '(lambda (x)(member (cadr x) (mapcar 'vl-filename-base files ) ))attItems)) 
    (setq files (mapcar '(lambda (x)(strcat dir x))files))
    
    (setq adocs (vla-get-documents acApp))
    (setvar "sdi" 0)
    ;; change  location of "c_sdk" block here:
    (setq blkPath "C:/Users/ACCESS/PROCESS.dwg")
    (setq startTime (getvar "cdate"))
    
    
    (foreach dwgPath files
     (if 
    ;; get the data record for this document
    (setq found (car (vl-remove-if-not '(lambda (x)(eq (cadr x) (vl-filename-base dwgPath))) attItems)))
    (progn
      (setq num1 (car found)
    	num2 (cadr found)
    	oldrev (caddr found)
    	newrev (last found))
     
      ;(setq acsp (vla-get-modelspace dwg))
      
    (setq doc (vla-open adocs dwgPath :vlax-false ""))
    ;; change attribute list for title block here
       (setq title_atts (list
      (cons  "CONTRACTOR'S_NUMBER" num1)
      (cons  "NUMBER_VALE" num2)
      (cons  "REVISION" newrev)
      (cons  "ESCALE" (strcat "1:" (itoa (fix(vlax-variant-value (vla-getvariable adoc "dimscale"))))))))
        (if (vl-catch-all-error-p (vl-catch-all-apply 'vla-item (list (vla-get-blocks doc) "c_sdk")))
        (progn
        
        (setq blkDoc (vla-open adocs blkPath :vlax-false))
       (setq block (vl-catch-all-apply 'vla-item (list (vla-get-blocks blkDoc) "c_sdk")))
    (setq sfar
        (vlax-make-variant
        (vlax-safearray-fill
          (vlax-make-safearray vlax-vbobject
            (cons 0 0)
          )
         (list  block)
        )    
      )
          )
    (not (vl-catch-all-error-p
    	(vl-catch-all-apply
    	  '(lambda ()
    		      (vla-copyobjects
    			blkDoc ;;source drawing that is contains the block definition
    			sfar
    		(vla-get-blocks doc)
    		      )
    		    )
    	  ))
    	)
        
        (vla-close blkDoc)(vlax-release-object blkDoc)))
    (if (not(vl-catch-all-error-p (vl-catch-all-apply 'vla-item (list (vla-get-blocks doc) "c_sdk"))))
        (progn
                 (vla-setvariable doc "ctab" "Model")
          (setq acsp (vla-get-modelspace doc))
    (setq pfs (vla-get-pickfirstselectionset doc))(vl-catch-all-apply 'vla-clear (list pfs))
          (vla-zoomextents (vla-get-application doc))
          (vla-select pfs acselectionsetall nil nil seltype seldata)
          (setq title_block (vl-catch-all-apply 'vla-item (list pfs 0)))
          ;; change title block attributes
            (setq t_atts (vlax-invoke title_block 'getattributes))
      
      (foreach att t_atts
        (if (setq attfound (assoc (vla-get-tagstring att )title_atts))
     (vla-put-textstring att (cdr attfound))))
    
          (setq nblock(vl-catch-all-apply 'vla-insertblock (list acsp ppt "c_sdk"  5 5 5 0)))
      (vl-catch-all-apply 'vla-put-xeffectivescalefactor (list nblock 5))
      (vl-catch-all-apply 'vla-put-Yeffectivescalefactor (list nblock 5))
      (vl-catch-all-apply 'vla-put-zeffectivescalefactor (list nblock 5))
      ;; change "c_sdk" block attributes    
      (setq atts (vlax-invoke nblock 'getattributes))
      (setq attpairs (mapcar 'cons tags attvalues))
      (foreach att atts
        (if (setq attfound (assoc (vla-get-tagstring att)attpairs))
     (vla-put-textstring att (cdr attfound))))
     (vla-save doc)(vla-close doc)))))
    
      )
    (setq endTime (getvar "cdate"))
    
    (setq elapsTime (rtos (- endTime startTime)2 6))
      (alert (strcat"Time elapsed:\t" elapsTime))
      (princ)
      )
    "The whole problem with the world is that fools and fanatics are always
    so certain of themselves, and wiser people so full of doubts."
    Bertrand Russell

  5. #5
    Member
    Join Date
    2009-11
    Posts
    13
    Login to Give a bone
    0

    Default Re: Lisp to read an excel and update attributes in title blocks

    Let's try but for now thanks a lot, I have to modify 1800 drawings, maybe I can use field for sdk

  6. #6
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: Lisp to read an excel and update attributes in title blocks

    Ok, do it for your job, perhaps it's a good point about field
    Cheers
    "The whole problem with the world is that fools and fanatics are always
    so certain of themselves, and wiser people so full of doubts."
    Bertrand Russell

Similar Threads

  1. 2013: Batch extraction of title block attributes to excel
    By robechandler167079 in forum AutoCAD General
    Replies: 1
    Last Post: 2015-04-28, 07:29 PM
  2. Update Title Blocks
    By drovang710500 in forum AutoLISP
    Replies: 4
    Last Post: 2013-12-18, 02:16 AM
  3. 2014: Update Title Blocks
    By drovang710500 in forum MDT - General
    Replies: 1
    Last Post: 2013-12-17, 04:35 PM
  4. EXCEL Drawing register using Autocad Title Block Attributes
    By simon_coupland in forum AutoCAD Customization
    Replies: 6
    Last Post: 2010-05-24, 10:13 AM
  5. Replies: 9
    Last Post: 2005-01-25, 02:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •