Modifying text block through excel spreadsheet.

[sergioe]

Hi!

Newbie to the forums, so I hope I'm posting this in the right place.

 

I maintain our office floorplans with employee names in AutoCAD. With 33 buildings and about 125 different CAD files, it can get cumbersome if there are a lot of changes to be made.

 

I want to create a way to make this process easier. I'm thinking if I have some sort of block that can export all employee names and related location numbers to an excel spreadsheet/database, I can easily just edit the excel file without opening the CAD files.

 

Is this possible. Can anyone point me in the right direction?

[BIGAL]

I did something similar "where do they sit" I used the phone number as the link and compared it to our phone list. I took the office plan added the phone number twice two layers the second is used to reference a picture of the staff member which is inserted into the floor plan. The second part is a reference grid this was added to the phone list location & grid A 5. Its not automatic as I have to manually edit the excel to add a update routine was a lot of work.

 

 

I think I posted this before but here it is again

 

; routine to insert images based on a block position
; by Alan H Aug 2012
(vl-load-com)
(defun *error* ( msg )
       (princ "\nerror: ")
       (alert (strcat "\n Missing phone number image" name))
       (princ)
)
(setq mspace (vla-get-modelspace (vla-get-activedocument (vlax-get-acad-object))))

(setvar "clayer" "staffimages")
(setq ss1 (ssget "x"  (list (cons 0 "INSERT") (cons 2 "phnum"))))  ;block name is phnum
(setq len (sslength ss1))
(setq x 0)
; then insert block value at x,y

; need to pull out block find x,y and value
(repeat len
(foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS1 x )) 'getattributes)
       (if (= "PHNUM" (strcase (vla-get-tagstring att)))  ; tagname is phnum
       (progn
(setq blknum (vla-get-textstring att))
      (setq inspt (vla-get-insertionpoint att))
       )
)
)
;(setq blkval (rtos blknum 2 0))
(setq name (strcat "P:\\Offices\\" blknum ".jpg"  ))
(princ (strcat "\n" name))

