Excel to Scripts

This drawing of a flowmeter control cone represents about three years of my work some thirty years ago, 1971. Two hundred co ordinates were required and all had to be calculated using a slide rule!

Today the drawing and the calculations took less than 15 minutes. My how things have changed.

MS Excel is thought by some to be an accountants tool, which of course it is. Engineers however should think of Excel as the next stage up from their trusty calculator.

Please experiment with the graphics capabilities of excel and get used to making a series of calculations by dragging down a cell or cells with the mouse.

Next we need to get the results not in a graphical form but in AutoCAD script format.

In Excel you can use the Plus sign '+' to add numbers Eg. =A1 + A2.
What we require is Excel to add text.

The ampersand sign '&' is the sign for text concatenation, that is, adding two or more strings of text together.

Please enter the formula =A1&B1 in the cell D1.
You should get zero and zero added together which will be "00" in text.

Next modify the D1 cell to =A1&","&B1 that is; cell A1 added to the text "," added to cell B1 this will give us "0,0".
Drag down the 'D' column and bingo! we have a set of coordinates that can be copied to the windoze clipboard and pasted into either the AutoCAD command line or a text editor and saved as a script file.

=A1&","&B1 is entered in
cell D1 and copied down.

x-y-quadratic.scr Download the results of the spread sheet in a script file.

x-y-quadratic.txt View the results of the spread sheet in a text file.

Using Insert Command

Let's now have a think about other AutoCAD commands that could use this technique.

Imagine that during a survey a civil engineer identified certain features on the landscape that have to be inserted into an AutoCAD drawing.

Nowadays he will use a portable GPS instrument to get the map coordinates of each feature and could dump them into Excel similar to the example above. In another column he may place the feature name eg. Post, Pylon, Phone-mast etc. Each of these would be the name of an AutoCAD block or xref.

We need to modify the formula in column 'D' to make a script file that will insert the correct block at the relevant place in the map. After investigating the INSERT command we find that the dialogue is as follows:-


    INSERT filename x,y xscale yscale rotation
  
    Between each AutoCAD parameter is a space.
  
    Example:-
    
       INSERT post 120,550 1 1 0

The 1 1 0 provides for xscale = 1, yscale = 1, and rotation = 0.

Put the block name in cell C1 and modify cell D1 to :-


      ="INSERT "&C1&" "&A1&","&B1&" 1 1 0"
     **Note A2000/i/2/4 users may need to use -INSERT
  
 This says :-
 
   Take the text "INSERT " add to it the contents of cell C1 add to
   that a space " " add to that the x,y coordinates as before then
   add the fixed text string " 1 1 0"

Now drag down column 'D' Then copy column 'D' to the clipboard and paste it into a text editor file. Save this as a script file and run from in AutoCAD using the SCRIPT command.

	INSERT post 0,0 1 1 0
	INSERT post 1,1 1 1 0
	INSERT post 2,4 1 1 0
	INSERT post 3,9 1 1 0
	INSERT post 4,16 1 1 0
	INSERT post 5,25 1 1 0

="-insert post "&A1&","&B1&" 1 1 0" put in D1 and copied down.
Note blank space placed after post and before the 1 1 0.
Putting the block name in column C provides flexibility

Please remember that the block, in this case POST has to be either within the drawing or on the DOS path.

MS Excel to AutoCAD Script Files