Copy text from selected range of Excel and Paste each cell value on each click

[Anonymous]

Hello

I have a excel file containing 3 - columns

1. Name of Point 1

2. Name of Point 2

3. description of line between the points

Now I want to select multiple rows with 3 columns in excel. I want a program to allow clicking for position of each point and place 'point names' from column 1 and 2 at respective points and description (from column 3) at centre of the points.

Thank you very much

[Sea-Haven]

I asked the same question in one of the forums and did not get an answer, its about getting the property "selected range" from excel, VL lisp lets you read properties out of excel. Your after say A1:C23 so can do a row column read of cells in correct order. 

This is what needed but in VL

Worked it out a start I have not included the open excel etc so it looks for an application myxl.

; get current range selected in excel
; By Alanh Nov 2021

; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)


; thanks to Lee-mac for this defun
; 58 is Colon

(defun csv->lst ( str / pos )
(if (setq pos (vl-string-position 58 str))
    (cons (substr str 1 pos) (csv->lst (substr str (+ pos 2))))
    (list str)
    )
)


(defun getrangexl ( / lst UR CR RADD )

(setq lst '())
(setq UR (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))

(setq lst (csv->lst radd))

(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))

(setq st (columnrow st))
(setq end (columnrow end))

(princ st)
(princ "\n")
(princ end)

)

(getrangexl)

To run say in conjunction with getexcel.lsp

(alert "select rang in excel")
; go pick range excel
(getrangexl)
; row colum start
; row column end

[hosneyalaa]

Hi
I didn't understand your question well

Did you explain it better in a video?

[pbejse]

---

@Anonymous wrote:

Hello

I have a excel file containing 3 - columns

---

Would you settle with using CSV in place of Excel file?

(Defun c:Pt2pt ( / _DelTolst _trans _Text a p1 p2 csvFile pt opf lst fp sp)
(defun _DelTolst ( str m / pos x lst lst2)
	(if (setq pos (vl-string-position m str))
	   (cons (substr str 1 pos) (_DelTolst (substr str (+ pos 2)) m ))
	   (list str)
	   )
)
(defun _Text (str pt1 pt2 / p_ ang)
  (entmakex
              (list
                (cons 0 "TEXT")
                (cons 10 
                      (Setq p_  ( _trans  (polar
                                 (mapcar '(lambda (x y)
                                            (* 0.5 (+ x y))
                                          )
                                         pt1
                                         pt2
                                 )
                                 (+ (setq ang (angle  (_trans pt1)( _trans  pt2))) (* pi 1.5))
                                 (getvar "Textsize")
                               ) )
                      ) 
                )
                (cons 11 p_)
                (cons 40 (getvar "Textsize"))
                (Cons 50 
			(if (and
			(> ang (/ pi 2))
			(<= ang (* pi 1.5))
			)  (+ ang pi) ang ))
                '(72 . 4)
                '(73 . 3)
                (cons 1 str)                      
                )
              )
            )
(setq _trans (lambda (p)(trans p 1 0)))  
  
(if
  (setq csvFile (getfiled "Select data" (getvar 'dwgprefix) "csv" 16))
  (progn
    (setq opf (open csvFile "r"))
    (read-line  opf)
    (while (setq a (read-line  opf))
      (setq lst (cons (_DelTolst a 44) lst))
      )
    (close opf)
    (setq lst (reverse lst))
    (While (And (setq pt (Car lst))	  
		  (setq p1 (if
			     (eq (setq fp (car pt)) sp) p2
			     (Getpoint (strcat "\nPick point <" fp ">"))))
		   (setq p2 (Getpoint p1 (strcat "\nPick point <" (setq sp (cadr pt)) ">")))
		)
      	
	  (entmakex (list (cons 0 "LINE") (cons 10 p1) (cons 11 p2)))
      	  	(_Text fp p1 p1)
		(_Text sp p2 p2)
        	(_Text (Caddr pt) p1 p2)
      (setq lst (cdr lst)))
    )
  	)
   (princ)
    )

Command: pt2pt

Pick point <P1>
Pick point <P2>
Pick point <P3>
Pick point <P4>

HTH

[Sea-Haven]

This is not finished but shows how to retrieve a current selected range. Please note Alan Excel library is a work in progress and is based around another request. Running out of time today downloaded xls.

(defun c:wow ( / filename sheetname numcols numrows )

(if (not chkexcel)(load "Alan Excel library"))

(setq filename "D:\\acadtemp\\Book1.xlsx" sheetname "Sheet1")

(chkexcel filename)

(getrangexl)

(alert (strcat "You have selected " (rtos cnt 2 0) " cells"))

; work out the repeats
; (repeat
; (repeat

(setq numcols (+ (- (car end)(car st)) 1))
(setq numrows (+ (- (cadr end)(cadr st)) 1))

; now do the how many cols and rows
; need to use Number2Alpha to convert column number to a alpha column name.
; (setq cellname .......
(getcell cellname)
; )
; )
(princ)
)