(Part 2 – Block swaps from Excel edits)
Autodesk’s Nathan Eliason hosted a webcast on Wednesday that focused on AutoCAD Electrical’s ability to use Excel to edit its drawing sets. I helped put the demo together and was responsible for real-time Q & A support while Nathan was “on stage”.
This posting is a brief summary of what Nathan covered in the mid-part of the hour-long webcast – using Excel to quickly revise a copied project. It might serve as a simple overview / tutorial of this AutoCAD Electrical feature.
Part 1 of this tutorial, here, demonstrated the following sequence:
- 1) Copy existing project and drawings to new names,
- 2) Export the project drawing data “To Spreadsheet”,
- 3) Open Excel spreadsheet and edit, edit, edit
- 4) Push the edited data back to the drawing set using the “From Spreadsheet” utility
So, in the example above that ended Part 1 of the tutorial showed Excel spreadsheet edits finding their way back to two different drawings in the active project drawing set.
How does this work?
So, how does this update work? We could have hundreds of drawings in our project and tens of thousands of components, terminals, and wire numbers in our project. How does this simple spreadsheet edit know what goes where?
It’s pretty simple. There are two key columns that need to appear somewhere in each sheet of the spreadsheet:
(FILENAME) – gives the drawing name of the target drawing
(HDL) – gives the AutoCAD “handle” value assigned to each block insert instance
Together these two values uniquely identify each block insert instance used in an AutoCAD Electrical project drawing set. And this is what the “From Spreadsheet” utility uses to uniquely match a row in the spreadsheet with a block insert instance found somewhere on one of the drawings in the overall project drawing set.
So, in the example shown above, the green highlighted row in the spreadsheet maps back to a block insert instance on drawing “2011_LINE202.dwg” with handle number “40B”.
Not all data columns can be written back to the drawing. For example, in the COMP table shown below, there is a column labeled “(REF)” that gives the calculated line reference or X-Y grid reference of the extracted component.
Manually forcing a different value into the “(REF)” field and then running update “From Spreadsheet” will NOT cause this component to move to some other line reference or grid reference on the drawing. That is beyond the scope of this tool. Likewise, changing the drawing name field value for a component will NOT trigger that component to move from one drawing to some other drawing. Again, beyond the scope of this tool.
So, of the 39 fields of the COMP sheet, five of them are not “updateable”. These are marked with column names enclosed in parenthesis. The other 34 fields will trigger updates on components on the drawings.
Block swap from Excel edits
Okay, looking at this COMP Excel sheet, something doesn’t seem right here…
…fields shown in parenthesis are supposed to be not update-able. But shouldn’t the symbol’s block name be frozen as well? Shouldn’t the field label “BLOCK” really be “(BLOCK)” in parenthesis???
Does this mean that I can change a block name in the spreadsheet from one name to some other name? Won’t AutoCAD consider that to be illegal?
For example, on the 5th row down in the sheet shown above I have a push-button tagged as “PB422″. The BLOCK column carries a value of “HPB11”. This is the symbol’s actual “block” name, extracted by AutoCAD Electrical and pushed into this row of the spreadsheet along with 38 other pieces of data related to it. According to AutoCAD Electrical’s symbol naming convention, this tells me that it is a N.O. parent push-button symbol.
Question: What happens if I edit the spreadsheet and change this block name from a push-button to a 2-position N.C. selector switch symbol, “HSS122” ???
Answer: The update “From Spreadsheet” tool will execute a “Block Swap” operation. It will take out the HPB11 symbol, find and pop in symbol HSS122, reconnect the wires, and push the attribute values back on to this new symbol ( ! ).
UPDATE: this swapped block can be either a local block on the drawing (i.e. already used elsewhere on the drawing or defined but not purged) or it can reference a library symbol “.dwg” file somewhere in the AutoCAD Electrical or Acad support path list. The swap function will search for the block and, if found, pull out the old block, pop in the new, adjust wire connections if the new block is smaller or wider than the original, and push the attributes back out to it.
So, what have we discovered here? Not only can this tool push edited attribute values back out to existing block inserts on any drawing in our project set, but we can even trigger it to swap out one type of block symbol for another! This is pretty cool.
UPDATE2: this is not native AutoCAD behavior, it is native AutoCAD Electrical behavior.