Creating macro for transporting coordinates to excel/textfile

[Technick]

Hello,

 

in my work I use the autocad function ID to get coordinates for certain items in the drawing, then I copy them to a excel sheet. This is quite time consuming.

 

Is it possible to create a macro were I use a command similiar to the ID command and then get the coordinates automatically transferred to excel (or a textfile)?

Also having the option to label the coordinate would be great.

 

Example:

1. Using command macro

2. Pointing on a stair with coordinates X=10, Y=5

3. A window pops up asking for name, I write "Stair" and push ok.

4. Output in excel or textfile would be: Stair 10 5

 

Really appreciate any help I can get with creating this macro!

[BIGAL]

Some thing like this not tested for a start could be a lot more elaborate

 

(setq pt (getpoint "\nPick point"))
(setq xs (rtos (car pt) 2 3))
(setq ys (rtos (cadr pt)2 3))
(setq ans (getstring \n Enter lable")) 
(setq ans (strcat ans " " xs "   " ys))
(command "text" pt 90 ans)

 

Else use point and search here lots of stuff about points to excel.

[Technick]

Some thing like this not tested for a start could be a lot more elaborate

 

(setq pt (getpoint "\nPick point"))
(setq xs (rtos (car pt) 2 3))
(setq ys (rtos (cadr pt)2 3))
(setq ans (getstring \n Enter lable")) 
(setq ans (strcat ans " " xs "   " ys))
(command "text" pt 90 ans)

Else use point and search here lots of stuff about points to excel.

 

 

Thank you! I get an error at last command saying:

Requires valid numeric angle or second point.

; error: Function cancelled

 

Can you help with this?

[MSasu]

Seems that BIGAL had an imposed height in his text style and you have set 0.0 for that feature - thus the interpreter is expecting the input of label height. Fix the code as below, where 1.0 is the text height - adjust it to your needs:

(command "text" pt [color=magenta]1.0[/color] 90 ans)

[Technick]

Thanks, that worked!

 

I am now using the following macro:

(defun c:as ()
(setq pt (getpoint "\nPick point"))
(setq ans (getstring "\nEnter label: ")) 
(setq ans (strcat ans ))
(command "text" pt 1.0 0 ans)
)

I create the points in its own layer and turn off all the other layers. Then use the extraction tool to export the points.

(If anyone have a more clever way of doing it, please let me know)

 

Only problem I have now is using using the space when labeling (as this will execute a command). For now I'm solving this problem using underscore.

Example: Engine_200t

Edited April 17, 2012 by Technick
Added code tags

[MSasu]

You can force the GETSTRING function to accept spaces:

(setq ans (getstring [color=red]T[/color] "\nEnter label: "))

[Technick]

You just made my day! Thank you!

[MSasu]

Glad I could help you! You're welcome!

 

By the way, please edit your previous post to add code tags.

[BIGAL]

If happy to go this way you could use a block instead of a point using attributes & Fields you can copy a block with same description and the x,y co-ords will auto update this may be a lot easier than labelling each point and removes text problem.

[MSasu]

To call of TEXT command in AutoLISP - the code below will ensure compatibility with any text style, regardless of height (imposed or not):

(command "_TEXT" textPoint)
(if (= (cdr (assoc 40 (entget (tblobjname "STYLE" (getvar "TEXTSTYLE"))))) 0.0)
(command textHeight)
)
(command textAngle textLabel)

[Technick]

Thank you!

 

I now want to make the macro easier to use:

Is it possible to set the macro to create a new layer were it puts the text, and then revert to the previous layer used?

 

Example:

1. User initiate command

2. Macro creates layer called "Coordinates" or jump to this layer if it already exist

3. User input name of coordinate

4. Macro create the point and jumps back to previous layer

 

Appreciate any help I can get with creating this!

[MSasu]

2. This may help you to solve the layer:

(if (not (tblsearch "LAYER" "Coordinates"))
(entmake (list '(0 . "LAYER")
               '(100 . "AcDbSymbolTableRecord")
               '(100 . "AcDbLayerTableRecord")
               '(70 . 0)
               '(2 . "Coordinates")
               '(62 . 5)
               '(6 . "Continuous")
               '(290 . 0)
               '(370 . -3)))
)
(setvar "CLAYER" "Coordinates")

3. Check the GETPOINT function.

 

4. This can be one solution:

(setq OldLayer (getvar "CLAYER"))
...
(setvar "CLAYER" "Coordinates")
...
(setvar "CLAYER" OldLayer)

[Technick]

Beautiful!! Thanks!

 

Code used for macro is now:

 

(defun c:as ()
(setq OldLayer (getvar "CLAYER"))
(if (not (tblsearch "LAYER" "Coordinates"))
(entmake (list '(0 . "LAYER")
               '(100 . "AcDbSymbolTableRecord")
               '(100 . "AcDbLayerTableRecord")
               '(70 . 0)
               '(2 . "Coordinates")
               '(62 . 5)
               '(6 . "Continuous")
               '(290 . 0)
               '(370 . -3)))
)
(setvar "CLAYER" "Coordinates")
(setq pt (getpoint "\nPick point"))
(setq ans (getstring T "\nEnter label: "))
(setq ans (strcat ans ))
(command "text" pt 1.0 0 ans)
(setvar "CLAYER" OldLayer)
)

[MSasu]

I have made some modifications to your code and commented them. I suggest also to take a look to INITGET function if need to restrict the input for string.

 

(defun c:as( / OldLayer layerName pt ans )        ;localize variables to avoid conflicts
(setq layerName "Coordinates")                   ;use a variable since need in many places
(setq OldLayer (getvar "CLAYER"))
(if (and (setq pt (getpoint "\nPick point: "))   ;ensure valid user input
         (setq ans (getstring T "\nEnter label: ")))
 (progn                                          ;PROGN required to group statements
  (if (not (tblsearch "LAYER" layerName))        ;create new layer only if required
   (entmake (list '(0 . "LAYER")
                  '(100 . "AcDbSymbolTableRecord")
                  '(100 . "AcDbLayerTableRecord")
                  '(70 . 0)
                  (cons 2 layerName)
                  '(62 . 5)
                  '(6 . "Continuous")
                  '(290 . 0)
                  '(370 . -3)))
  )
  (setvar "CLAYER" layerName)
  ;(setq ans (strcat ans ))                      ;this is obsolete !!!
  (command "_text" pt 1.0 0 ans)                 ;underscore ensure compatibility
  (setvar "CLAYER" OldLayer)
 )
)
(princ)                                          ;exit the routine quietly
)

[Technick]

Nice! Thank you!

 

This macro makes the job much quicker, but I see it takes some time extracting data with Data Extraction Tool.

 

Method of working is now:

 

1. Create all the points with AS macro

2. Go to layer manager, freeze and hide every layer except "Coordinates" layer

3. Go to Data Extraction tool

4. Edit existing data extraction and pick a template used earlier

5. Select objects in current drawing (objects in layer "coordinates")

6. Settings already predefined in template, so just hit next till it exports to excel sheet

 

Is it possible to take process from 2-6 in a macro?

[MSasu]

You're welcome!

How about writing your own extraction routine based on this example?

(if (setq setTexts (ssget "_X" '((0 . "TEXT") (8 . "Coordinates"))))
(progn
 (setq index 0)
 (repeat (sslength setTexts)
  (setq itemText (entget (ssname setTexts index)))
  (princ (strcat "\nPoint " (cdr (assoc 1 itemText))
                 "\t" (rtos (cadr   (assoc 10 itemText)) 2 5)
                 "\t" (rtos (caddr  (assoc 10 itemText)) 2 5)
                 "\t" (rtos (cadddr (assoc 10 itemText)) 2 5)))
  (setq index (1+ index))
 )
)
)

[Technick]

Great idea! Using this routine I tried to export the data from autocad to excel with copy/paste from command bar, and then used "Convert text to columns" for separating the text and the coordinates in excel. Unfortunately this didnt work out good, since the delimiters only took "space", not "tab", which lead to excel seperating the text too. I tried searching the forums for a way of extracting the text and coordinates in separate colums like the data extraction tool is doing, but could not find a way of doing it (as you may have noticed I'm not a programming expert ). Do you have any idea how to make it export it to Excel?

[MSasu]

I just given you the above as example; should adjust it to suit your needs. Instead of AutoCAD's text screen dump the report into a text file (see OPEN, WRITE-LINE, CLOSE).

May check the CSV format for an alternative.

[Technick]

I tried looking into the file: http://web2.airmail.net/terrycad/LISP/GetExcel.lsp

but I don't even get the OpenExcel function to work:( Also how to program routine to put text, x and y in separate columns is a little difficult for a newbie like me I will try searching more to see if I can get this to work

[MSasu]

As suggested above, try to use a CSV file; this is automatically recognised by Excel and can use later Save As inside Excel to convert it to XLS/XLSX format.