Update Layer that named Blocks are on from Spreadsheet result.

[mick_barrett]

Hi,

I have a drawing showing hundreds of borehole and test drilling locations. The various holes are made from four different Block types and each location has a unique name. The locations and completion dates are shown on an embedded Excel spreadsheet.

We need to issue the drawing multiple times as the holes are completed and the request is that the incomplete holes are to be shown in Red.

To this end I have two layers that the blocks reside on, Complete (in Black) and Incomplete (in Red).

Is there a script or text line I can use in the spreadsheet that I can copy and paste into the Command line (or something more automated), that reads that there is a value in column D (Completed Date) and uses the name of the block to select it in the model and change its layer?

I tried the method given in a similar sounding article but the command line didn’t like it.
I tried; (COMMAND "chprop" "g" "B01" "" "la" "COMPLETE" "")

And also a variant on: _.chprop  (ssget "_X" '((2 . "DELTA"))) ;  _layer A-DELTA ;

This is a pic of the drawing;

And a sample of the table, (including the formula which is normally hidden), that identifies completed holes;

CPT-86(C218)	18/08/2023	25.00	28/08/2023	LNG Tank 2	668763.12	2671088.83			(COMMAND "chprop" "g" "CPT-86(C218)" "" "la" "COMPLETE" "")
CPT-67(C219)	18/08/2023	25.00	28/08/2023	LNG Tank 1	668771.72	2670975.65			(COMMAND "chprop" "g" "CPT-67(C219)" "" "la" "COMPLETE" "")
CPT-68(C220)	18/08/2023	25.00	28/08/2023	LNG Tank 1	668833.24	2670975.65			(COMMAND "chprop" "g" "CPT-68(C220)" "" "la" "COMPLETE" "")
B301	22/08/2023	100.00		LNG Tank 2	668809.62	2671112.83
B302	22/08/2023	100.00		LNG Tank 2	668830.62	2671091.83

Any assistance would be gratefully appreciated.
Thanks.

Mick

Product Version Q.111.0.0 AutoCAD 2020.1.3  

[Kent1Cooper]

A [small] sample of the spreadsheet file would be helpful.

[mick_barrett]

Thanks Kent,
Hopefully this isn't too big, it's one of the sheets we have.

[Sea-Haven]

Rather than doing stuff in Excel can read the Excel from Autocad, as you have a XY can look for a block at that location and double check matches name in column 1. If there is a date in the Excel Column D and its red ?? Then change its layer to completed. Is that correct ? 

[mick_barrett]

Yes that would work perfectly, thanks, how do i achieve that?

[Sea-Haven]

It is easy once you understand the CAD <-> Excel relationship using lisp. hard part is finding functions that do thing inside Excel.

Added to my to do list. Maybe tomorrow.

[mick_barrett]

Hi,

Did you get a chance to take a look at this? I’m guessing it’s possibly more complex than I hoped it would be.

thanks,

Mick

[Sea-Haven]

Sorry been busy have some time now, will try maybe even later today.

[Sea-Haven]

Give this a try check the result carefully.

In your Excel I removed the second header line as it may cause an error. 

; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/update-layer-that-named-blocks-are-on-from-spreadsheet-result/td-p/12378356

; Read a Excel and change layers
; By AlanH Nov 2023


(defun c:wow ( / )

(defun getcell2 (row column / )
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
(setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
	(if (setq pos (vl-string-position 58 str))
		(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
		(list str)
    )
)

 ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)

(defun findtxt (ftxt lstents / cnt val)
(setq cnt (length lstents))
(repeat cnt
  (setq val (cadr (nth (setq cnt (- cnt 1)) lstents)))
  (if (and (= val ftxt))
    (command "chprop" (car (nth cnt lstents)) "LA" "COMPLETE" "")
  )
)
(princ)
)

;;;;;; starts here 

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(setq UR (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))
(setq lst (_csv->lst58 radd))
(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))
(setq st  (columnrow st))
(setq end  (columnrow end))

(setq ss (ssget "X" '((0 . "INSERT"))))

(if (= ss nil)
 (progn
  (alert "No blocks found \nWill exit now")
  (exit)
 )
)

(setq lst '())
(repeat (setq x (sslength ss))
(setq ent (ssname ss (setq x (- x 1))))
(setq obj (vlax-ename->vla-object ent))
(if (or (= (vlax-get obj 'Effectivename) "BH-MARK") (= (vlax-get obj 'Effectivename) "CPT-MARK"))
 (progn
  (setq att (vlax-get (car (vlax-invoke obj 'Getattributes)) 'textstring))
  (setq lst (cons (list ent att) lst))
 )
)
)

(setq row 2 )
(repeat (- (cadr end) 2)
  (princ (setq txt (getcell2 row 1)))
  (princ "\n")
  (setq tdate (getcell2 row 4))
  (if (= tdate nil)
    (princ ".")
    (findtxt txt lst)
  )
  (setq row (1+ row))
)

(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))
(princ)
)
(c:wow)

[mick_barrett]

Many thanks for this,
Unfortunately we haven't been able to get it to run properly, from running it line by line in the Visual Lisp editor it seems that the issue is in Line 80 (setq RADD (vlax-get-property CR "Address"))

We get the error
; error: too few actual parameters

Looking at the returned objects in the CR variable, we cannot see "Address". Sorry to be a pain, but would you know why?
Thank you.

Mick

[Sea-Haven]

Ok forgot to say open correct Excel 1st as I don't do a ask for Excel file name. Could be added.

(or (setq myxl (vlax-get-object "Excel.Application"))
(setq myxl (vlax-get-or-create-object "excel.Application"))
)
T
(setq UR (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "UsedRange"))
#<VLA-OBJECT Range 0000000061252708>
(setq CR (vlax-get-property UR "CurrentRegion"))
#<VLA-OBJECT Range 0000000061252B88>
(setq RADD (vlax-get-property CR "Address"))
"$A$1:$G$222"

[Sea-Haven]

Did you have another go ?