Help with a lisp - Areas from closed polygons to current (opened) excel file

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

no one??

Well, if i come up with a solution (hopefully before 2019) i will post it here

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.

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"

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

thanks Bigal.

Im gonna have another look to that Getexel.lsp and see if i can get something useful for my cause from it

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

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...

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!