Excel selected range

[carmine.iannotta342739]

Good afternoon, i'm new in visual lisp programming, so i hope someone can help me.
Is it possible to get a list with the values of cells in a range selected by the user?

[Lee Mac]

This should help you:

http://web2.iadfw.net/terrycad/LISP/GetExcel.lsp

[carmine.iannotta342739]

Thank you Lee Mac, but in this code i need to specify the cell. Is there a way to get the cell selected in excel?

[irneb]

You need to look at the Excel ActiveX reference: http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

From there you should be able to browse to the relevant propery/object. E.g. from the Application object you get the ActiveCell property, which contains a Range object. This in turn either has an Address string or a Cells object (i.e. column number & row number) - whichever you need.

I preferred showing you how to get there, so you can get at other items as well.

[carmine.iannotta342739]

This is what i did until now:

(defun c:proex()
  (vl-load-com)								
  (setq documento (getfiled "esplora" "esempio" "xls"  13))		
  (setq excel (vlax-create-object "Excel.Application"))			
  (setq fogliolavoro (vlax-get-property excel 'Workbooks))		
  (setq attfoglio (vlax-invoke-method fogliolavoro 'Open documento))    
  (vla-put-visible excel :vlax-true)					
  (getint )  
  (setq selezione (vlax-get-property excel 'Selection))
  (print selezione)  
)

But when i use it he gives me something like:
#<VLA-OBJECT Range 0000000036f66178>

What's wrong?
I need to store the values ​​in the range in a list, how can I go on?

[irneb]

But when i use it he gives me something like:
#<VLA-OBJECT Range 0000000036f66178>

What's wrong?
I need to store the values ​​in the range in a list, how can I go on?

Nothings wrong at all. You would just want to go further.

You get the current selection inside Excel. That is a range of cells (i.e. something like B3 ... H16). Notice the returned value: #<VLA-OBJECT Range 0000000036f66178>

So you now have a reference to the Range Object. Thus you need to check how many cells are selected and step through each to find its value. Notice the Range Object's Properties: There's an Item and a Count property which you'd probably need to use to get hold of a list of "Cell" Objects - which are actually Range Objects in themselves - but addressing only one single cell.

If the range object's count property = 1, then you should be able to get the value contained inside the cell using the Range Object's Value property.

[irneb]

As an example I've modified your code a bit (marked in red):

(defun c:proex (/ documento excel fogliolavoro attfoglio selezione values-list row-list)
  (vl-load-com)
  (setq documento (getfiled "esplora" "esempio" "xls" 13))
  (setq excel (vlax-get-or-create-object "Excel.Application"))
  (setq fogliolavoro (vlax-get-property excel 'Workbooks))
  (setq attfoglio (vlax-invoke-method fogliolavoro 'Open documento))
  (vla-put-visible excel :vlax-true)
  (getint)
  (setq selezione (vlax-get-property excel 'Selection))
  (vlax-for row (vlax-get-property selezione 'Rows) ;Step through all the rows in the selection
    (setq row-list nil) ;Initialize the temporary list to hold the values in the current row
    (vlax-for cell (vlax-get-property row 'Columns) ;Step through all the cells in the row
      (setq row-list (cons (vlax-get cell 'Value) row-list)) ;Add the cell's value to the list
    )
    (setq values-list (cons (reverse row-list) values-list)) ;Add the row's values to the list
  )
  (vlax-release-object excel) ;Release the reference to Excel!!!! Important!
  (reverse values-list) ;Return the list of values to whatever calls this defun
)

Notice I've also localized the variables, this is a thing which I'd highly advise you do on all your routines.

And above all, release any ActiveX objects you've created through vlax-create-object / vlax-get-or-create-object. Else you could run out of RAM quite quickly, not to mention crash ACad. You might also want to close Excel before releasing the object.

[carmine.iannotta342739]

This is exactly what I need, thank irneb!

[irneb]

This is exactly what I need, thank irneb!

You're welcome

There's many ways of doing this, I'm not too sure how it would perform if you've got multiple ranges selected in excel - i.e. If you hold down the Ctrl key and select 2 or more areas. From my tests it seems to work fine for 2 separate portions on the same sheet, but ends up with nil values if you've selected across sheets.

BTW, why did your code use 13 in the getfiled call? I'd think 12 would be more "correct":

1 = Ask for new file name, if exists ask user if they want to overwrite. (I don't agree with this one)

+

4 = Allow other extensions than the one specified.

+

8 = Search for the file in the support paths if not specified as full path.

[fixo]

You might be want to look at to do it
by Excel way using Input function:
(from my oldies so sorry for bad code manner)

;; local defun
(defun RefSelection (/ *error* addr c2 c2 Excelapp Sel Sht r1 r2 Rng Vl Wbk)
  (vl-load-com)
  (defun *error*  (msg)
    (if
      (vl-position
	msg
	'("console break"
	  "Function cancelled"
	  "quit / exit abort"
	  )
	)
       (princ "Error!")
       (princ msg)
       )
            (vl-catch-all-apply
	'vlax-invoke-method
	(list Wbk "Close")
      )

	(vl-catch-all-apply
	  'vlax-invoke-method
	  (list ExcelApp "Quit")
	)

  (mapcar
    (function (lambda (x)(vl-catch-all-apply(function (lambda()
		(if (not (vlax-object-released-p x))
		  (progn
		  (vlax-release-object x)
		  (setq x nil))
		)
	      )
    )
			   )
		)
	      )
    (list Sel Sht Wbk ExcelApp)
  )  
  (gc)
  (gc) 
    (princ)
    )
  

(setq ExcelApp (vl-catch-all-apply
	       (function (lambda ()(vlax-get-or-create-object "Excel.Application")))))

(if (vl-catch-all-error-p
      (setq Wbk
	     (vl-catch-all-apply
	       (function (lambda ()
			   (vlax-get-property ExcelApp "ActiveWorkBook"))))))
  (progn
    (alert "Excel WorkBook Must Be Open Before!")
    (exit)
    (*error* nil)
    (princ)
    )
  )
(setq Sht
       (vl-catch-all-apply
	 (function (lambda ()
		     (vlax-get-property ExcelApp "ActiveSheet")))))

(vlax-put-property ExcelApp 'visible :vlax-true)

(vlax-put-property ExcelApp 'ScreenUpdating :vlax-true)

(vlax-put-property ExcelApp 'DisplayAlerts :vlax-false)

(if (not (vl-catch-all-error-p
	   (setq Rng
		  (vl-catch-all-apply
		    (function (lambda ()
				(vlax-variant-value
				  (vlax-invoke-method
				    (vlax-get-property Wbk 'Application)
				    'Inputbox
				    "Select a Range: "
				    "Range Selection Example"
				    nil
				    nil
				    nil
				    nil
				    nil
				    8))))))))
  (progn
    (vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)

    (setq r1 (vlax-get-property Rng 'row))
    (setq c1 (vlax-get-property Rng 'column))
    (setq r2 (vlax-get-property (vlax-get-property Rng 'rows) 'count))
    (setq c2 (vlax-get-property (vlax-get-property Rng 'columns) 'count))

    (setq addr (strcat (chr (+ 64 c1))
		       (itoa r1)
		       ":"
		       (chr (+ (ascii (chr (+ 64 c1))) (1- c2)))
		       (itoa (+ r1 (1- r2)))))

    (setq Rng (vlax-get-property sht 'Range addr))

    (vlax-invoke Rng 'Select)
    )
  )

(if Rng
  (progn
    (setq vl (mapcar (function (lambda (x)
				 (mapcar 'vlax-variant-value x)))
		     (vlax-safearray->list
		       (vlax-variant-value
			 (vlax-get-property Rng 'value2)))))
    (princ "\n")
	   (alert (vl-princ-to-string vl))
	   )
    (progn
      (alert "Select Excel Range Before!")
      (exit)
      (*error* nil)
      (princ)
      )

    )

(*error* nil)
)

;;Usage:
(defun C:Xss ()

(RefSelection)
(princ)
)
(princ "\nType Xss in the command line")
(princ)