Export Block count and Description in Excel

[Panayotov]

Hi, folks!

I am new member of this forum, but from a long time I have found very useful solutions and LISP programs here.

I wonder if anyone can help me to modify one Lisp, which I am using. I am trying to find a way to extract quantities from AutoCAD drawings automatically. The program aims is to export the number and description of the blocks in excel file. The blocks must be sorted first by preset layers list and for each layer to be sorted by block name (A-z, 0-9).

I have a similar program, but it can not work as I described.

Can the program be modified so that it can export information from many drawings (such as date extraction but only blocks). I attach the program which I have.

 

Many thanks in advance!

BCEX.lsp

[Tharwat]

Welcome to CADTutor Panayotov

 

Try this program that I have just finished from writing it and the program export the collected data to an excel file with csv format , so you can re-save the file in any other format if you'd like to .

 

Anyway try it and let me know .

 

(defun c:Blocks2Excel  (/ _doc nm ds b ly f lst fl op)
 ;;--------------------------------------------;;
 ;;						;;
 ;; Author : Tharwat 10.June.2015		;;
 ;;--------------------------------------------;;
 ;; Compute the quantity of all blocks in	;;
 ;; a drawing and write the outcome to an	;;
 ;; Excel file format in format .csv		;;
 ;;--------------------------------------------;;
 ;; Layer Name:,Block Name:,QTY,Description	;;
 ;;						;;
 ;;--------------------------------------------;;
 (vlax-for l  (vla-get-layouts
                (setq _doc
                       (vla-get-ActiveDocument (vlax-get-acad-object))))
   (vlax-for o  (vla-get-block l)
     (if (and (eq (vla-get-objectname o) "AcDbBlockReference")
              (setq nm (vla-get-effectivename o))
              (setq ds
                     (if (vlax-property-available-p
                           (setq b (vla-item (vla-get-blocks _doc) nm))
                           'comments)
                       (vla-get-comments b)
                       "")
                    )
              (setq ly (vla-get-layer o))
              )
       (if (vl-some '(lambda (x)
                       (and (eq ly (car x))
                            (eq nm (cadr x))
                            (setq f x)
                            )
                       )
                    lst)
         (setq lst (subst (list ly nm (1+ (caddr f)) ds) f lst))
         (setq lst (cons (list ly nm 1 ds) lst))
         )
       )
     )
   )
 (setq lst (vl-sort lst '(lambda (j k) (< (car j) (car k)))))
 (cond ((not lst)
        (alert "Couldn't find any block in this drawing !!"))
       ((and (setq fl (getfiled "Specify new Excel file name"
                                (getvar 'DWGPREFIX)
                                "csv"
                                1))
             (setq op (open fl "w"))
             )
        (write-line "Layer Name:;Block Name:;QTY;Description" op)
        (mapcar '(lambda (x)
                   (write-line
                     (strcat (car x)
                             ";"
                             (cadr x)
                             ";"
                             (itoa (caddr x))
                             ";"
                             (nth 3 x))
                     op))
                lst)
        (close op)
        )
       )
 (princ)
 )(vl-load-com)

[Panayotov]

Thanks!!!

The lisp is working great!

In this way I can sort the blocks by layers in excel, but is it possible to interchange the quantity and description. I know I can do it in excel but should do it each time.

Currently to export all quantities (from multiple drawing) I open a new drawing and attach drawings of all drawing, then bind each drawing,

then explode all bind drawing and after that I start program which export quantities. Is there smarter way to do that?

[Panayotov]

Actually there is no need to interchange the quantity and description.

The data should be further processed in Excel, so that will be done automatically.

[Tharwat]

Thanks!!!

The lisp is working great!

Nice , you are welcome.

 

Currently to export all quantities (from multiple drawing) I open a new drawing and attach drawings of all drawing, then bind each drawing,

then explode all bind drawing and after that I start program which export quantities. Is there smarter way to do that?

It could be done in codes , but that needs too much line of codes and frankly I have no time to do that . sorry.

[Panayotov]

No problem. This way is just fine. Thanks again!

[arnie79]

Hi... The lisp is perfect... is there any possibility to select the specific cad blocks not the entire blocks?

Thanks

[Grrr]

Hi... The lisp is perfect... is there any possibility to select the specific cad blocks not the entire blocks?

Thanks

 

Yup, its possible to filter by the effectivename.

 

Thats a nice code from Tharwat BTW.

[Tharwat]

Hi... The lisp is perfect... is there any possibility to select the specific cad blocks not the entire blocks?

Thanks

Hi,

 

Sure its possible.

I have just modified the program for you but I don't have time to test it out so please try it and let me know.

 

(defun c:Blocks2Excel (/ bks int sel ent nm ds b ly f lst fl op)
 ;;--------------------------------------------;;
 ;;						;;
 ;; Author : Tharwat 10.June.2015		;;
 ;; Modified on 3rd.Aug.2017 to allow users to	;;
 ;; select specific blocks.			;;
 ;;--------------------------------------------;;
 ;; Compute the quantity of all blocks in	;;
 ;; a drawing and write the outcome to an	;;
 ;; Excel file format in format .csv		;;
 ;;--------------------------------------------;;
 ;; Layer Name:,Block Name:,QTY,Description	;;
 ;;						;;
 ;;--------------------------------------------;;
 (princ"\nSelect blocks :")
 (if (setq bks (vla-get-blocks (vla-get-ActiveDocument (vlax-get-acad-object)))
           int -1
           sel (ssget '((0 . "INSERT"))))
   (while (setq ent (ssname sel (setq int (1+ int))))
     (if (and (setq nm (vla-get-effectivename (vlax-ename->vla-object ent)))
              (setq ds (if (vlax-property-available-p (setq b (vla-item bks nm)) 'comments)
                         (vla-get-comments b)
                         "")
                    )
              (setq ly (cdr (assoc 8 (entget ent))))
              )
       (if (vl-some '(lambda (x) (and (eq ly (car x)) (eq nm (cadr x)) (setq f x))) lst)
         (setq lst (subst (list ly nm (1+ (caddr f)) ds) f lst))
         (setq lst (cons (list ly nm 1 ds) lst))
         )
       )
     )
   )      
 (setq lst (vl-sort lst '(lambda (j k) (< (car j) (car k)))))
 (cond ((not lst) (alert "Couldn't find any block in this drawing !!"))
       ((and (setq fl (getfiled "Specify new Excel file name" (getvar 'DWGPREFIX) "csv" 1))
             (setq op (open fl "w"))
             )
        (write-line "Layer Name:;Block Name:;QTY;Description" op)
        (mapcar '(lambda (x)
                   (write-line (strcat (car x) ";" (cadr x) ";" (itoa (caddr x)) ";" (nth 3 x)) op))
                lst)
        (close op)
        )
       )
 (princ)
 )(vl-load-com)

 

Thats a nice code from Tharwat BTW.

 

Thank you.

[arnie79]

Hi Tharwat... thanks a lot. can i ask favor if you can revise the program of BCEX.lsp same concept that you provide instead of entire blocks count it will select specific blocks only.

Thanks in advance

[arnie79]

[Tharwat]

Hi Tharwat... thanks a lot. can i ask favor if you can revise the program of BCEX.lsp same concept that you provide instead of entire blocks count it will select specific blocks only.

Thanks in advance

 

Hi,

 

Did not you try the modified program on reply No# 9 yet?

 

Are you wanting to add specific block names into the program ? if yes, then just list these block names down.

[BIGAL]

Hi Tharwat would you not use something like this to select blocks rather than a hard coded list.

 

(setq ssbame "")
(while (/= (setq ent (entsel "Pick block  <Cr> to exit ")) nil)
(setq bname (cdr (assoc 2 (entget (car ent)))))
(setq ssbname (strcat ssbname "," bname))
)

(setq sel (ssget (list (cons 0 "INSERT")(cons 2 ssbname))))

[arnie79]

Hi Tharwat, I tried it thanks:)... by the way if possible to arrange the excel output by column for example block name in one column and description in other column and qty another column.

thanks:)

[Tharwat]

Hi Tharwat would you not use something like this to select blocks rather than a hard coded list.

 

(setq ssbame "")
(while (/= (setq ent (entsel "Pick block  <Cr> to exit ")) nil)
(setq bname (cdr (assoc 2 (entget (car ent)))))
(setq ssbname (strcat ssbname "," bname))
)

(setq sel (ssget (list (cons 0 "INSERT")(cons 2 ssbname))))

[/quote]

Hi,

Honestly I don't prefer this way in general because if a user missed selecting a block then the program would move to next step and ask the user to select the fore-said block references that are filtered into the 'ssbname' variable list of names.

Just to pay your attention for something 'should' be important in this case.
If you select something with the following codes that means you have a none-nil value which is not equal to nil, so there is no need to check if its not equal to nil as you have demonstrated into your codes with while function and the following is enough.

[code] (while (setq ent (entsel "Pick block  <Cr> to exit ")) ;; if something selected then this means none-nil value which also means not equal to nil 

 

Besides that, you did not check if a user selected a Block reference which is very important to do otherwise the codes would error if the user selected a line for instance and as you know the line object does not have the DXF 2 which represents the block name as in any Block object.

[Tharwat]

Hi Tharwat, I tried it thanks:)... by the way if possible to arrange the excel output by column for example block name in one column and description in other column and qty another column.

thanks:)

 

Hi,

 

Try this [uNTESTED] mods of the same program and let me know.

 

(defun c:Blocks2Excel (/ bks int sel ent nm ds b f lst fl op)
 ;;--------------------------------------------;;
 ;;						;;
 ;; Author : Tharwat 10.June.2015		;;
 ;; Modified on 3rd.Aug.2017 to allow users to	;;
 ;; select specific blocks.			;;
 ;;--------------------------------------------;;
 ;; Compute the quantity of all blocks in	;;
 ;; a drawing and write the outcome to an	;;
 ;; Excel file format in format .csv		;;
 ;;--------------------------------------------;;
 ;; Block Name,Description,QTY			;;
 ;;						;;
 ;;--------------------------------------------;;
 (princ"\nSelect blocks :")
 (if (setq bks (vla-get-blocks (vla-get-ActiveDocument (vlax-get-acad-object)))
           int -1
           sel (ssget '((0 . "INSERT"))))
   (while (setq ent (ssname sel (setq int (1+ int))))
     (if (and (setq nm (vla-get-effectivename (vlax-ename->vla-object ent)))
              (setq ds (if (vlax-property-available-p (setq b (vla-item bks nm)) 'comments)
                         (vla-get-comments b)
                         "")
                    )
              )
       (if (vl-some '(lambda (x) (and (eq nm (cadr x)) (setq f x))) lst)
         (setq lst (subst (list nm ds (1+ (caddr f))) f lst))
         (setq lst (cons (list nm ds 1) lst))
         )
       )
     )
   )      
 (setq lst (vl-sort lst '(lambda (j k) (< (car j) (car k)))))
 (cond ((not lst) (alert "Couldn't find any block in this drawing !!"))
       ((and (setq fl (getfiled "Specify new Excel file name" (getvar 'DWGPREFIX) "csv" 1))
             (setq op (open fl "w"))
             )
        (write-line "Block Name;Description;QTY" op)
        (mapcar '(lambda (x)
                   (write-line (strcat (car x) ";" (cadr x) ";" (itoa (caddr x))) op))
                lst)
        (close op)
        )
       )
 (princ)
 ) (vl-load-com)

[Grrr]

Hi,

Honestly I don't prefer this way in general because if a user missed selecting a block then the program would move to next step and ask the user to select the fore-said block references that are filtered into the 'ssbname' variable list of names.

 

Hi Tharwat,

Just to elaborate BIGAL's suggestion - you could always error-trap it. Example:

 

; (PickBlockNames)
(defun PickBlockNames ( / rec )
 (defun rec ( L / e nm )
   (cond
     ( (setq e (car (entsel (strcat "\n" (if L (strcat "Pick next block [" (vl-string-right-trim "," (apply 'strcat (mapcar '(lambda (x) (strcat x ",")) L))) "] <done> :") "Pick block <exit> :")))))
       (and (member '(0 . "INSERT") (entget e)) (setq nm (vla-get-EffectiveName (vlax-ename->vla-object e))) )
       (rec (if (and nm (cond ((member nm L) (prompt "\nThat block is already selected."))(T))) (cons nm L) L ))
     )
     ( (= 7 (getvar 'errno)) (setvar 'errno 0) (prompt "\nMissed.") (rec L) )
     ( L )
   )
 )
 (rec nil)
); defun PickBlockNames

 

Personally I like BIGAL's suggestions, whatever how good or bad he coded - his point is to provide an idea/solution.

[JochemB]

On 10/06/2015 at 14:06, Tharwat said:

Welcome to CADTutor Panayotov

 

Try this program that I have just finished from writing it and the program export the collected data to an excel file with csv format , so you can re-save the file in any other format if you'd like to .

 

Anyway try it and let me know .

 

 

(defun c:Blocks2Excel  (/ _doc nm ds b ly f lst fl op)
 ;;--------------------------------------------;;
 ;;						;;
 ;; Author : Tharwat 10.June.2015		;;
 ;;--------------------------------------------;;
 ;; Compute the quantity of all blocks in	;;
 ;; a drawing and write the outcome to an	;;
 ;; Excel file format in format .csv		;;
 ;;--------------------------------------------;;
 ;; Layer Name:,Block Name:,QTY,Description	;;
 ;;						;;
 ;;--------------------------------------------;;
 (vlax-for l  (vla-get-layouts
                (setq _doc
                       (vla-get-ActiveDocument (vlax-get-acad-object))))
   (vlax-for o  (vla-get-block l)
     (if (and (eq (vla-get-objectname o) "AcDbBlockReference")
              (setq nm (vla-get-effectivename o))
              (setq ds
                     (if (vlax-property-available-p
                           (setq b (vla-item (vla-get-blocks _doc) nm))
                           'comments)
                       (vla-get-comments b)
                       "")
                    )
              (setq ly (vla-get-layer o))
              )
       (if (vl-some '(lambda (x)
                       (and (eq ly (car x))
                            (eq nm (cadr x))
                            (setq f x)
                            )
                       )
                    lst)
         (setq lst (subst (list ly nm (1+ (caddr f)) ds) f lst))
         (setq lst (cons (list ly nm 1 ds) lst))
         )
       )
     )
   )
 (setq lst (vl-sort lst '(lambda (j k) (< (car j) (car k)))))
 (cond ((not lst)
        (alert "Couldn't find any block in this drawing !!"))
       ((and (setq fl (getfiled "Specify new Excel file name"
                                (getvar 'DWGPREFIX)
                                "csv"
                                1))
             (setq op (open fl "w"))
             )
        (write-line "Layer Name:;Block Name:;QTY;Description" op)
        (mapcar '(lambda (x)
                   (write-line
                     (strcat (car x)
                             ";"
                             (cadr x)
                             ";"
                             (itoa (caddr x))
                             ";"
                             (nth 3 x))
                     op))
                lst)
        (close op)
        )
       )
 (princ)
 )(vl-load-com)

 

 

 

Hello,

 

I love your LISP, but is it possible to exclude blocks that are used in the Paperspace? 

I'am new at LISP so I have no ideau how to do that in this code.

 

Thanks!

[dober]

It would also be possible to read the visibility of dynamic blocks.

[BIGAL]

Simple answer yes.

 

1st step get a copy of Lee-mac.com Dynamic block functions.

 

2nd step

(setq obj (vlax-ename->vla-object (car (entsel "\nSelect a dynamic block "))))

3rd step

(setq visname (LM:getvisibilityparametername obj))