Managing CAD Standards with Excel

We started working with a single file.  Then we moved to applying a script to multiple files with information pulled in from the operating system.  Now we’ll discuss managing CAD standards information in Excel.

Any text information can be dropped into Excel, so an obvious place to pull info is the command line in AutoCAD itself.  Layer information, styles, and dimensions can be collected from AutoCAD, and placed in Excel, where they are easily viewed and adjusted.  Then, using the strategies shown earlier, that information can be returned to the CAD files, or applied to new files.

It is like working on a 1000 piece puzzle.  It is easiest to pull out a bunch of similar pieces (all the edges or all the bright orange pieces) and work on them separately.  Once you have that chunk put together you can return it to the main puzzle.

Layers from AutoCAD

Many commands allow you to list properties information:  (-LAYER) layers, (DIMSTYLE) dimension styles, (LIST) properties, and (STATUS) status just to name a few.  We’ll focus on the layers.  Open a file that has your standard layers in it, and list the layers.

Here’s the command sequence -layer ? *
The text window should open.

Notice how the information comes out in a mostly tabulated form: just like a spreadsheet.  You can copy the text from here and paste it into Excel.

At this point there are three main problems with the transferred information:

   1. The information is in one single column.
   2. The conversion from text to columns isn’t properly delimited.
   3. There is some information that may cause data type errors in excel.

These are all fairly easy to deal with, but issues “B” and “C” may not be apparent till you’ve already dealt with issue “A.”  An illustration may come in handy.  Here’s what you could end up with if you just dive in.

   1. Excel assumes that the dash should be considered as a minus sign and turns it into a function
   2. An extra space in front of “Layer” pushes it over by an extra column
   3. The word “(white)” is redundant.  The space before it pushes the column over to the right.
   4. The error message for #1
   5. Spaces within names of layers are read as indications of new columns

If you’ve come to this point, you could go through and clean up the information line by line and waste hours.  But, in this case, an ounce of prevention is worth a pound of cure.  You can do a little bit of work up front and save yourself a lot of heartache in the end.

First, be sure that none of your layer names contain spaces.

Second remove all the named colors.  Find and replace will make this happen very quickly.  As an example, you can trade (red) for blank.

Then do the same for, (white), (yellow), (cyan), (blue), (magenta) and (green).

 Third, convert your column of text into a table.  Select your column and choose “text to columns” from the data tab.

A wizard pops up that will take you through the steps.  Here are screen shots of my preferences.

 

Now that’s a fine looking table!  You can adjust it as you like.  You may want to remove the blank rows and the “Press enter to continue” rows.  You may want to add descriptions, colors, and titles.  It is now a document that you can print out and give to all your AutoCAD users.  You can make it as pretty as you like

But what happens if you’d like to add some additional layers?  Or change some of the information in your file?  It would be a shame to have to start with the CAD information, convert it and clean it every single time.  The better thing to do is to turn this file into a script that will create all your layers automatically.

Excel Layers to AutoCAD

We have all the layer information.  We simply need to figure out what inputs are required for the LAYER command, and then put our information into the proper locations.  The script should look something like this:

LAYER

MAKE #1name COLOR #1color #1name LTYPE #1ltype #1name

MAKE #2name COLOR #2color #2name LTYPE #2ltype #2name

MAKE #3name COLOR #3color #3name LTYPE #3ltype #3name

…

SET

0

Where #name, #color, #ltype, and #p come from the columns we created earlier.

We want to keep our Layer Standards list as nice as possible for the CAD users, so the script should be generated on a different sheet.  We’ll have to reference across sheets, but that isn’t too difficult.

Once you have a full row, concatenate your script, add spaces, then autofill.

Copy that column and paste it into a script.  You’ll need to open with the LAYER command and close with a couple extra returns to conclude the LAYER command.  And that’s it.  I’ve placed a copy of the spreadsheet and the resulting script on Google docs here:
spreadsheet https://docs.google.com/templates?q=GeneralLayers_01&sort=hottest&view=author

