Input data from CAD to an open excel

[Anonymous]

Hello guys!

I am need of your knowledge and expertise.

Basically I am nearing the end of my small project.

So my program lists the starting block and end block of a polyline and it's length.

And creates a CAD Table.

Now what I would like is, instead of creating a table in would transfer that data directly to an open Excel file in a particular cell chosen by the user.

Is this possible? and if it isn't.  I am open to any alternatives that may serve the same purpose.

Many thanks to those who would like to share their input 😊

[hosneyalaa]

For better understanding, and, maybe, get further help, please upload such sample.dwg

[devitg]

@Anonymous  and the XLS too

[Sea-Haven]

1 Did you do any Googling Autocad -> Excel so much code out there already, how to do what you want.

2 How good is your lisp skills ?

3 Getexcel.lsp has the answers.

[Anonymous]

1  I did but I did not find anything with the same intention as mine, they usually divert to another way.

2  Very basic knowledge only 😞

3 I see, if getexcel has the answers then I will have a closer look at it.

Thanks @Sea-Haven 

[Anonymous]

Hi @hosneyalaa see sample drawing with generated table 

[Anonymous]

Hi @devitg 

for the excel no specific xls needed I just want the user to select in excel where they want to input the data (even if it is a blank one)

[hosneyalaa]

HI

TEST THIS

