Overview of AutoCAD Electrical’s Excel Spreadsheet –> PLC I/O Generator Tool.
NOTE: 100+ other AutoCAD Electrical utilities indexed here.
Autodesk’s Dustin Clark hosted a webcast this morning on this “open source” tool in AutoCAD Electrical. I helped put the demo together and was responsible for the real-time Q & A support while Dustin was on stage.
Well, Dustin’s presentation generated a huge wave of feedback and kept me busy well past the conclusion of the formal presentation time responding to texted questions. So much so that I feel compelled to reproduce a number of the webcast slides and include detailed answers to the questions that they raised.
First of all, this tool in AutoCAD Electrical deals with complete I/O modules with the devices and wiring that connect up to each I/O point shown right there on the full module’s drawing. It is not set up to operate in the other popular mode… that is, to sprinkle discrete I/O points throughout the control design and then cross-reference each back to some master copy of the I/O module. Though this is possible in AcadE, it’s not the method that this “Excel –> Drawing generator tool” is designed to automate.
Dustin mentioned that often the first step you take in tackling a new controls design project is to collect a list of the project’s input and output requirements.
And, if you use an Excel spreadsheet to help arrange your project’s I/O list, then there may be a good chance that you can feed this to AutoCAD Electrical’s “Spreadsheet –> PLC I/O” tool and generate a first cut of your project’s I/O schematics.
Note: this AutoCAD Electrical Spreadsheet –> PLC I/O tool expects to work with Excel 97 – 2003 “.xls” format (not “.xlsx”). It can also work with an Access “.mdb” file format or with an ASCII text “.csv” file format.
Dustin’s first demo – starting very simply
The flow of the webcast demos was to start with a bare-bones I/O list spreadsheet and examine the generated I/O drawing set. Then, with each subsequent demo, add more design detail to the spreadsheet and see how that maps to each new version of the generated drawing set.
Here in Dustin’s first demo, project size suggests that we’ll need to allocate four I/O modules for our new project: two 16-point / 120 VAC input modules, one 16 point 120 VAC output, and one 8-point 24VDC output module. We initially specify AB series 1794 Flex I/O.
Since these modules are supported in the out-of-the-box AutoCAD Electrical product, we simply enter the catalog part numbers in the first column as shown here. Make sure that there is a blank row inserted between each module.
Note: Dustin’s demos use the default spreadsheet column format. This means the I/O module’s part number is in the first column, the I/O point addressing in the 5th column (E), and I/O point descriptions begin in the 7th column (G). But this is pretty much user-adjustable in the utility’s user interface dialog (not covered in the webcast but fully covered in the on-line help!).
So, we save our Excel file above. Then launch the PLC I/O Utility from the AutoCAD Electrical ribbon button shown here. Accepting all the defaults, two new I/O drawing generate showing four modules appropriately spec’d to match the AB 1794 series of Flex I/O.
Note: for earlier versions of AutoCAD Electrical, launch the tool from the pull-down or from the tool-bar pull-down as shown below.
Dustin’s second demo – Expanding spreadsheet to define description text for each I/O point
This next level of detail:
- We add in a new row for each I/O point of each module
- For each I/O point we enter up to five chunks of description text for that I/O point.
Note: One thing to remember, we MUST keep a blank row inserted between the end of one module and the beginning of the next.
Now, re-running the tool, we get a new set of drawings with the individual I/O point descriptions included (below).
Note: each time this tool runs, it creates a fresh set of I/O drawings. It does NOT incrementally update an existing set of I/O drawings with edits you make to the existing spreadsheet. There are other AutoCAD Electrical tools that can do this to a certain extent, but were not covered in this webcast.
Third demo – adding more detail to Spreadsheet – connected in-line devices
Now Dustin adds a bunch of new columns in groups of four, one set for each potential in-line device position (room for nine sets per I/O point). This is the AutoCAD Electrical default – four columns per I/O device. But you can expand this as required. See the on-line help.
Note: probably the hardest part of this is figuring out what device “block name” to enter into the “*BLK” column for each device. For example, if you want a N.O. pushbutton symbol to pop in for a certain I/O point, you need to enter a block name “HPB11”. If you want a N.C pushbutton, you’d need to enter “HPB12”. A little list of common symbol block names will probably be very useful here ( ! ).
With this extra in-line connected device information included in our Excel spreadsheet, newly generated I/O drawings are much more complete:
Here is a close-up of the top part of the first module’s connections. Each group of four columns in the spreadsheet drives what goes in to a connected-component column in the generated drawing.
Note: for an output module’s I/O point, the columns are reversed. The left-most group of four columns is the set farthest from the output point. The 9th group would be the set closest to the module I/O point.
Here’s a close-up below. This is a detailed look at just one of the columns and how the data from the spreadsheet both drives what component is popped in (the “S” column here) and the annotation that then goes on to the component (the 1st, 2nd, and last columns).
Note: the “|” pipe symbol in any of the *DESC columns is a delimiter that breaks the single line of text across consecutive “DESC*” attributes on the target symbol. In the example “CYCLE|START” below, the text “CYCLE” will try to go to the DESC1 attribute on symbol HPB11 and “START” will try to go to the symbol’s DESC2 attribute. If another pipe symbol delimiter, the remainder would try to go to the symbol’s attribute DESC3.
Dustin’s fourth demo – piggy-backing additional attribute/value combos into the spreadsheet
The setup for this one was this: Let’s say we know the pressure switch settings that need to be documented for these two switches. It would be nice to just enter these values in the spreadsheet and have AutoCAD Electrical pull them across to each switch symbol’s “RATING1” attribute value.
Just add a piggy-backed attribute name/value to an existing column’s value. Don’t try this in the “block name” column. Use one of the other three columns reserved for the inserted component’s position. The format is “;” semicolon followed by attribute name, “=“ sign, and then the attribute value. You can piggy-back more than one extra attribute in a given Excel cell. Just delimit with a “;” semicolon character between each.
If the column’s cell is blank to start with, begin with a semi-colon character. For example, if you want to use a blank “LOC” location cell to add some piggy-backed attribute values, begin with a “;” as first character.
Note: the target block instance needs to have the target attribute tag on it. If not present then the piggy-backed entry in your spreadsheet will have no effect. In the example above, the N.O. pressure switch symbol HPS11 carries a blank RATING1 attribute. So the above works great.
Fifth demo – inserting a “circuit” as an in-line wired device instead of a block insert symbol
This was Dustin’s setup: Here’s where it gets more interesting. Not everything hooked up to a PLC I/O point is a single wire in and single wire out. Sometimes the device might have multiple connections or there might be a couple devices wires in parallel and tied to a single I/O point. Here’s an example of a pushbutton in parallel with a 2-position selector switch. Maybe we use this over and over in our designs and we always want to tie this combination in to one I/O point.
Use AutoCAD Electrical to make a small sub-circuit consisting of an AutoCAD Electrical pushbutton symbol and a selector switch symbol and then just WBLOCK it out, let’s say to file name JOG_CIRC.dwg.
Now, in our spreadsheets, now and in the future, whenever we want to pop in this standard sub-circuit, we just enter it as shown here. Make sure that the WBLOCKed sub-circuit file name has the leading “*” character and that the circuit file is in an AutoCAD search path. In this example we also add a “SPACER” keyword to pull an extra gap in the module to clear up enough room for this sub-circuit to pop in.
Note: it is important that the circuit be saved in a folder that is included in AutoCAD Electrical’s search path or the general ACAD search path. If this is not possible, you can enter the full path into the Excel cell. IMPORTANT: if you encode the full path, you MUST use either double back-slashes or single forward slashes in the path/file name!
Running the AutoCAD Electrical spreadsheet to PLC I/O utility and referencing our modified spreadsheet… ! There’s our sub-circuit inserted into the wide gap in the PLC I/O module.
Note: circuit component tags will auto-tag if parent symbols, but no provision in the spreadsheet to push specific attribute values to specific parts of the inserted circuit. Doing this would require some customization of the program. Do-able, but outside the scope of Dustin’s webcast.
Configuring this Excel tool to your spreadsheet format and your drawing layout
There is an extensive UI (partly shown below) that allows you to reconfigure this tool to better match up with your spreadsheet layout and generate drawings based on your standards. The webcast just used the AutoCAD Electrical “demo” defaults.
Delving into this part of the tool would have far exceeded the hour we had for the webcast. But all of this is pretty much covered in the AutoCAD Electrical on-line documentation.
Webcast posting for rerun viewing…
This webcast should be available at this link, or go to the main site here:
This Spreadsheet to PLC I/O generator tool is supplied in basic AutoLISP file format. If you’re adventurous, it begs to be customized. The file name is wdio.lsp. You can find it on your AutoCAD Electrical installation by typing this at the command prompt:
(findfile “wdio.lsp”) [Enter]
There is also a wdio.dcl dialog definition file in the same folder. That’s it. You modify this lisp file (make backup first!) and you can alter what the Spreadsheet –> PLC I/O tool will do.
Here are four blog postings that give step-by-step examples:
Here are some other recent AutoCAD Electrical “Tutorial” blog postings:
Afterword / Advertisement
If your company sees potential in a custom application and it exceeds your internal resources to bring it to pass, please consider N8 Consultants.
Update: someone asked for the sample Excel files used in this webcast. I think these are the ones… download here