pmxcad Posted June 4, 2018 Share Posted June 4, 2018 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 Quote Link to comment Share on other sites More sharing options...
Lee Mac Posted June 4, 2018 Share Posted June 4, 2018 The values are already held by the variable "vl" - I would suggest manipulating this list rather than assigning each value to a separate variable. Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 5, 2018 Author Share Posted June 5, 2018 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 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted June 6, 2018 Share Posted June 6, 2018 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" Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 7, 2018 Author Share Posted June 7, 2018 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. Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 7, 2018 Share Posted June 7, 2018 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)) Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 7, 2018 Author Share Posted June 7, 2018 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? Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 7, 2018 Share Posted June 7, 2018 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. Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 8, 2018 Author Share Posted June 8, 2018 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? Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 8, 2018 Share Posted June 8, 2018 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. ) ) Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 8, 2018 Author Share Posted June 8, 2018 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.. Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 8, 2018 Share Posted June 8, 2018 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? Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 8, 2018 Author Share Posted June 8, 2018 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? Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 8, 2018 Share Posted June 8, 2018 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. Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 8, 2018 Author Share Posted June 8, 2018 now only values between quotation marks Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 8, 2018 Share Posted June 8, 2018 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? Quote Link to comment Share on other sites More sharing options...
pmxcad Posted June 8, 2018 Author Share Posted June 8, 2018 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. Quote Link to comment Share on other sites More sharing options...
Tharwat Posted June 8, 2018 Share Posted June 8, 2018 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. Quote Link to comment Share on other sites More sharing options...
hanhphuc Posted June 9, 2018 Share Posted June 9, 2018 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 Quote Link to comment Share on other sites More sharing options...
hanhphuc Posted June 9, 2018 Share Posted June 9, 2018 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.