(vla-AddRaster mspace name inspt 1.5 0.0) 
;(command "-image" "attach" name (vlax-3d-point inspt) 1.0 0.0)
(setq x (+ x 1))
) ;repeatlen
(setq ss1 nil)
(princ "alla done"
(princ)

 

Extract phnum put in excel and do a sort and a bit of copy and paste lines up with phone list formm other source.

 

; inserts a phone block based on two grids
(vl-load-com)
;(defun *error* ( msg )
;        (princ "error: ")
;       (princ (strcat " Missing phone number" userphnum ))
;        (princ)


; change the 410 to layout name

;;-------------------=={ Parse Numbers }==--------------------;;
;;                                                            ;;
;;  Parses a list of numerical values from a supplied string. ;;
;;------------------------------------------------------------;;
;;  Author: Lee Mac, Copyright © 2011 - [url="http://www.lee-mac.com"]www.lee-mac.com[/url]       ;;
;;------------------------------------------------------------;;
;;  Arguments:                                                ;;
;;  s - String to process                                     ;;
;;------------------------------------------------------------;;
;;  Returns:  List of numerical values found in string.       ;;
;;------------------------------------------------------------;;
(defun LM:ParseNumbers ( s )
 (
   (lambda ( l )
     (read
       (strcat "("
         (vl-list->string
           (mapcar
             (function
               (lambda ( a b c )
                 (if
                   (or
                     (< 47 b 58)
                     (and (= 45 b) (< 47 c 58) (not (< 47 a 58)))
                     (and (= 46 b) (< 47 a 58) (< 47 c 58))
                   )
                   b 32
                 )
               )
             )
             (cons nil l) l (append (cdr l) (list nil))
           )
         )
         ")"
       )
     )
   )
   (vl-string->list s)
 )
)


(defun outfile ()
(setq textval (vla-get-textstring textobj))
(setq userphnum (rtos (car (Lm:parsenumbers textval)) 2 0))
(setq obj1 (vlax-ename->vla-object (car (entsel "\nSelect the Vertical line"))))
(setq Lay1 (vla-get-layer obj1))
(setq obj2 (vlax-ename->vla-object (car (entsel "\nSelect the Horizontal Line"))))
(setq Lay2 (vla-get-layer obj2))
(setq ans (strcat userphnum "-" "Bro "  lay1 "-" Lay2))
(write-line ans fo)
) ;end defun
; this is where program starts
; open file to write
(setq fo (open "P://brougham st offices//stafflocation.txt" "A"))

(while (setq textobj (vlax-ename->vla-object (car (entsel "\nSelect the person's phone number>>"))))
(outfile)
)
(close fo)

(princ)

[BIGAL]

Just had a crash attempt 2 using a staff members phnum as link its twice as one is for display the other is for where their image will appear. It uses the phone list say in excel as a cross reference but the excel is manulay edited but with some help via the second lisp, using sort to match phnum and location.

 

 

Code to insert images each JPG is just named as a phone number 1234.jpg

 

; routine to insert images based on a block position
(vl-load-com)
(defun *error* ( msg )
       (princ "\nerror: ")
       (alert (strcat "\n Missing phone number image" name))
       (princ)
)
(setq mspace (vla-get-modelspace (vla-get-activedocument (vlax-get-acad-object))))

(setvar "clayer" "staffimages")
(setq ss1 (ssget "x"  (list (cons 0 "INSERT") (cons 2 "phnum"))))  ;block name is phnum
(setq len (sslength ss1))
(setq x 0)
; then insert block value at x,y

; need to pull out block find x,y and value
(repeat len
(foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS1 x )) 'getattributes)
       (if (= "PHNUM" (strcase (vla-get-tagstring att)))  ; tagname is phnum
       (progn
(setq blknum (vla-get-textstring att))
      (setq inspt (vla-get-insertionpoint att))
       )
)
)
;(setq blkval (rtos blknum 2 0))
(setq name (strcat "P:\\Brougham St Offices\\" blknum ".jpg"  ))
(princ (strcat "\n" name))

(vla-AddRaster mspace name inspt 1.5 0.0) 
;(command "-image" "attach" name (vlax-3d-point inspt) 1.0 0.0)
(setq x (+ x 1))
) ;repeatlen
(setq ss1 nil)
(princ "alla done"
(princ)

Create grid reference

; inserts a phone block based on two grids
(vl-load-com)
;(defun *error* ( msg )
;        (princ "error: ")
;       (princ (strcat " Missing phone number" userphnum ))
;        (princ)


; change the 410 to layout name

;;-------------------=={ Parse Numbers }==--------------------;;
;;                                                            ;;
;;  Parses a list of numerical values from a supplied string. ;;
;;------------------------------------------------------------;;
;;  Author: Lee Mac, Copyright © 2011 - [url="http://www.lee-mac.com"]www.lee-mac.com[/url]       ;;
;;------------------------------------------------------------;;
;;  Arguments:                                                ;;
;;  s - String to process                                     ;;
;;------------------------------------------------------------;;
;;  Returns:  List of numerical values found in string.       ;;
;;------------------------------------------------------------;;
(defun LM:ParseNumbers ( s )
 (
   (lambda ( l )
     (read
       (strcat "("
         (vl-list->string
           (mapcar
             (function
               (lambda ( a b c )
                 (if
                   (or
                     (< 47 b 58)
                     (and (= 45 b) (< 47 c 58) (not (< 47 a 58)))
                     (and (= 46 b) (< 47 a 58) (< 47 c 58))
                   )
                   b 32
                 )
               )
             )
             (cons nil l) l (append (cdr l) (list nil))
           )
         )
         ")"
       )
     )
   )
   (vl-string->list s)
 )
)


(defun outfile ()
(setq textval (vla-get-textstring textobj))
(setq userphnum (rtos (car (Lm:parsenumbers textval)) 2 0))
(setq obj1 (vlax-ename->vla-object (car (entsel "\nSelect the Vertical line"))))
(setq Lay1 (vla-get-layer obj1))
(setq obj2 (vlax-ename->vla-object (car (entsel "\nSelect the Horizontal Line"))))
(setq Lay2 (vla-get-layer obj2))
(setq ans (strcat userphnum "-" "Bro "  lay1 "-" Lay2))
(write-line ans fo)
) ;end defun
; this is where program starts
; open file to write
(setq fo (open "P://brougham st offices//stafflocation.txt" "A"))

(while (setq textobj (vlax-ename->vla-object (car (entsel "\nSelect the person's phone number>>"))))
(outfile)
)
(close fo)

(princ)

[edwinprakoso]

You can also try using ATTOUT to export to spreadsheet. Then after editing, update the attributes in AutoCAD using ATTIN.