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.
