Scriptsheets.com

AutoCAD command script programming

The Scriptsheets Add-In for MS-Excel is a great way to automate CAD drawings using any version of AutoCAD (Full or LT) and linking the power of Excel calculations with AutoCAD command scripts!

Take home!

Download: Scriptsheets.zip (702 kB)

If you are satisfied with the product and start saving time and money you could consider making a donation and support future development efforts!

Some tips

  • Readme.pdf
    After you UnZip all files you should open the Readme.pdf which shows you how to install the Scriptsheets.xlam file!
  • Auditing toolbar
    Microsoft Excel provides tools that help you track down data in your worksheets, just open the Auditing toolbar. You then can trace the precedents (the cells that provide data to a specific cell) or you can trace the dependents (the cells that depend on the value in a specific cell).
  • Create names to represent cells
    If you build many formulas which depend on lots of cells throughout your workbook, you should create names for these cells.
    That way you can use the name in formulas which makes them easier to understand.
  • Change security level for macro virus protection to medium
    MS Excel uses security levels for macro virus protection.
    If the security level is set to high then macros are automaticlly disabled when a workbook is opened!
    Each scriptsheet workbook contains additional worksheet functions which are implemented as VBA macro’s.

Drawing Setup Sample

Download: Drawingsetup.zip (33 kB)

This sample scriptsheet template automates the insertion of a drawing border and title block. The sheet makes use of named cell-ranges to store data and spreadsheet functions to look it up again.

This is an excelent sheet to study the basics of programming using the Scriptsheets Add-In and it also get you started drawing in AutoCAD aswell.

Survey data sample

Download: Survey.zip (21 kB)

This sample scriptsheet holds a named cell range which could hold various rows of XYZ coordinates.  For each row in the range a point command is written to the scriptfile. Standard worksheets functions are used to get the verying number of rows and to lookup the XYZ coordinates.

This sheet is interesting because it shows how to use a FOR/NEXT loop to pharse the rows in a named cell range.

Questions and answers

  1. What version of Excel do I need?
    You can install the Scriptsheets.xlam add-in with Microsoft Excel 2007 and newer.
  2. Can I use the Scriptsheets Add-In in a 64bit version of Office?
    Yes .xlam add-ins can be loaded in 32bits and 64bit Office version.
  3. #NAME? values show up in my sheet and scriptfile?
    Each scriptsheet workbook contains additional worksheet functions which are implemented as VBA macro’s.
    MS Excel uses security levels for macro virus protection. If the security level is set to high then macros are automaticlly disabled when a workbook is opened!
    Instead of the result of a perticular function cells which will show #NAME? you should change the security level for macro virus protection to medium.
  4. How do I make a block using a script file?
    Using a script file to select the objects that you created is the problem. However when you first open an empty drawing and than draw you block-entities, save the file and close it, you can later insert the file as a block.

    expert 2
    save mydrawing.dwg
    close
    new .
    _circle 20,20 15
    expert 2
    save circle.dwg
    close
    open mydrawing.dwg
    _insert circle=circle.dwg 0,0 1 1 0

  5. DTEXT command brings script to a halt?
    Using DTEXT in a script has problems and is a known AutoCAD defect logged against DID 107799 – DTEXT IS NOT SCRIPT COMPATIBLE. You can always use TEXT instead as shown in the sample script below. The blank lines are written to a script file when you use a semicolon “;” in the scriptsheet.

    _text _justify _mc 0,0 2.5 0
    First dtext line
    <blank line>
    <blank line>
    Second dtext line

  6. Is it possible use a VBA macro to write a scriptfile?
    Yes you can invoke the writescript method of the Scriptsheet VBA project; following these steps:

    • In MS-Excel press Alt+F11 to start VBA environment
    • In the Tools menu choose References
    • Click the Browse button and add a reference the ScriptsheetsVBAProject
    • Paste the following macro into a module

      Sub WriteAutoCADScript()
      ‘Run writescript method

      ScriptsheetsVBAProject.Writescript
      End Sub

    • Select worksheet containing the script program and run the WriteAutoCADScript macro to write a script.

How it works

Writing command scripts

Instead of writing AutoCAD commands directly in a text file you type them into the right part of a spreadsheet, always starting in colum M.
Each row will become a line in the actual script file and will only be written to the file if the preceding value in column L is something else then FALSE.

Combining spreadsheet calculations

