LISP routine to copy "_Dist" output

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

DataExtraction?

Have you tried _DATAEXTRACTION?

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.

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.

Minor Update ^^.

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.

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.

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

"\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?

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.

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.

Suuuuuure. [ATTACH]27165[/ATTACH]

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

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

Probably a bit too much.

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

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

You're very welcome SWfangirl