Changing Dynamic Block attribute value from Excel.

[G.Aquarius]

Hello, 

New to LISP,  would like some help. 

 

I'm try to make a lisp where it selects a dynamic block, reads one of the attributes, goes to an excel file, searches Column C for a cell that matches the attribute, and then replaces that attribute info with what's there in Column D. 

Say

- attribute "REF" was "GA-103" 

- C4 has "GA-103" in it

- D4 has "GA-43-103" in it

Would like to change the attribute "REF" value from "GA-103" to "GA-43-103". 

 

I have attached a dwg and xlsx file. 

Test For CADTutor - 2.dwg Test For CADTutor - 2.xlsx

[BIGAL]

What do you know about Lisp, I have read cells from excel code, its based on getexcel.lsp with some extra functions.

 

Let us know can post the excel functions that is not working code.

[fuccaro]

For beginner lispers I would recommend a different approach. Can you export the excel file in CSV format? That's plain text, much simpler to read from within AutoLisp

[BIGAL]

I tend to disagree about using csv reading direct from Excel is not hard it just requires,

 

open link application to Excel, (openexcel)

get say full range of used cells, (getrangexl2) returns row & column as numbers (1 3) (7 4) see sample xls

get a cell value via row and column as numbers (getcell2 row column) start at row 2, column 3.

 

Need a couple more defuns that are called. _csv->lst58, Alpha2Number, ColumnRow. Just use something like (if (not columnrow)(load "Alan excel library3")) then all the defuns are available without adding to core code. The code started life as getexcel and I added more functions, with help from a few excel/lisp people like Fixo.

 

A short version and relevant functions is attached. To use for simplicity just open Excel before running the code, also Note "Enable editing" if testing sample.

 

Ps looking at Librecalc lisp library also.

 

 

 

 

 

Alan Excel library3.lsp

Edited June 25 by BIGAL

[BIGAL]

Try this. Note your values in sample did not match the Excel values added more. 

 

; https://www.cadtutor.net/forum/topic/87091-changing-dynamic-block-attribute-value-from-excel/

(defun c:ex2atts  (/ row column lst x atts att obj st end)
(if (not ColumnRow)(load "alan excel library3"))

(getrangexl)

(setq row 2 column (cadr end) lst '())

(repeat (- (car end) 1)
(setq lst (cons (list (getcell2 row (- column 1))(getcell2 row column)) lst))
(setq row (1+ row))
)

(setq ss (ssget '((0 . "INSERT")(66 . 1))))

(repeat (setq x (sslength ss))
 (setq obj (vlax-ename->vla-object (ssname ss (setq x (- x 1)))))
 (setq atts (vlax-invoke obj 'Getattributes))
 (foreach att atts
  (if (= (vlax-get att "tagstring") "REF")
  (progn
  (setq val (vlax-get att 'textstring))
  (foreach lstval lst
    (if (= (car lstval) val)
    (vlax-put att 'Textstring (cadr lstval))
    )
  )
  )
  )
 )
)

(princ)
)

(c:ex2atts)

 

[G.Aquarius]

Hey @BIGAL, sorry I didn't get a chance to check this, life got busy. 

 

Thank you for the code, I made a small change to load to add the the file path before the "Alan Excel Library3.lsp".

 

Running the (load "___") by itself, it results in the following error:

_________________________________ 

T 
nil 
nil 
nil 
COLUMNROW 
ALPHA2NUMBER 
_CSV->LST58 
GETCELL2 
GETRANGEXL 
; error: malformed list on input

__________________________________

 

Can you please let me know what this is and how to fix it? 

[BIGAL]

My fault the closeexcel is missing a closing bracket at end add a ")" as last line should fix.

[G.Aquarius]

@BIGAL

 

The repeat loop to work with multiple blocks was nice, and learnt the "vlax-get" and "vlax-put" functions from you, so thank you  .

 

I actually researched a bit more and used the  GETCELLSFUNCTION Lisp from Jeffery P. Sanders and the LM:sublst from Lee Mac, to come up with the lisp attached. It works, but do let me know if I can improve it. 

FIND NAME & REPLACE WITH NEW NAME.lsp Test For CADTutor - 2.xlsx Test For CADTutor - 2.dwg

Edited July 5 by G.Aquarius