Get Excel cell data into variables

Hello all,

I found a lisp on a forum that can bring selected excel cells to Autocad. This comes in the direction of what I would like to have. But now the outcome comes with an alert box.

Is it possible to get the cell values in variables such as setq column-a, setq column-b etc etc up to column t. Only one row is selected.

;; fixo 20-11-2011
;; 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
			    )))))))
 (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)

thank you very much in advance

PmxCAD

The values are already held by the variable "vl" - I would suggest manipulating this list rather than assigning each value to a separate variable.

Hi Lee,

thank you for your response. I'm not so keen with lisp. With the values in a variable I can do something with it and can also use it in other lisp routines. And max 1 line with 10 columns. So yes, ...... 10 variables.

PmxCAD

If you look at a row column response so for example Colum A and row 4 has a value of 22

(setq col "A"
row 4
val "22")


(set (read (strcat COL (rtos row 2 0)) ) val)

type !A4 on command line

"22"

Hi BIGAL,

I've been playing around for an hour or so, but I can not get it together.

I would not know how to apply or modify your code to get the values of the selected row of columns in a variable.

Hi,

I did not test the code but hopefully its correct and you would have the a list of the selected range ( rows & columns ) assigned to the variable lst

   (setq c2 (vlax-get-property (vlax-get-property Rng 'columns) 'count))
[color="blue"](setq cels (vlax-get-property Rng 'cells)
     cols c2
)
(repeat r2
 (repeat cols
   (setq grp (cons (vlax-variant-value
                     (vlax-get-property
                       (vlax-variant-value
                         (vlax-get-property cels 'item r2 cols)
                       )
                       'text
                     )
                   )
                   grp
             )
   )
   (setq cols (1- cols))
 )
 (setq lst  (cons grp lst)
       grp  nil
       r2   (1- r2)
       cols c2
 )
)[/color]

   (setq addr (strcat (chr (+ 64 c1))

Tharwat,

Gives an error:

error: malformed list on input.

;================================================

;; fixo 20-11-2011
;; 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
                   )))))))
 (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 cels (vlax-get-property Rng 'cells)
     cols c2
)
(repeat r2
 (repeat cols
   (setq grp (cons (vlax-variant-value
                     (vlax-get-property
                       (vlax-variant-value
                         (vlax-get-property cels 'item r2 cols)
                       )
                       'text
                     )
                   )
                   grp
             )
   )
   (setq cols (1- cols))
 )
 (setq lst  (cons grp lst)
       grp  nil
       r2   (1- r2)
       cols c2
 )
)

   (setq addr (strcat (chr (+ 64 c1))

and it closes excel, can this be disabled?

I just added the needed codes among the other codes from your earlier posted routine so you need to gather them all as one and not just mine.

Ok sorry Tharwat,

had replaced the last part with your code instead of adding it.

Now no more error.

Am now working to get it out of the list. The first item is successful with (caar lst).

But with the following columns / values from the lst it does not work yet. Tried with (nth 0 lst).

This gives everything from the lst and (nth 1 lst) gives NIL. So I think I have to play around with (nth 0 lst) to get the other values of the lst.

Do you have sugestions where to look for it?

Hi,

You need to use foreach or mapcar to cycle through each list in the variable lst but if you are new to AutoLISP just use the foreach function as follows:.

(foreach x lst
 (;; here you would have the car list of the variable lst then with the next loop of this foreach 
  ;; function you would get the second list as the first one and so on.
 )
) 

Tharwat,

Now I am completely lost.

I have tried a number of things, but It does not work.

So I'm on the wrong track.

I have tried this:

(foreach n lst (setq a (1 2 3 4 5)))

(foreach x lst
 (setq a1)
 (setq b1)
 (setq c1)
)

(foreach lst '(1 2 3 4) (print n))

And a few other things from the internet.

Yes, a lot of free time, but very educational..

No worries at all.

Are you after assigning the ranged values to variables that shown in the final alert message or wanting all rows and columns as I have posted the codes earlier for that purpose?

Tharwat,

i like them in a varable like;

(setq col-A); value column A

(setq col-B); value column B

(setq col-C); value column C

etc etc

In excel I only select one row.

Then there is the problem that, after lisp, excel is closed.

Can that be turned off?

I got what you wanted but that is entirely inefficient way of coding and AutoLISP is created for such tasks so please use the lists then you would know by time is that it is the best way to go with then you can use / play with the list as best as you want to.

Just ignore my codes and add replace the following codes with the alert message to see the outcome on the command line on your AutoCAD after that.

Replace this:

 (alert (vl-princ-to-string vl))

With this:

(foreach x vl (print x))

And run the program once again.

Just bear with me to show you what you are looking for with many variables is really incorrect once you have tens or hundreds of variables as you may have now with your task.

Let me know what you have printed on the command line in AutoCAD.

now only values between quotation marks

now only values between quotation marks

For instance, like this?

("a" "b")
("c" "d")
("e" "f")

Can I know what are you planning to do with the list to allow me to propose a working method for you?

yes, like that.

In brief.

I want to use it for my work, but for security reasons I work from a network that is not connected with the outside world, so no internet access. So I can not give you examples only a description. We have drawings with buildings. We color the spaces and put a text into them. I created a lisp requesting to select a closed polyline (on xref). This is brought up with ncopy and used for accering (solid with transparency) then you have to select the space number (on xref) to insert a block. This is in short.

What I want to make now is to skip a few steps with this lisp.

We receive from the client an excel list with building number, room number, color of the accering etc.

And I do not know if it is possible to search for space number (on xref) make a selection set of these and select the close-closed polyline (on xref) and bring it up.

Now we have to look in the excel list for this data.

After this another lisp will run. This looks at the size of the polyline and looks at its dimensions and then determines the size of the paper size that is needed (1:50). it creates a new drawing zoomed in (layout 1:50) on that space.

We draw on room level.

Do you have any questions?

I used google translate for this long story, so there may be some weird things in it.

I see that you are in need of a custom program and its not just a matter of getting rows or columns' values from an Excel file, so if you want me to write this program for you with a few fees then just contact me.

Have a nice weekend.

Tharwat,

Now I am completely lost.

I have tried a number of things, but It does not work.

So I'm on the wrong track.

I have tried this:

 

(foreach [color="red"]n[/color] lst (setq a (1 2 3 4 5)))
[color="green"]; n temporary variable name , didn't use it? so it doesn't work[/color]

 

(foreach x lst
 (setq a1 )
 (setq b1)
 (setq c1)
)
[color="green"]; error: too few arguments in SETQ [/color]


(foreach [color="red"]x[/color] lst
 (setq a1 [color="red"]x[/color])
 (setq b1 [color="red"]x[/color])
 (setq c1 [color="red"]x[/color])
)
[color="green"]; i.e: a1, b1 ,bc now hold 1st ,2nd, 3rd valus of lst [/color]

 

(foreach  lst '(1 2 3 4) (print n))
[color="green"];missing variable name[/color]

(foreach  [color="red"]n[/color] (setq lst '(1 2 3 4) )
(print [color="red"]n[/color]))
[color="green"];prints out at text screen / command line[/color]

 

And a few other things from the internet.

Yes, a lot of free time, but very educational..

FWIW, maybe Tharwat overlooked this

I got what you wanted but that is entirely inefficient way of coding and AutoLISP is created for such tasks so please use the lists then you would know by time is that it is the best way to go with then you can use / play with the list as best as you want to.

i do support Tharwat's statement - LISP = LISt Process

Tharwat,

i like them in a varable like;

(setq col-A); value column A

(setq col-B); value column B

(setq col-C); value column C

etc etc

 

In excel I only select one row.

Then there is the problem that, after lisp, excel is closed.

Can that be turned off?

setting variable is not effective & messy as Tharwat mentioned.

no sure this suggest alternative suits OP ?

(defun [color="blue"]xls:alpha->nth[/color]  (str / l) [color="green"]; nth zero base[/color]
 (setq	l (reverse (mapcar '(lambda (x) (1+ (rem (- x 65) 26)))
	  (vl-string->list
	    (strcase (cond ((/= (type str) 'STR) (vl-princ-to-string str))
			   (str)
			   ) 
		     ) 
	    )
	  )
  )		
)
 (1- (+ (car l)(apply '+ (mapcar '(lambda(x)(* x 26)) (cdr l) ) )))
 )

[color="green"];example [/color]
_$ (xls:alpha->nth "A" ) 
[color="green"];0 = 1st column is 'A', zero base i.e: to be used with [color="blue"]nth[/color]  [/color]
_$ (xls:alpha->nth 'az ) 
[color="green"];51[/color]
_$ (mapcar 'xls:alpha->nth '(aB "ba" c "Aa" af zz))
[color="green"];(27 52 2 26 31 701)[/color]

test row & column function

[color="green"];test for row & column [/color]
(defun foo ( col row lst )
[color="green"]; col = alpha str , row = index integer which [color="red"]1[/color] as nth base[/color]
(nth ([color="blue"]xls:alpha->nth[/color] col) (nth (1- (fix row) ) lst))
)

Example :

(setq [b]lst[/b] '((A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF)[color="green"] ;Row1[/color]
    (65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 A65 A66 A67 A68 A69 A70)  [color="green"];Row2[/color]
    ("A" "B" "C" "D" "E" "F" "G" "H" "I" "J" "K" "L" "M" "N" "O" "P" "Q" "R" "S" "T" "U" "V" "W" "X" "Y" "Z" "AA" "AB" "AC" "AD" "AE" "AF") [color="green"];Row3[/color]
           (1 2 3 4 5 6 7 8 9 0 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32) [color="green"];Row4[/color]
          )
     )

(foo 'a 2 lst) [color="green"];A2[/color]
[color="green"]; 65[/color]
(foo 'j 1 lst)[color="green"] ;J1[/color]
[color="green"]; J[/color]
(foo "ac" 2 lst) [color="green"];AC2[/color]
[color="green"]; A67[/color]
(foo 'z 3 lst) [color="green"];Z3[/color]
[color="green"]; "Z"[/color]
(foo "w" 4 lst) [color="green"];W4[/color]
[color="green"]; 23[/color]
(foo 'zz 4 lst) [color="green"];ZZ4[/color]
[color="green"]; nil[/color]

my apology if interrupt this thread out of topic

Have a nice weekend