AutoCAD® and Excel - a match made in heaven?
There are many ways to input commands into AutoCAD®. You will be familiar with the command line as the main method for driving AutoCAD®, any you will most likely know that toolbars are usually just shortcuts to commands that are in fact sent to the command line. Understanding how AutoCAD® accepts input from the command line allows us to invent our own ways of inputting data, and one of the most useful ways is to copy and paste commands directly into the command line.
Copy and paste this list of commands into the command line:
LINE 0,0 30,45
LINE 30,45 60,77.9422863405995
LINE 60,77.9422863405995 90,90
LINE 90,90 120,77.9422863405995
LINE 120,77.9422863405995 150,45
LINE 150,45 180,1.10263360941776E-14
LINE 180,1.10263360941776E-14 210,-45
LINE 210,-45 240,-77.9422863405995
LINE 240,-77.9422863405995 270,-90
LINE 270,-90 300,-77.9422863405995
LINE 300,-77.9422863405995 330,-45
LINE 330,-45 360,-2.20526721883552E-14
You should now have a rather coarse looking sine wave which was drawn by a series of LINE commands. "What's the point of that" you say? Well the drawing of a sine wave is just an arbitrary example of copy and pasting commands directly into the command line. Where this concept really comes into it's own is when we use Excel to create the list that we copy. Here's how I created the list above:
The method for creating the coordinates is unimportant - what you should focus on is the formula for creating the command:
="LINE " & E3 & "," & F3 & " " & E4 & "," & F4 & " "
Using a bit of concatenation of cells with the '&' operator, we can make the content of cells represent a command in AutoCAD®, which we can then later copy and paste into AutoCAD®. Taking this further you can string a few commands together for each row of data, allowing you to achieve more complex tasks than drawing a simple line. So if you had a few coordinates for say manhole locations, and each had an associated ID number, you could quite easily create a formula to firstly input a circle at the correct location, and then insert some text at the same location with the ID number. If you have a block, use the INSERT command in your formula instead. The possibilities are endless. I'll never forget the time that I had to produce a tree survey based on a huge list of coordinates and other information in Excel. We'd budgeted for a fair amount of work, but with a bit of knowhow it was as easy as copy and paste.
I hope you found this tip useful, and if you did I would like to recommend you subscribe below. I've got loads more tips to share!
Comments
Aprinto
2010-12-07 15:36:37
Hi, I need a program or modul to connect Excel 2007 and AutoCAD® 2010 for non commercial purpose. I am not expert of VBA program. I had closed irregular polygon drawing, same hatch style, same layer, named poly1, poly2, poly3, ... poly-n.
In Excel, I had value for those polygon in table :
Shape, Coord_X, Coord_Y, Elev, Value, Color
Poly1 ? ? ? 0 White hatch (hidden or not print)
Poly2 ? ? ? 10 Red (value 1-10)
Poly3 ? ? ? 20 Green (value 11-20)
...I just want to recolor hatch object in their polygon relevant to their value in Excel. Polygon itself will not change their color. I don't know do I need hatch coordinates or not for this case.
Could you help me how to recolor this polygon hatch? All solutions will be appreciated. Thanks a lot,
Will
2010-12-08 16:27:29
Sorry, but I'm not 100% sure exactly what you're trying to achieve here. I gather that you have a set of data in Excel, and that it somehow relates to polygons, which I think exist in the form of hatches in AutoCAD®. Nonetheless, I will write a post after I finish this comment that shows how Excel can be an excellent (pun intended!) way of controlling AutoCAD®. Hopefully this will help, but feel free to ask for more clarification if you require it.
bluehill
2011-03-30 22:57:32
I used the same method(="point " & E3 & "," & F3 & " " & E4 & "," & F4 & " ") for plotting number of points and I thought it was working very well. But later I realized some of the points are missing. I also found out that it works in a new drawing for the first time but after that it keeps missing some random points. I could not figure out what is the problem. Please help!
Will
2011-03-31 11:05:54
Remember that a LINE requires 2 points and a POINT only one, so you'll have to tweak the formula a bit so that you end up with what you need:
LINE 0,0 5,6
This is a line from 0,0 to 5,6POINT 0,0
This is a point at 0,0
POINT 5,6
This is a point at 5,6 \So, the formula should be something like:
=”point ” & E3 & “,” & F3 & ” ”So you don't need the (& E4 & “,” & F4 & ” “) at the end (which is the second point of the LINE command)
Hope this helps!
bluehill
2011-03-31 16:06:17
No it doesn't help. For e.g.Following are the lines I copied from excel and pasted on autocad command line. Most of these are plotted while some are missing...!!! point 0,9083.64
point 8.04,9084
point 192.96,9096
point 223.68,9108
point 253.44,9120
point 1122.72,9132
point 1961.88,9144
point 2837.76,9156
point 3511.8,9168
point 4289.88,9180
point 5091.24,9192
point 5547.24,9196.8
Will
2011-03-31 17:43:16
Strange - when I copy and paste those commands into the AutoCAD® command line, I get 12 points (which is what you'd expect). I do have to copy and paste into notepad first and add a space at the end first, but if you're copying from Excel you've got (& " ") at the end of the formula so it should be ok. Perhaps send me the Excel file you're using?
Will
bluehill
2011-03-31 19:20:56
Ya, I added a space at the end in notepad too but when I paste it in autocad command, it gives me only 6 points. I also tried drawing a polyline using same points but without "point". For eg i type pl command first. when it asks for next point I paste all those poits but without "point"...and this time it draws the polyline but again some of the points are not there...
i expanded the command line. It says invalid point for the points that are not drawn....I do not see any reason for those points to be invalid because if i paaste the points individually they all are plotted!!!
One more thing, In my autocad when i press a space bar the last command is repeated (i don't know if it is default setting). So to plot above points, i tried with putting "point" on the first line and removing them from other lines buit i added space at the ends. Now it gives me only 10 points....!!!
By the way i'm using autocad 2011.
thanks
Will
2011-04-01 08:00:51
Hmm very strange - I copied and pasted the cells directly from the Excel file you sent me, and I got 12 points in my drawing. So, it must be something to do with your setup - very strange.
The way I would diagnose what the problem is would be to try copy/pasting 1 cell, then 2 cells, then 3... until you get a problem. Then press F2 and look back through the command line and try to see how AutoCAD® is interpretting what you've pasted.
Will
Haroon
2011-04-05 20:00:03
I want to know how I can chnage the dimensions of a box drawn on AutoCAD® using an excel sheet. I have no knowledge of VB or Macros but am proficient with excel.
Will
2011-04-08 08:11:26
Hmm this sounds like it will probably involve using an excel macro that creates the box and records the object ID of that entity in the excel spreadsheet. Then, it is easy to retrieve at a later date the precise entity that needs updating using the object ID from the spreadsheet. If you're drawing a box, then you'll most likely be dealing with a polyline. Farily simple to use, you've just got a list of four coordinates in an array, one for each corner. Retrieve the polyline and tweak the coords to update it.
Hope this helps! Will
Tony
2011-04-28 11:36:21
Hi Will, Thanks for the excellent tip. I have a list of individual points which need importing & have got as far as "point x,y" which plots on screen fine. How do I attach a point number to each individual point?
Will
2011-04-28 14:09:15
The way I would do it Tony is to add another command into the mix - the TEXT command. So create some formulas to end up with something like this:
"Point x,y TEXT x,y Text for that point"
You might have to tweak it a bit to get the number of spaces etc right, but that should do it. You might also want to +1 to the x and y for the TEXT so that it is offset a little from your points.
Hope this helps!
Fred B
2015-12-02 19:09:21
Hello Will, Reviving and old thread, hope you ares still listening. :) But this is relevent I beleive.
How can I get the cut and paste to work with the TEXT command which requires two Return characters after the text entry in order to return to the command prompt?
If I paste the following into the command line:
text 0,0 24 0 N22
text 10,10 24 0 N11I get the following onscreen result with the "N22" text aligned on 0,0.
N22
text 10,10 24 0 N11The second command is taken as a second line of text rather then a command!
Thanks,
Fred
Fred B
2015-12-03 13:07:15
Found a solution involving LISP. See posts of Dec 2nd, 2015 at: http://forums.autodesk.com/t5/autocad-2013-2014-2015-2016/circles-from-excel-to-autocad/m-p/5934371#M105439
If you have better option don't hesitate to pitch in. Thanks.