(defun c:TESTrun (/ *error* sset1 qty counter acdoc insertionpoint tables len ent row hyplnk start end box1 box1name box2 box2name)
		(setq *error* xx:Error)
		(vl-load-com)
  		(setq acdoc (vla-get-ActiveDocument (vlax-get-acad-object)))
	
		;select a set of lines
		(setq sset1 (ssget'((0 . "LINE"))))
  		(setq qty (sslength sset1))
		(setq insertionpoint (getpoint "\nTable scale depend on annotation scale.\nSpecify table insert point: "))
		;selection filter
  		(setq tables (vla-addtable
			      (vla-get-modelspace acdoc)
			      (vlax-3d-point insertionpoint)
			      (+ 2 qty)
			      3
			      10
			      50
			    )
		)
  		(vla-settext tables 1 0 "Start")
  		(vla-settext tables 1 1 "End")
  		(vla-settext tables 1 2 "Length")
		(setq row 2)
		(setq counter 0)
		(print qty)
  		(while (< counter qty)
		  (setq ent (vlax-ename->vla-object (ssname sset1 counter))
		  	start (vlax-curve-getstartpoint ent)
			end (vlax-curve-getendpoint ent)
			len (vlax-curve-getDistatParam ent (vlax-curve-getEndParam ent))
		  )
		  (setq box1 (ssget "_X" (list (cons 0 "INSERT") (cons 10 start))))
		  (setq box1name (vla-get-effectivename (vlax-ename->vla-object (ssname box1 0))))
		  (setq box2 (ssget "_X" (list (cons 0 "INSERT") (cons 10 end))))
		  (setq box2name (vla-get-effectivename (vlax-ename->vla-object (ssname box2 0))))
                  
		  (vla-settext tables row 0 box1name)
		  (vla-settext tables row 1 box2name)
		  (vla-settext tables row 2 (rtos len 2 2))
		  (setq counter (1+ counter))
		  (setq row (1+ row))
		)

)

[Anonymous]

Thanks @hosneyalaa 

I see you saw my typo for box1---> box2 (I also noticed just recently)

and thanks for the rtos recommendation I forgot about that.

Basically now my problem is how to handle this data.

I don't want to create a CAD table anymore.

My Options are:

1. to input it to excel via getexcel
2. create a new csv file with the data
3. paste to clipboard the data so the user can paste to whatever cell he likes

I am very interested in a clipboard option I saw a code

from @CodeDing  here

This method is very convincing, my idea is to create a string that if I paste in excel would mimic the results of my table.

In this code he successfully segregated each text by a tab entry (moving to the next cell) so my only problem is how to code the "Enter" command in a strcat generated text

(defun c:C2E ( / _SetClipBoardText ss txt cnt len)
(vl-load-com)
;Function to set clipboard
  (defun _SetClipBoardText ( text / htmlfile result )
    ;;  Attribution: Reformatted version of
    ;;  post by XShrimp at theswamp.org.
    ;;  See http://tinyurl.com/2ngf4r.
    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'SetData
            "Text"
            text
        )
    )
    (vlax-release-object htmlfile)
    text
  );defun
;Get input from user. 4 dims, in order w/ window (ssget) capability
(setq ss nil cnt 1 txt "")
(while (not ss)
  (prompt (strcat "\nSelect Dimension " (itoa cnt) ": "))
  (if (setq ss (ssget '((0 . "DIMENSION"))))
    (if (= 1 (sslength ss))
      (progn
	(setq len (cdr (assoc 42 (entget (ssname ss 0)))))
	(setq txt (strcat txt (rtos len 4)))
	(if (/= 4 cnt) (setq ss nil txt (strcat txt "\t")))
	(setq cnt (1+ cnt) len nil)
      );progn
      (progn
	(setq ss nil)
	(prompt "\n...invalid. More than 1 object selected.")
      );progn
    );if
  ;else
    (prompt "\n...nothing selected")
  );if
);while
;If string, send to clipboard and send feedback to user
(if (< 0 (strlen txt))
  (progn
    (_SetClipBoardText txt)
    (prompt "\nText Successfully copied to clipboard.")
  );progn
    (prompt "\nFailure, no text to copy to clipboard...")
);if
(princ);finish quietly
);defun

[hosneyalaa · Accepted solution]

HI @Anonymous 

TEST 

(defun c:TESTrun (/ *error* sset1 qty counter acdoc insertionpoint tables len ent row hyplnk start end box1 box1name box2 box2name)
		(setq *error* xx:Error)
		(vl-load-com)
  		(setq acdoc (vla-get-ActiveDocument (vlax-get-acad-object)))
	
		;select a set of lines
	(IF	(setq sset1 (ssget'((0 . "LINE"))))
  (PROGN
  		(setq qty (sslength sset1))
                (setq LISLINE NIL)
                (setq file (open  (setq file2open(getfiled "Output file"
                (strcat (getvar "dwgprefix")
                (substr (getvar "DWGNAME") 1 (- (strlen (getvar "dwgname")) 4)))
                "CSV"     ;file type
                1)) "w"))
                (setq i 0 sep ";")    ;sep=separator
;;;		(setq insertionpoint (getpoint "\nTable scale depend on annotation scale.\nSpecify table insert point: "))
;;;		;selection filter
;;;  		(setq tables (vla-addtable
;;;			      (vla-get-modelspace acdoc)
;;;			      (vlax-3d-point insertionpoint)
;;;			      (+ 2 qty)
;;;			      3
;;;			      10
;;;			      50
;;;			    )
;;;		)
;;;  		(vla-settext tables 1 0 "Start")
;;;  		(vla-settext tables 1 1 "End")
;;;  		(vla-settext tables 1 2 "Length")
;;;		(setq row 2)
		(setq counter 0)
		(print qty)
  		(while (< counter qty)
		  (setq ent (vlax-ename->vla-object (ssname sset1 counter))
		  	start (vlax-curve-getstartpoint ent)
			end (vlax-curve-getendpoint ent)
			len (vlax-curve-getDistatParam ent (vlax-curve-getEndParam ent))
		  )
                  
		  (setq box1 (ssget "_X" (list (cons 0 "INSERT") (cons 10 start))))
		  (setq box1name (vla-get-effectivename (vlax-ename->vla-object (ssname box1 0))))
		  (setq box2 (ssget "_X" (list (cons 0 "INSERT") (cons 10 end))))
		  (setq box2name (vla-get-effectivename (vlax-ename->vla-object (ssname box2 0))))
;;;                  (setq LISLINE (CONS (LIST box1name box2name (rtos len 2 2)) LISLINE))
                  (write-line (strcat box1name sep box2name sep (rtos len 2 2)) file)
;;;		  (vla-settext tables row 0 box1name)
;;;		  (vla-settext tables row 1 box2name)
;;;		  (vla-settext tables row 2 (rtos len 2 2))
		  (setq counter (1+ counter))
;;;		  (setq row (1+ row))
		)

  (close file)
    (startapp "explorer" file2open);opin excel 
    ))
  (princ)

  
)

[Anonymous]

Thanks for the test code @hosneyalaa 

this was the resulting excel.

I'll figure out how to tab delimit the text so as to input in diff cells.

[Sea-Haven]

A work around write the text file, open using Autocad in notepad, Startapp can do Ctrl+a, Ctrl+c then go to Execl.

(command "start" "notepad D:\\acadtemp\\blocks.txt")

I suggest use the delimeter comma x,y,z etc 

You can answer that delimeter is tab comma : ; etc.