Write to an Excel document based on xref count?

[markv]

Below is some code that works fine however what i would like to add is a counter to write data to an excel file. What i need is the amount of xref's overlayed and the amount of estimated time is saves per xref. for example. the routine gets the log in name of the user. counts the number of xrefs overlayed in the process, esitmate a time of 1 minute per xref and writes this data to a spreadsheet on a network location. This will be used to help quantify the savings of code writing to some of the naysayers around. any ideas?

 

; Lisp used to auto load Xrefs for project sheet on correct layers & renames
; Created by Mark Voigt
; Revised by Mark Voigt & Wade Wolkenhauer
(defun c:eset (/ OldName refe1)
(command "arx" "load" "DOSLib18.arx") 
;--------------------------------------------------
(setq dir
       (vl-filename-directory
(getvar "dwgprefix")
)
)
;-----------------------------------------------------------------  
(setq dcl_id (load_dialog "elecsetups.dcl"))
 (if (not (new_dialog "elecsetups" dcl_id)) (exit) )
(setq lst (list "tog1" "tog2" "tog3" "tog4"))
(action_tile
  "accept"
  "(progn
  (setq l1 (get_tile \"tog1\"))
  (setq l2 (get_tile \"tog2\"))
  (setq l3 (get_tile \"tog3\"))
  (setq l4 (get_tile \"tog4\"))
  (done_dialog 1)
 )"
)
(action_tile "sall" "(progn (checkall lst))")
(action_tile "call" "(progn (uncheckall lst))")
(action_tile "cancel" "(progn (setq l1 nil l2 nil l3 nil l4 nil l5 nil l6 nil l7 nil l8 nil l9 nil l10 nil l11 nil l12 nil l13 nilxr l14 nil l15 nil)(done_dialog))")
 (start_dialog)
 (unload_dialog dcl_id)
 (if (= l1 "1")(c:e1state))
 (if (= l2 "1")(c:e2state))
 (if (= l3 "1")(c:e3state))
 (if (= l4 "1")(c:reload2))
 (princ)
)
(defun checkall (lst)
 (foreach a lst
   (set_tile a "1")
   )
 )
(defun uncheckall (lst)
 (foreach a lst
   (set_tile a "0")
   )
 )
       ;START NEW Overlay
(defun c:E1state (/ ref)
  (command "-xref" "detach" "*" "")
  (COMMAND "-LAYER" "MAKE" "XREF-NEW"
"m" "XREF-DEMO" "")
(SETVAR "LAYEREVAL" 0)
(COMMAND "-INSERT" "E1NEW" "0,0" "1" "1" "0")
(COMMAND "ERASE" "LAST" "")
       (COMMAND "CLAYER" "XREF-NEW")
       (setq dir
       (vl-filename-directory
(getvar "dwgprefix")
)
)
  (setq ref (CAR (vl-directory-files dir "*a1.dwg")))
   (if (/= ref nil)
   (progn;if xref found
  (setq refnamepath (strcat dir "\\" ref))
(COMMAND "-XREF" "Overlay" refnamepath "0,0" "1" "1" "0")
  (vl-load-com)
(vlax-for blk (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-Acad-Object)))
 (if
  (and
   (equal (vla-get-IsXref blk) :vlax-true)
   (wcmatch (setq OldName (vla-get-Name blk)) "*a1")
  )
  (vla-put-Name blk "NEW"))
    )
(COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E1NEW.LAS" "" "")
(COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E1DEMO.LAS" "" "")
  (c:refdemo)
   );progn
    ;if xref not found then
             (c:refdemo)
);if
) ;defun         
;END NEW Overlay
       ;START DEMO Overlay
(defun c:refdemo (/ ref)
       (COMMAND "CLAYER" "XREF-DEMO")
(setq ref (CAR (vl-directory-files dir "*a1.dwg")))
(if (/= ref nil)
   (progn;if xref found
(setq refnamepath (strcat dir "\\" ref))
(COMMAND "-XREF" "Overlay" refnamepath "0,0" "1" "1" "0")
  (vl-load-com)
(vlax-for blk (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-Acad-Object)))
 (if
  (and
   (equal (vla-get-IsXref blk) :vlax-true)
   (wcmatch (setq OldName (vla-get-Name blk)) "*a1")
  )
  (vla-put-Name blk "DEMO"))
    )
  (c:RELOAD2)
   );progn
    ;if xref not found then
             (c:RELOAD2)
);if
) ;defun         
       ;end demo Overlay

  ;START POWER PLAN Overlay
(defun c:e2state (/ ref)
  (command "-xref" "detach" "*" "")
  (COMMAND "-LAYER" "MAKE" "XREF-NEW"
"m" "XREF-DEMO"
"m" "XREF-E3" "")
  (SETVAR "LAYEREVAL" 0)
(COMMAND "-INSERT" "E2NEW" "0,0" "1" "1" "0")
(COMMAND "ERASE" "LAST" "")
       (COMMAND "CLAYER" "XREF-E3")
(setq dir
       (vl-filename-directory
(getvar "dwgprefix")
)
)
 (setq ref (CAR (vl-directory-files dir "*e3.dwg")))
   (if (/= ref nil)
   (progn;if xref found
  (setq refnamepath (strcat dir "\\" ref))
(COMMAND "-XREF" "Overlay" refnamepath "0,0" "1" "1" "0")
  (vl-load-com)
(vlax-for blk (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-Acad-Object)))
 (if
  (and
   (equal (vla-get-IsXref blk) :vlax-true)
   (wcmatch (setq OldName (vla-get-Name blk)) "*e3")
  )
  (vla-put-Name blk "E3"))
    )
 (COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E2NEW.LAS" "" "")
 (COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E2DEMO.LAS" "" "")
  (c:refnew)
   );progn
    ;if xref not found then
             (c:refnew)
);if
) ;defun         
       ;end POWERPLAN Overlay
  ;START SIGNAL PLAN Overlay
(defun c:e3state (/ ref)
(command "-xref" "detach" "*" "")
  (COMMAND "-LAYER" "MAKE" "XREF-NEW"
"m" "XREF-DEMO"
"m" "XREF-E2" "")
       (SETVAR "LAYEREVAL" 0)
(COMMAND "-INSERT" "E3NEW" "0,0" "1" "1" "0")
(COMMAND "ERASE" "LAST" "")
       (setvar "CLAYER" "XREF-E2")
(setq dir
       (vl-filename-directory
(getvar "dwgprefix")
)
)
 (setq ref (CAR (vl-directory-files dir "*e2.dwg")))
   (if (/= ref nil)
   (progn;if xref found
  (setq refnamepath (strcat dir "\\" ref))
(COMMAND "-XREF" "Overlay" refnamepath "0,0" "1" "1" "0")
  (vl-load-com)
(vlax-for blk (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-Acad-Object)))
 (if
  (and
   (equal (vla-get-IsXref blk) :vlax-true)
   (wcmatch (setq OldName (vla-get-Name blk)) "*e2")
   )
  (vla-put-Name blk "E2"))
    )
 (COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E3NEW.LAS" "" "")
 (COMMAND "-LAYER" "STATE" "IMPORT" "H:\\cad tools\\ELEC LAYER STATES\\E3DEMO.LAS" "" "")
  (c:refnew)
   );progn
    ;if xref not found then
             (c:refnew)
);if
) ;defun         
       ;end SIGNAL PLAN Overlay
;START NEw Overlay
(defun c:refnew (/ ref)
       (COMMAND "CLAYER" "XREF-NEW")
(setq ref (CAR (vl-directory-files dir "*a1.dwg")))
(if (/= ref nil)
   (progn;if xref found
(setq refnamepath (strcat dir "\\" ref))
(COMMAND "-XREF" "Overlay" refnamepath "0,0" "1" "1" "0")
  (vl-load-com)
(vlax-for blk (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-Acad-Object)))
 (if
  (and
   (equal (vla-get-IsXref blk) :vlax-true)
   (wcmatch (setq OldName (vla-get-Name blk)) "*a1")
  )
  (vla-put-Name blk "NEW"))

    )
     (c:refdemo)
  );progn
    ;if xref not found then
             (c:refdemo)
);if
);defun         
      ;end new Overlay

;========================================
(defun c:reload2 () 
(setq a1 (CAR (vl-directory-files dir "*a1.dwg")))
 (command "-xref" "path" "NEW" a1)
 (command "-xref" "path" "DEMO" a1)
(setq e2 (CAR (vl-directory-files dir "*e2.dwg")))
 (command "-xref" "path" "E2" e2)
(setq e3 (CAR (vl-directory-files dir "*e3.dwg")))
 (command "-xref" "path" "E3" e3)
 (dos_msgbox "Operation Complete!" "Electrical Setup" 1 1))

[SOliver]

Hi markv

 

What is considered an overlay:

An inserted xref.

An xref whose bounding box shares possible coordinates with another xref.

Either of the above where the appear higher in the draw order than none-xref objects

Something not listed?

 

SOliver

[markv]

Yes an overlay is an inserted xref. I just want to count the amount of xrefs that are inserted mutliply that by a time factor and write that data into an excel sheet.