LISP routine to copy "_Dist" output

[kylesom]

Hi

 

At present we have a repetitive process in the office that means we have a guy measuring distances in CAD, copying/typing X,Y,Z distances into an excel sheet where a calc is performed.

 

I would like / need a LISP routine that will copy X,Y and Z and then when I hit paste in Excel, the data is pasted in 3 seperate cells (either columns or rows, whichever works!).

 

Has it been done before or would anyone like to have a go, or show me how to do it?

 

Cheers

Kyle

[Lee Mac]

DataExtraction?

[SLW210]

Have you tried _DATAEXTRACTION?

[kylesom]

DataExtraction?

 

Not applicable - its to measure distances between cad objects, not measuring the lengths of objects. And its not every object in the drawing or even by layer. Distance from/to points have to be selected manually.

[Lee Mac]

Perhaps give something like this a try:

 

(defun c:L2XL ( / *error* _release p1 p2 r xlapp xlcells ) (vl-load-com)
 ;; © Lee Mac 2011

 (defun *error* ( msg )
   (if (and XLApp (not (vlax-object-released-p XLApp))) (vlax-put-property XLApp 'Visible :vlax-true))
   (mapcar '_release (list XLCells XLApp))
   (or (wcmatch (strcase msg) "*BREAK,*CANCEL*,*EXIT*")
       (princ (strcat "\n** Error: " msg " **")))
   (princ)
 )

 (defun _release ( object )
   (if (and object (not (vlax-object-released-p object)))
     (vlax-release-object object)
   )
 )

 (if (setq XLApp (vlax-get-or-create-object "Excel.Application"))
   (progn
     (setq XLCells
       (vlax-get-property
         (vlax-get-property
           (vlax-get-property
             (vlax-invoke-method (vlax-get-property XLApp 'Workbooks) 'Add)
             'Sheets
           )
           'Item 1
         )
         'Cells
       )
     )
     (setq r 0)

     (while
       (and
         (setq p1 (getpoint "\nSpecify First Point <Done>: "))
         (setq p2 (getpoint "\nSpecify Next Point <Done>: " p1))
       )
       (vlax-put-property XLCells 'Item (setq r (1+ r)) 1 (rtos (distance p1 p2)))
     )
     (vlax-put-property XLApp 'Visible :vlax-true)
     
     (mapcar '_release (list XLCells XLApp)) (gc) (gc)
   )
   (princ "\n--> Could not interface with MSExcel.")
 )
 (princ)
)

Press Enter when finished picking points.

[Lee Mac]

Minor Update ^^.

[alanjt]

FYI, you could also cheat (not that there's anything wrong with what you've done) and just write it to a text file (saved as *.XLS), just separating each item with a tab - it'll open perfectly in Excel.

[SLW210]

Not applicable - its to measure distances between cad objects, not measuring the lengths of objects. And its not every object in the drawing or even by layer. Distance from/to points have to be selected manually.

 

And yet you felt no need to include such relevant information. You could still use _DATAEXTRACTION.

[BlackBox]

FYI, you could also cheat (not that there's anything wrong with what you've done) and just write it to a text file (saved as *.XLS), just separating each item with a tab - it'll open perfectly in Excel.

 

I've done this for .CSV before; I thought it was pretty simple to:

 

(setq textLines '(("This" "is" "a" "test")
                 ("Just" "another" "example")))
(foreach item  textLines
 (print
   (vl-catch-all-apply
     'strcat
     (mapcar
       (function
         (lambda (x)
           (strcat x ",")
           )
         )
       item)
     )
   )
 )

 

I've honestly never looked into replacing "," with a tab (and obviously .CSV with .XLS*).

 

Edit - Instead, I went the (setq xl (vlax-get-or-create-object "Excel.Application")) route. LoL

Edited April 21, 2011 by BlackBox
^^ Minor code update ^^

[alanjt]

"\t" will do it.

 

BTW, why are you catching it as an error? If it does throw an error, won't it write that error to the file?

[BlackBox]

BTW, why are you catching it as an error? If it does throw an error, won't it write that error to the file?

 

I 'spose you're right... I wrote the code on the fly (as noted by the print)... it's not part of any routines. I only included the write-line for others' use.

[alanjt]

I 'spose you're right... I wrote the code on the fly (as noted by the print)... it's not part of any routines. I only included the write-line for others' use.

Suuuuuure.

[BlackBox]

Suuuuuure. [ATTACH]27165[/ATTACH]

 

You really drink from the (lilly) pond a lot! LoL

[alanjt]

You really drink from the (lilly) pond a lot! LoL

Probably a bit too much.

[kylesom]

Perhaps give something like this a try:

 

(defun c:L2XL ( / *error* _release p1 p2 r xlapp xlcells ) (vl-load-com)
 ;; © Lee Mac 2011

 (defun *error* ( msg )
   (if (and XLApp (not (vlax-object-released-p XLApp))) (vlax-put-property XLApp 'Visible :vlax-true))
   (mapcar '_release (list XLCells XLApp))
   (or (wcmatch (strcase msg) "*BREAK,*CANCEL*,*EXIT*")
       (princ (strcat "\n** Error: " msg " **")))
   (princ)
 )

 (defun _release ( object )
   (if (and object (not (vlax-object-released-p object)))
     (vlax-release-object object)
   )
 )

 (if (setq XLApp (vlax-get-or-create-object "Excel.Application"))
   (progn
     (setq XLCells
       (vlax-get-property
         (vlax-get-property
           (vlax-get-property
             (vlax-invoke-method (vlax-get-property XLApp 'Workbooks) 'Add)
             'Sheets
           )
           'Item 1
         )
         'Cells
       )
     )
     (setq r 0)

     (while
       (and
         (setq p1 (getpoint "\nSpecify First Point <Done>: "))
         (setq p2 (getpoint "\nSpecify Next Point <Done>: " p1))
       )
       (vlax-put-property XLCells 'Item (setq r (1+ r)) 1 (rtos (distance p1 p2)))
     )
     (vlax-put-property XLApp 'Visible :vlax-true)
     
     (mapcar '_release (list XLCells XLApp)) (gc) (gc)
   )
   (princ "\n--> Could not interface with MSExcel.")
 )
 (princ)
)

Press Enter when finished picking points.

 

Hi...gave this a try. It seems to give paste only the "distance" as opposed to X, Y, Z?

 

Also I don't need the code to open an Excel sheet and paste the values, just to hold the values as copied so that I can paste them into an existing Excel sheet.

 

Apologies, could have been a bit more prescriptive in my original posts...

[SWfangirl]

Again I have found a hero in Lee Mac!! This will save me a ton of time on my projects! Thank You

[Lee Mac]

You're very welcome SWfangirl