Topic: modify : replace text from excel lisp

[handasa]

Greetings everyone
i found this lisp created by fixo [/size]
https://www.theswamp.org/index.php?topic=13096.0

Code: [Select]

;--------code start--------;
(defun get-excel-data (/ ExcData xlApp xlBook xlCell xlRange xlSheet)
  (if (not (setq xlApp (vlax-get-object "Excel.Application")))
    (setq xlApp (vlax-create-object "Excel.Application"))
  )
  (if xlApp
    (progn
      (if
    (not
      (vl-catch-all-error-p
        (vl-catch-all-apply
          (function    (lambda    ()
              (setq
                xlBook (vlax-get-property xlApp "ActiveWorkBook")
              )
            )
          )
        )
      )
    )
    (progn
       (vlax-invoke-method xlBook "Activate")
       (setq xlSheet (vlax-get-property xlBook "ActiveSheet"))
     (if
      (vl-catch-all-error-p
        (vl-catch-all-apply
          (function    (lambda    ()
              (setq xlRange (vlax-get-property xlApp "Selection"))))))
              (setq xlRange (vlax-get-property xlSheet "UsedRange")))
       
    (setq ExcData (vlax-safearray->list
               (vlax-variant-value
                 (vlax-get-property xlRange "Value")
               )
            )
       )    ;or Value2
       (setq ExcData
          (mapcar (function
                (lambda (x) (mapcar 'vlax-variant-value x))
              )
              ExcData
          )
       )

       (vlax-invoke-method xlApp "Quit")
    )
      )
      (mapcar (function    (lambda    (x)
              (vl-catch-all-apply
                (function (lambda ()
                    (progn
                      (vlax-release-object x)
                      (setq x nil)
                    )
                      )
                )
              )
            )
          )
          (list xlCell xlRange xlSheet xlBook xlApp)
      )
    )
  )
  (gc)
  ExcData
)
; ------------------------------------------;

(defun newstring (old new str)
(while
(vl-string-search old str)
(setq str (vl-string-subst new old str))))

; ------------------------------------------;
(defun C:FRE ()

(vl-load-com)
(or adoc
    (setq adoc (vla-get-activedocument
(vlax-get-acad-object))))
(or acsp (setq acsp (if (= (getvar "CVPORT") 1)
(vla-get-paperspace
adoc)
(vla-get-modelspace
adoc)
)
)
    )
(vla-endundomark
  adoc)
(vla-startundomark
  adoc)
(setq repList (get-excel-data))
(vla-zoomall (vlax-get-acad-object))
(setq ss (ssget "_X" (list (cons 0 "*TEXT"))))
  (setq axss (vla-get-activeselectionset adoc))
  (vlax-for a axss
    (setq strRep (vlax-get-property a "TextString"))
    (while (vl-some (function (lambda(x)
                 (vl-string-search x strRep)))
        (mapcar 'car repList))

    (setq newStr     
    (car
    (vl-remove-if 'not
    (mapcar (function (lambda(a b)(newstring a b strRep)))
       
        (mapcar 'car repList)(mapcar 'cadr repList)))))
    (setq strRep newStr))
      (vlax-put-property a "TextString" newStr)
           (vla-update a))
     

(vla-endundomark
  adoc)
         
  (princ)
)

(prompt "\n\t\t  * Type FRE to execute... *")
(princ)
; TesT :(C:FRE)


which replace text from opened excel window in background.

the point here that this lisp process the same text many time

say i have two columns as follow
original text      replaced with     
green                red
red                   yellow
yellow              orange

so if i have three texts in the drawing "green , red, yellow" the result that the three texts will be "orange" which is undesired result

i tried to move the processed text to locked layer using the command
(vla-put-Layer eo (getvar "CLAYER")) and making the current layer locked but this didn't work ...

any suggestions ?
any help will be appreciated , thanks in advance and good night

[MP]

Odd how you found the code but not an author to give credit.