And here:
script https://docs.google.com/templates?q=Layer_script&sort=hottest&view=public

Epilogue:

Through this series of blog posts, I’ve shown how Excel can be a powerful tool for developing AutoCAD scripts.  There are of course other ways of accomplishing the same tasks.  There are commercial tools specifically created to write scripts.  AutoDesk has even created an “Action Recorder” to automate script creation.  If you wanted something faster more powerful, you could use AutoLISP or VBA programming languages to accomplish the same things.

The beauty of this method is that it relies on software that is ubiquitous and very little additional specialized knowledge.  These methods can be applied to other automation problems.  It is a more general solution.

Best of all, this is a stepping stone to getting into some more advanced code writing.  The first step into the world of programming is understanding that computers execute a series of instructions in order.  Writing any program is simply creating a template for a series of actions.

Someday I may take it to the next step with AutoIT3, but for now I plan to get back to biking stuff.

First things first – Creating a basic script

As I mentioned previously, a script is simply a list of commands executed sequentially.  All you have to do in Excel is create a list of commands.  Then you can paste these commands into the command line of AutoCAD and they will execute.

Here’s an example of a script that creates a simple rectangle:

Rec

0,0

1,1

To test it out, you can copy the text and paste it into the command line.

A quick reminder here, you need to be aware of how many spaces and returns are included when you run scripts.  Too few and your last command won't be concluded (just hit enter in ACAD) too many and you'll start another command (just hit ESC).

Pretty simple, no?

Here is the same rectangle in a table

Rec

0,0

2,2

Test this one as well.   Copy and paste into the command line.

That’s the basic idea. 

Now, it might not look like much, but now that you are working in Excel, you can use all the tools that come along with Excel.  You can use functions and references to move your data around or change it however you like.

AutoCAD 2010 Faux Review

First the Link:
AutoCAD 2010 tips and tricks video
http://adskmedia.com/autocad_2010/

Now the rant:
From my experience, AutoCAD spent many years (R12-2007) in a rut. The interface remained basically unchanged (same commands new images), while new, fragile, features were added with every release. The features often seemed half-baked and languished in an unfinished state for many (sometimes all) subsequent releases.

In the last three releases, the interface has received a major overhaul. I find many of the changes fascinating, and refreshing. But it seems that the dust hasn't settled on the interface. It continues to change in each of the three previous releases. This is a reversal from the many years when the interface remained basically static, but not necessarily a better scenario. To be productive, you really need to know where all your tools are going to be.

The interface has changed, but has the underlying problem been improved? Are the new features now more stable than the ones of previous years? What about the big new features of AutoCADs past. Has there been any improvement? I guess it isn't as much of a selling point to say, "Remember all those broken features we gave you five years ago? Well, we finally fixed them."

That being said, I should fully disclose: I haven't tried the new release. My previous work computer struggled to manage the interface adjustments in 2009. My home computer is a Mac. So, while the software has upgraded, my hardware hasn't.

In spite of my complaints (it is mostly just sour grapes), I appreciate the power and change in focus of the new features. The additions of parameters and constraints to geometry in this release along with improvements to the 3D modeling interface and tools over the course of the last four releases makes the full version of AutoCAD begin to come into its own as a true design tool. It is no longer CAD drafting that tries to emulate hand drafting conventions, but a completely new variety of drafting where lines respond to relationships to other lines.

AutoCAD on the Mac Redux

I may get my wish. AutoCAD on the Mac survey today. Revit Tomorrow?

From - http://worldcadaccess.typepad.com/

AutoDesk Assistance Program

If you are like me, you really relied on your employer to provide the tools of your trade. Now that you are unemployed you are in a real bind. You want to keep your skills up or learn new programs, but you don't have any money to spare.

Well, you could visit Pirate Bay, WarezRUs, or some other purveyor of viruses, worms, and cracks.

Or you can finally go legit (if only for a year) through AutoDesk's Assistance Program:
http://students6.autodesk.com/?nd=assistance_home

Now if only I could run Revit on my pre-Intel Mac!