The left part of the scriptsheet is free to calculate values of your liking in practice mainly X and Y coördinates, angles or distances. Which of course are referenced in the cells where you write AutoCAD commands.
There is also a parameter-sheet in the workbook which is meant to specify input values.
The blackbord-sheet -in the midle of the parameter-sheet and one or more scriptsheets- is a sheet to make common calculations. Usefull when you have more than one scriptsheet in the workbook and don’t want to make the same calculations a second time.

Adding program flow

The Scriptsheets Add In will expose a new command in the Add-In tab of the Excel Ribbon.
This command walks down the active spreadsheet – one row at a time – and reads all text in the right part of the spreadsheet begining in column M.
When it finds the word START it will open an empty scriptfile and start processing what it reads until it finds the word STOP.
Other than just AutoCAD commands -which are directly written to the script file- rows can contain program flow statements like GOTO and RETURN forcing the Add In to make a jump and continue processing at an other row.
Also the statements FOR and NEXT can be used to forcing the group of rows between them to be processed more than once.

Additional functions

; (semicolon)

If you specify a semicolon in a cell at the end or in between cells with AutoCAD commands you force a newline in the scriptfile. You sometimes need this to exit AutoCAD commands or explicitly end the input of text values.

/* comment */

All data in cells that start with /* will be skiped and interpreted as comment

=POINT(X, Y)

Pairs up the X and Y coördinate and returns a text which is allways valid in a AutoCAD scriptfile.
The X and Y values are seperated by a komma and the decimal seperator will allways show up as a dot. Regardles of the Regional Settings of your Windows system!

X is the X coördinate of a point
Y is the Y coördinate of a point

=NUMBER(N)

Returns a text which is allways valid in a AutoCAD scriptfile. Again the the decimal seperator will allways show up as a dot. Regardles of the Regional Settings of your Windows system!

N is the number to be returnd as a text

=ANGLE(X1, Y1, X2, Y2)

Returns an angle in radians between two points

X1 is the X coördinate of the first point
Y1 is the Y coördinate of the first point
X2 is the X coördinate of the second point
Y2 is the Y coördinate of the second point

=DISTANCE(X1, Y1, X2, Y2)

Returns the distance between between two points

X1 is the X coördinate of the first point
Y1 is the Y coördinate of the first point
X2 is the X coördinate of the second point
Y2 is the Y coördinate of the second point

=ROTATE(X, Y, a, Output)

Rotates a point around 0,0 (UCS origin) and returns the X or Y value

X is the X coördinate of the point
Y is the Y coördinate of the point
a is the angle in radians, positive values will result in CCW (counter clockwise) rotation
Output is either 1 or 0 resulting in returning the the X or the Y coördinate of the rotated point

Program flow

START

START statement in column M. When this statement is processed an empty scriptfile is opened and from that moment on all rows containing data other than statements or commentsis interpreted as AutoCAD command syntax and is written into the opened scriptfile.

STOP

STOP statement in column M to close the scriptfile and end the processing of rows. The rows between the START and a STOP buildt up the the main program.

GOTO <row>

GOTO statement in column M forcing processing of rows to continue on the row specified by the <row> argument. You use this to jump out of your main program to a subroutine.
All values in the cells in columns A to K and on the same row as the GOTO statement are copied to the corresponding cells of the row where you jump to.
<row> the actual number of the row which will be processed next. This value needs to be in the cell directly next to the GOTO statement (column N)!

RETURN

RETURN statement in column M forcing processing of rows to continue with the next row directly below the row which stated the last GOTO. You typicly use this to end a subroutine and jump back into the main program.

FOR <> IS <start> TO <end> STEP <step>

FOR repeats (loops) a group of rows a specified number of times.
<> cell used by the FOR statement as a counter. (cool to reference in your calculations!)
<start> this is the inital value of <>
<end> this is the final value of <>
<step> this is the amount <> is changed each time trough the loop.

NEXT

NEXT statement in column M forcing processing of rows to jump back and continue with the row which stated the last FOR. (You can nest FOR NEXT loops 16 times).

Stack operators

PUSH

PUSH statement in column M which saves all values in the cells in columns A to K on the same row as the PUSH statement on a memory stack.

PEEK

PEEK statement in column M which writes values -saved on top of the memory stack- in the cells in columns A to K on the same row as the PEEK statement.

POP

POP statement in column M to remove the saved values -by the last PUSH statement- from the top of the memory stack.