Jump to content

modify : replace text from excel lisp


handasa

Recommended Posts

Greetings everyone

i found this lisp created by fixo

https://www.theswamp.org/index.php?topic=13096.0

 

;--------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

 

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:)

Edited by handasa
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...