xxfaxx Posted February 7, 2018 Share Posted February 7, 2018 Hello As you may already know i am new to the forum, but not so new to autocad (been using it almost for 10 years), but i am really new to this autolisp world (and vba for that matter), i am planning in learning a lot of it by myself to automate some work we do. Anyway, i found a lisp that the user "Fixo" (a good soul that used to help noobs like me) wrote about 8 years now. The lisp in question does what i need but with one little problem. It opens a new excel file everytime it is used. I need to get the areas of closed selected polygons to the current and opened excel file, to the cell i have clicked. I hope i am making myself as clear as posible. This is the link to the autolisp file i am refering to (AreasToXL_V3 or V5) if it helps http://www.cadtutor.net/forum/showthread.php?41722-Closed-polyline-Area-values-to-Excel&p=697435#post697435 I could see that the piece of code that opens excel is this one (setq aexc (vlax-get-object "Excel.Application") xbks (vlax-get-property aexc "Workbooks") abks (vlax-invoke-method xbks "Add") xshs (vlax-get-property abks "Sheets") asht (vlax-get-property xshs "Item" 1) xcel (vlax-get-property asht "Cells") ) I am attaching (or at least trying to, because it is my first time attaching something ) two example files. a DWG and an excel one showing the result needed. Thank you very much in advance to anyone who could help at least a little Example.dwg example.xlsx Quote Link to comment Share on other sites More sharing options...
xxfaxx Posted April 17, 2018 Author Share Posted April 17, 2018 no one?? Well, if i come up with a solution (hopefully before 2019) i will post it here Quote Link to comment Share on other sites More sharing options...
BIGAL Posted April 18, 2018 Share Posted April 18, 2018 (edited) If you have a look at Getexel.lsp it has lots of functions including working with an open excel. The code above uses the Add function. Rather than checking is it already open. I had a quick play but need to do a bit more on is an excel currently open. Edited April 18, 2018 by BIGAL Quote Link to comment Share on other sites More sharing options...
hanhphuc Posted April 18, 2018 Share Posted April 18, 2018 (edited) no one??Well, if i come up with a solution (hopefully before 2019) i will post it here hi, just noticed your fisrt post was 7th Feb 2018 08:37 pm IMO no respond perhaps related to excel vba macro automation?? In the your Excel sheet cell : "click in this cell and use the command again i would like the information to be here" it is something doable by vba but this LISP forum for discussion not request vba freebies But if you try some codes get stuck as for help, i'm sure you would have quick respond from active members here to solve your problems i prefer to output plain text in "*.csv" format, it is simple by AutoLisp open & write-line functions it can be view in Excel as well & its easier & faster than ActiveX method. unless you need some formatted output then you can use Excel API as BIGAL suggested. But here's quick alternative solution by Lisp method.. [color="green"];limitations ;Each lot must be a closed LWPolyline ;Area text must contain "*[b] m2[/b]" ;Lot number must be numeric ;Assume each lot has ONLY (lot number text & area text) pairs [color="red"];all labels must be inside polygon[/color] [/color] (alert "Type \"[b]TEST[/b]\" in command line (without quote) to invoke.. ") (defun c:test ( / l i s ss ls lst en ob fn f wp ) ;hanhphuc [color="red"]18[/color].04.2018 (and (setq ss (ssget "X" (list '(0 . "LWPOLYLINE") '(-4 . "=") '(70 . 1)[color="red"] (cons 410 (getvar 'ctab))[/color]))) [color="green"];<- edit[/color] ) (repeat (setq i (sslength ss)) (setq en (ssname ss (setq i (1- i))) wp (mapcar ''((x)(trans (cdr x) en 1)) (vl-remove-if ''((x) (/= (car x) 10)) (entget en))) lst (cons (if (setq s (ssget "WP" wp )) (setq ls (vl-remove nil (mapcar ''((x) (setq ob (vlax-ename->vla-object x)) (if (vlax-property-available-p ob 'TextString) (vlax-get ob 'TextString) ) ) (acet-ss-to-list s) ) ) ls (if [color="red"](and (=(length ls)2)[/color] (wcmatch (car ls) "* m2*")[color="red"])[/color] (reverse ls) ls ) ls (subst (strcat (car ls) ",") (car ls) ls) ls [color="red"](if (wcmatch(cadr ls) "*,*")[/color] (subst (vl-string-subst "" "," (cadr ls)) (cadr ls) ls) ls) ) ) lst ) ) ) (setq fn (strcat (getvar 'dwgprefix) "Area_example.csv")) (setq f (open fn "a")) (foreach x (cons "\nlot Number,Area" (vl-sort (vl-remove nil lst) ''((a b) (< (atoi (car a)) (atoi (car b))))) ) (write-line (if (listp x) (apply 'strcat x) x ) f ) ) (progn (if f (close f) ) (command "startapp" "notepad" fn) [color="green"];(command "start" fn) ;if default Excel to open *csv [/color] ) ) (princ) ) (vl-load-com) (C:TEST) p/s: if you are using AC2016, how about command: DATAEXTRACTION? but not ac2007 Edited April 18, 2018 by hanhphuc wcmatch , comment , date , 410 ctab , length Quote Link to comment Share on other sites More sharing options...
xxfaxx Posted April 19, 2018 Author Share Posted April 19, 2018 thanks Bigal. Im gonna have another look to that Getexel.lsp and see if i can get something useful for my cause from it Quote Link to comment Share on other sites More sharing options...
xxfaxx Posted April 19, 2018 Author Share Posted April 19, 2018 thanks for the tips hanhphuc And about the vba macro, we already have a vba macro that does what i am trying to do in .lsp format. BUT, i believe in lisp format it can be enhanced much further. Or perhaps i should give vba macro a chance. I dont know. as i said i am learning and i chose to learn autolisp first and then vba. i will try some codes again as soon as i get a little break from work (perhaps next week). The first code i will try will be the one you typed Why do i need the excel file opened and why dont we use dataextraction? because we do this extraction job THOUSANDS of times per month and dataextraction doesn´t give us what we really need A little automatization (a little more than the one we have from our vba macro) could help us get the job done faster and with no error Thank you again i will post my results when i get them Quote Link to comment Share on other sites More sharing options...
hanhphuc Posted April 20, 2018 Share Posted April 20, 2018 thanks for the tips hanhphucAnd about the vba macro, we already have a vba macro that does what i am trying to do in .lsp format... no worries, better than nothing. p/s: when open the "Example.xls" file, i received the security warning but it was nothing macro in the vba project. my guess is excel macro to interface autocad, maybe. good luck coding! 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.