topic Re: Input data from CAD to an open excel in Visual LISP, AutoLISP and General Customization Forum

Input data from CAD to an open excel

Anonymous — Thu, 24 Sep 2020 08:40:50 GMT

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 😊

Re: Input data from CAD to an open excel

hosneyalaa — Thu, 24 Sep 2020 21:33:41 GMT

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

Re: Input data from CAD to an open excel

devitg — Fri, 25 Sep 2020 00:03:23 GMT

@Anonymous and the XLS too

Re: Input data from CAD to an open excel

Sea-Haven — Sat, 26 Sep 2020 06:04:14 GMT

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.

Re: Input data from CAD to an open excel

Anonymous — Mon, 28 Sep 2020 01:49:14 GMT

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

Re: Input data from CAD to an open excel

Anonymous — Mon, 28 Sep 2020 01:50:31 GMT

Hi @hosneyalaa see sample drawing with generated table

Re: Input data from CAD to an open excel

Anonymous — Mon, 28 Sep 2020 01:51:29 GMT

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)

Re: Input data from CAD to an open excel

hosneyalaa — Mon, 28 Sep 2020 07:20:03 GMT

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

Re: Input data from CAD to an open excel

Anonymous — Mon, 28 Sep 2020 07:45:48 GMT

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:

to input it to excel via getexcel
create a new csv file with the data
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

Re: Input data from CAD to an open excel

hosneyalaa — Mon, 28 Sep 2020 08:24:01 GMT

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

Re: Input data from CAD to an open excel

Anonymous — Mon, 28 Sep 2020 09:21:02 GMT

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.

Re: Input data from CAD to an open excel

Sea-Haven — Tue, 29 Sep 2020 04:03:22 GMT

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.