Save, Save, Save! Setting up a Save after Solution in ANSYS Mechanical

NOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!

Is this the reaction you have when you come in on Monday morning, and realize that another Windows update has, once again, rebooted your PC before you had a chance to save the 30-hour run that should have finished over the weekend? There a Workbench setting that can help relieve some of that stress.

The “Save Project After Solution” option will save the entire project as soon as the solution has finished. So when your model runs for 30 hours over the weekend, it gets saved before a Windows update shuts everything down.  These settings are persistent, so once you’ve changed them to ‘Yes’, then you are all set for next time. You just need to make sure that you change them for each ANSYS version if you have more than one installed.

Now on to my next blog… “How to recover a run if you forgot to change the settings above.” (Grumble Grumble!)

Quick Tips for Stratasys’ new Nylon 12CF Material

One of the newest materials available for the Stratasys Fortus 450 users (other machines could have this capability at a later date) is the Nylon 12CF. Nylon 12CF is a Carbon Fiber filled Nylon 12 filament thermoplastic. The carbon fiber is chopped fibers that are 150 microns long. This is Stratasys’ highest strength and stiffness to weight ratio for any of their materials to date as shown below. 
Often times, when Stratasys is getting close to releasing a new material, they will allow certain users to be a beta test site. One beta user was Ashley Guy who is the owner of Utah Trikes, which is located in Payson, Utah. He is having so much success with this material that he is making production parts with it. Watch this video to hear more from Ashley and to see some of his 3D printed parts.

Talking with Ashley, he has helped us with understanding some of the tips and tricks to get better results from printing with this material. One change that he highly recommends is to adjust the air gap between raster’s to -.004”. This will force more material between the raster’s so there won’t be as many noticeable air gaps. Here is a visual representation of the air gap difference using Stratasys software Insight:

The end goal at Utah Trikes is to produce production parts with this material, so by adjusting the air gap, the appearance of the parts look close to injection mold quality after the parts have been run through a tumbler. Some key things that I really like about this material is that the support material is soluble and easily removed using PADT’s own support cleaning apparatus (SCA Tank) that aid with the support removal. After the support has been removed, they are placed in a tumbling machine to smooth the surfaces of the part with different media within the tumbling machine. Any post process drilling or installing of helicoil inserts or adding bushings to the part is done manually.

Jerry Feldmiller of Orbital ATK, who also did a beta test of this material at his site in Chandler, Arizona, mentions these 3 tips:

  1. Nylon12 CF defaults to “Use model material for Support”. 90% of the time I uncheck this option.
  2. I use stabilizing walls and large thin parts to anchor the part to the build sheet and prevent peal up.
  3. Use seam control set to Align to Nearest.

Jerry also supplied his Nylon 12CF Tensile Test that he performed for this new material as shown below. He mentions that the Tensile Strength is 8-15 ksi depending on X-Y orientation.
~5 ksi in Z-axis, slightly lower than expected.

This part is used to clamp a rubber tube which replace the old ball valve design at ATK. Ball valves are easily contaminated and have to be replaced. After two design iterations, the tool is functioning.

Jerry also follows a guide that Stratasys offers for running this material. If you would like a copy of this guide, please email me your info and I will send it to you. My email is James.barker@padtinc.com

Now onto Stratasys and the pointers that they have for this material. First, make sure the orientation of the part is built in its strongest orientation. Nylon materials have the best layer-to-layer bond when comparing them against the other thermoplastics that Stratasys offers.

Whenever you print with the Nylon materials (Nylon 6, 12, and 12CF), it is advised to print the sacrificial tower so that any loose strands of material are collected in the sacrificial tower instead of being seen on the 3D printed part. You also want to make sure that these materials are all stored in a cool and dry area. Moisture is the filaments worst enemy, so by storing the material properly, this will help tremendously with quality builds.

It is also recommended for parts larger than 3 inches in height to swap the support material for model material when possible. Since the support material has a different shrink factor than the model material, it is advised to print with model material where permitted. This will also speed your build time up as the machine will not have to switch back and forth between model and support material. We have seen some customers shave 5+ hours off 20 hour builds by doing this.

This best practice paper is the quick tips and tricks for this Nylon 12CF material from our users of this material. The Stratasys guide goes into a little more detail on other recommendations when printing with this material that I would like to email to you. Please email me with your info.

Let us know if this material is of interest to you and if you would like us to print a sample part for testing purposes.

Phoenix Business Journal: 6 tips for conducting a technical meeting over the Internet

pbj-phoenix-business-journal-logoOnline meeting are great.  Sharing your work in real time with others makes a huge difference. In “6 tips for conducting a technical meeting over the Internet” we share advice on how to make those online meeting even more productive.

Support Design and Removal for 3D Printed ULTEM-9085 (Case Study: Intake Manifold)

ULTEM-9085 is one of my favorite materials to 3D-print: one of the reasons is it is a high performance polymer that can and has been used for end part manufacturing (see my blog post about ULTEM in functional aerospace parts), but the other is because it is a demanding material to print, in ways that ABS, Polycarbonate and even Nylon are not. What makes it demanding is primarily that ULTEM supports are not soluble and need to be removed mechanically. An additional challenge comes from the fact that the support is best removed when the part is at a high temperature (175-195 C), which requires the use of gloves and reduces the user’s dexterity. For complex geometries with internal channels, this is particularly challenging and occasionally results in an inability to print a certain part in ULTEM-9085, which runs contrary to the design freedom this technology otherwise enables.

In this post, I accumulate what I have learned through working (and failing) on many an ULTEM-9085 job, as well as through discussions with other users, and share this here in terms of design and process guidelines. To demonstrate these guidelines, I use a recent geometry that we printed for the Arizona State University’s (ASU) SAE team for an engine intake manifold. These guidelines apply to the Stratasys Fortus platform (for Fused Deposition Modeling, or FDM) using the Insight software that accompanies these tools. The screen shots are from Insight 10.6, and a Fortus 400 was used to print the parts shown.

Summary of Guidelines:

  1. Orient the part to eliminate supports in regions where you cannot remove them
  2. Use the box support style
  3. Optimize parameter settings (support angle, contour width, layer thickness)
  4. Remove the supports as soon as the part comes out of the build chamber
  5. Other observations: the interface of separation

1. Part Orientation

The single most important factor in simplifying support removal is part orientation. Most users of the FDM process know that part orientation determines the amount of support material consumed and also impacts the time to build the part. When working with ULTEM-9085, the additional challenge is that it is possible to design in supports that cannot be removed and will require you to scrap the job. This is especially true of internal features. While the automatic orientation feature in Insight allows you to minimize supports, it does not account for the difficulty of removing them. Thus when you are dealing with internal features, you may need to manually orient your part such that the internal features are aligned as close to the vertical as possible, and above the support angle (to be covered later).

As shown in Figure 1, for the intake manifold, I oriented the internal pipe structure close to the vertical and had to iterate a few times and verify that I had no support in the hard-to-reach areas. While I did have supports internally, they were limited to areas that were easy to access.

Figure 1. Engine intake manifold, to be printed out of ULTEM-9085
Figure 1. Engine intake manifold, to be printed out of ULTEM-9085
Figure 2. Part orientation to avoid any internal supports
Figure 2. Part orientation to avoid any internal supports in inaccessible regions

2. Box Supports

In a recent software upgrade, Insight added the ability to create box supports. The support structures consist of adjacent boxes instead of a continuous raster, which has the effect of allowing for easier separation of the support, though does slow down the build time. In my experience this support strategy does help with removal – the one parameter to consider here is the “Perforations” setting, though the default values were used for this part. The perforation is a layer of model material that is inserted into the support to make for easier breaking off of the support material. All cleavage surfaces in Fig. 3 are at perforation edges and you can see the building like construction with each floor distinguished by a layer of model material. When you have supports in hard to access regions, consider increasing the interval height so as to ensure you get separation at the model-support interface on the part before it occurs within the support on a perforation layer.

Figure Box Supports
Figure 3. Box Supports after removal from an ULTEM-9085 part

3. Optimize Process Parameters

While orientation will have the most significant impact on the support you need, another variable to be aware of is the “Self-Support Angle” parameter. This angle is measured from the horizontal, and represents the minimum angle of the part wall that will be built without supports. As a result, to reduce support requirements, you want this number to be as low as possible so that a greater volume of the part can be self-supported. Stratasys recommends default values, but these scale as a function of the contour width, and layer thickness, as shown in Fig. 4. The values bottom out at 40 degrees for the 0.013″ layer thickness and 43 degrees for the 0.010″ layer thickness. Thus, all other things being equal, you will be able to reduce the support needed by choosing a 0.013″ layer thickness and a 0.026″ or larger contour width. Note that both of these will impact your ability to resolve thin walls and fine features, so ensure you scan through all the tool-paths to validate that the geometry is accurately filled in.

Figure 4. Graph showing how the default values of the self-support angle vary as a function of contour width for the two layer thickness options available for ULTEM. Lower the angle, less the support needed.

4. Remove Supports Immediately

Supports are best removed when the model-support interface is hot. The best time to do this is right after you remove the parts from the print chamber, which is held at 195 C for ULTEM-9085. Ensure you have safety glasses on, work with thermal gloves and have a plier handy to pull out the support. In theory the parts can be re-heated again (175 C is a reasonable value for the oven), but Stratasys suggests that each re-heat cycle actually strengthens the interface, making it harder to remove. As a result, the best time to remove the supports is immediately out of the printer. Figure 5 shows the results of support removal for the intake manifold parts, including the build sheet.

Figure 5. Support removal can be a messy affair as you beat the clock against the cooling parts. Ensure you have gloves, a plier and safety glasses on.
Figure 5. Support removal can be a messy affair as you beat the clock against the cooling parts. Ensure you have gloves, a plier and safety glasses on.

5. Other Observations: the Interface of Separation

It helps to visualize what we are trying to do when we remove supports. There are two interfaces in question here, as shown in Figure 6. One is the model-support interface, the other is the support-box structure interface. We need separation at the model-support interface since removing the thin piece of interface material can prove challenging if the box supports have broken off (as happened for the piece below). What this means is as you remove support, you need to not just pull the supports but also add some peeling force that creates the separation. Once you create separation at the correct interface, you can pull the supports and should have proper cleavage.

Figure 6. (top) Support-model interface surface, and (bottom) support structure interface - it is important to get separation at the former interface
Figure 6. (top) Support-model interface surface, and (bottom) support structure interface – it is important to get separation at the former interface

One final point to keep in mind is that in some cases, eliminating internal supports may be impossible, as shown for a different part in Figure 7 below. The point is to eliminate the support in places you cannot reach with your pliers and get enough peeling force applied to. In the case below, I chose to have supports at the wide opening since I had adequate access to them. With practice, you will get a better sense of what supports can and cannot be removed and use that intuition to better shape your design and process layout decisions before you print.

Figure 6. Support in internal features are alright as long as you have access to them
Figure 7. Support in internal features are alright as long as you have access to them
Figure 7. The final part!
Figure 8. The final part
The ULTEM intake manifold runner and plenum being put through its paces at the ASU Formula SAE test rig
Figure 9. The ULTEM intake manifold runner along with a plenum that we also printed, both being put through their paces at the ASU Formula SAE test rig (Photo Ack: Michael Conard)

Show your support for ASU’s Formula SAE team at their Facebook page and see a video about the endeavor here.

Donny Don’t – Thin Sweep Meshing

It’s not a series of articles until there’s at least 3, so here’s the second article in my series of ‘what not to do’ in ANSYS…

Just in case you’re not familiar with thin sweep meshing, here’s an older article that goes over the basics.  Long story short, the thing sweep mesher allows you to use multiple source faces to generate a hex mesh.  It does this by essentially ‘destroying’ the backside topology.  Here’s a dummy board with imprints on the top and bottom surface:

image

If I use the automatic thin sweep mesher, I let the mesher pick which topology to use as the source mesh, and which topology to ‘destroy’.  A picture might make this easier to understand…

image

As you can see, the bottom (right picture) topology now lines up with the mesh, but when I look at the top (left picture) the topology does not line up with the mesh.  If I want to apply boundary conditions to the top of the board (left picture), I will get some very odd behavior:

image

I’ve fixed three sides of the board (why 3?  because I meant to do 4 but missed one and was too lazy to go back and re-run the analysis to explain for some of future deflection plots…sorry, that’s what you get in a free publication) and then applied a pressure to all of those faces.  When I look at the results:

image

Only one spot on the surface has been loaded.  If you go back to the mesh-with-lines picture, you’ll see that there is only a single element face fully contained in the outline of the red lines.  That is the face that gets loaded.  Looking at the input deck, we can see that the only surface effect element (how pressure loads are applied to the underlying solid) is on the one fully-contained element face:

image

If I go back and change my thin sweep to use the top surface topology, things make sense:

image

The top left image shows the thin sweep source definition.  Top right shows the new mesh where the top topology is kept.  Bottom left shows the same boundary conditions.  Bottom right shows the deformation contour.

The same problem occurs if you have contact between the top and bottom of a thin-meshed part.  I’ll switch the model above to a modal analysis and include parts on the top and bottom, with contact regions already imprinted.

image

I’ll leave the thin sweeping meshing control in place and fix three sides of the board (see previous laziness disclosure).  I hit solve and nothing happens:

image

Ah, the dreaded empty contact message.  I’ll set the variable to run just to see what’s going on.  Pro Tip:  If you don’t want to use that variable then you would have to write out the input deck, it will stop writing once it gets to the empty contact set.  Then go back and correlate the contact pair ID with the naming convection in the Connections branch.

The model solves and I get a bunch of 0-Hz (or near-0) modes, indicating rigid body motion:

image

Looking at some of those modes, I can see that the components on one side of my board are not connected:

image

The missing contacts are on the bottom of the board, where there are three surface mounted components (makes sense…I get 18 rigid body modes, or 6 modes per body).  The first ‘correct’ mode is in the bottom right image above, where it’s a flapping motion of a top-mounted component.

So…why don’t we get any contact defined on the bottom surface?  It’s because of the thin meshing.  The faces that were used to define the contact pair were ‘destroyed’ by the meshing:

image

Great…so what’s the take-away from this?  Thin sweep meshing is great, but if  you need to apply loads, constraints, define contact…basically interact with ANYTHING on both sides of the part, you may want to use a different meshing technique.  You’ve got several different options…

  1. Use the tet mesher.  Hey, 2001 called and wants its model size limits back.  The HPC capabilities of ANSYS make it pretty painless to create larger models and use additional cores and GPUs (if you have a solve-capable GPU).  I used to be worried if my model size was above 200k nodes when I first started using ANSYS…now I don’t flinch until it’s over 1.5M
    image
    Look ma, no 0-Hz modes!
  2. Use the multi-zone mesher.  With each release the mutli-zone mesher has gotten better, but for most practical applications you need to manually specify the source faces and possibly define a smaller mesh size in order to handle all the surface blocking features.
    image
    Look pa, no 0-Hz modes!Full disclosure…the multi-zone mesher did an adequate job but didn’t exactly capture all of the details of my contact patches.  It did well enough with a body sizing and manual source definition in order to ‘mostly’ bond each component to the board.
  3. Use the hex-dominant mesher.  Wow, that was hard for me to say.  I’m a bit of a meshing snob, and the hex dominant mesher was immature when it was released way back when.  There were a few instances when it was good, but for the most part, it typically created a good surface mesh and a nightmare volume mesh.  People have been telling me to give it another shot, and for the most part…they’re right.  It’s much, much better.  However, for this model, it has a hard time because of the aspect ratio.  I get the following message when I apply a hex dominant control:

    image
  4. The warning is right…the mesh looks decent on the surface but upon further investigation I get some skewed tets/pyramids.  If I reduce the element size I can significantly reduce the amount of poorly formed elements:image
  5. That’s going on the refrigerator door tonight!
    image
    And…no 0-Hz modes!
  • Lastly…go back to DesignModeler or SpaceClaim and slice/dice the model and use a multi-body part.image
    3 operations, ~2 minutes of work (I was eating at the same time)

    image
    Modify the connection group to search/sort across parts

    image

    That’s a purdy mesh!  (Note:  most of the lower-quality elements, .5 and under, are because there are 2-elements through thickness, reducing the element size or using a single element thru-thickness would fix that right up)

    image
    And…no 0-Hz modes.

Phew…this was a long one.  Sorry about that.  Get me talking about meshing and look what happens.  Again, the take-away from all of this should be that the thin sweeper is a great tool.  Just be aware of its limitations and you’ll be able to avoid some of these ‘odd’ behaviors (it’s not all that odd when you understand what happens behind the scenes).

Instructions for Installing and Configuring ANSYS MAXWELL and PExprt, Versions 16.X

ANSYS_pexpert_maxwell-1ANSYS PExpert is a fantastic tool for the design, modeling, and analysis of transformers and inductors. Using a combination of classical and finite element analysis (FEA) techniques, ANSYS PExprt determines the core size and shape, air gaps, and winding strategy for a given power converter topology. What we and our customers have found very useful is the ability to then evaluate the magnetic design in ANSYS Maxwell to view such things as flux density in the core and current density distribution in the windings. Powerful stuff.

The first step in implementing ANSYS PExprt with ANSYS Maxwell is installing and configuring them correctly.  We created a step-by-step guild for our ANSYS customers here in the Southwest, and thought others would find it useful.

ansys-maxwell-pexprt-install-image

Download: InstallingMaxwellandPExprt16.pdf

As always, feel free to contact us if you have any questions or need more information. Also, even if you are not in our sales area, please consider using PADT for consulting or training.

 

 

 

 

 

 

 

Tech Tips and Videos for ANSYS Mechanical and CFD

ansys_free_techtipsA few weeks ago we added some great free resources to our website for existing and potential users of ANSYS Structural and CFD tools.  It includes some great videos from ANSYS, Inc. on a variety of topics as well as productivity kits. It dawned on us that many of you are faithful readers of The Focus but don’t often check out our ANSYS product web pages. So, we are including the material here for your viewing pleasure.

(7/9/2015: We just added the Electromechanical kit here.)

For structural users, we have a link to “The Structural Simulation Productivity Kit ” here. The kit includes:

  • Analyzing Vibration with Acoustic–Structural Coupling Article
  • Contact Enhancements in ANSYS Mechanical and MAPDL 15.0 Webinar
  • ANSYS Helps KTM Develop a 21st Century Super Sports Car Case Study
  • A Practical Discussion on Fatigue White Paper
  • Designing Solid Composites Article

We also have a collection of videos from ANSYS, Inc that we found useful:

For CFD users, we have a link to “The CFD Simulation Productivity Kit ” here. The kit includes:

  • Simulating Erosion Using ANSYS Computational Fluid Dynamics Presentation,
  • Cutting Design Costs: How Industry leaders benefit from Fast and Reliable CFD  White Paper,
  • Introduction to Multiphase Models in ANSYS CFD Three Part Webinar,
  • Advances in Core CFD Technology: Meeting Your Evolving Product Development Needs White Paper,
  • Turbulence Modeling for Engineering Flows Application Brief.

We also have a collection of videos from ANSYS, Inc that we found useful:

Interested in learning more, contact us or simply request a quote.

Donny Don’t – Remote Objects

Nothing like a good ‘ol fashion Simpson’s reference.  I’m trying to start a new series of articles that address common mistakes and things to avoid, and what better reference than when Bart ‘joined’ the Junior Campers and found out he might get a knife out of the deal. 

6lrWlDO

For this first article, let’s talk about remote objects (force, displacement, points, joints).  First, remote objects are awesome.  Want to add a rotational DOF to your solid-object model?  Remote Displacement.  Want to apply a load and don’t want to worry about force/moment balance?  Remote Force.  Want to apply a load but also constrain a surface?  Remote Point.  Take two points and define a open/locked degrees of freedom and you have a kinematic joint.

The thing to watch out for is how you define these remote points.  ANSYS Mechanical does an amazing job at making a pretty tedious process easy (create pilot node, create constraint-type contact, specify DOFs to include, specify formulation).  In Mechanical, all you need to do is highlight some geometry, right mouse click, and insert the appropriate object (remote point, remote force, etc).  No need to keep track of real constant sets, element tshape’s…easy.  Almost too easy if you ask me.

Once you start creating multiple remote objects, you may see the following:

message1

If you dig into the solver output file you may see this:

image

The complaint is that we have multiple overlapping constraint sets.  Let’s take a step back and see the model I’ve setup:

image

I have a cylinder, attached to a body-to-ground spring on one face, a translational joint applied on the OD, and a remote force and moment applied on the opposite end.  If I follow the instructions shown from the ANSYS Workbench message about graphically displaying FE Connections (select the ‘Solution Information’ item, click the graphics tab):

image

We can see that any type of constraint equation is shown in red.  The issue here is that the nodes on the OD edge on the top and bottom of my cylinder belong to multiple constraint equation sets.  On the bottom my my cylinder those nodes are being constrained to the spring end AND the cylindrical joint.  On the top the nodes on the edge are being constrained to the joint AND remote force.  When you hit solve, ANSYS needs to figure out how to resolve the conflicting constraint sets (a node cannot be a slave term for two different constraint sets).  I don’t know exactly how the solver manages this, but I like to imagine it’s like two people fighting over who gets to keep a dog…and they place the dog in-between them and call for it, and whoever the dog goes to gets to keep it. 

Now for this example, the solver is capable of handling the over-constraint because overall…the model is properly constrained.  The spring can loose some of the edge nodes and still properly connect to the cylinder.  Same goes for the other remote objects (translation joint and remote force/moment).  If we had more objects defined and more overlaps, that’s a different story.  You can introduce a pretty lengthy lag, or outright solver failure, if there are a lot of overconstraint terms in the model. 

So now the question becomes, how do I fix this.  The easiest way is to not fix this and ignore the warning.  If our part behaves properly, we get the reaction forces we’d expect, then odds are the overconstraint terms that are automatically corrected are fine.  If we actually wanted to remove that warning, we would need to make sure we scope remote objects that do not touch other remote objects.  We can do this by going into DesignModeler or SpaceClaim and imprinting the surfaces. 

image

In DM, I just extruded the edges with the operation set to imprint face.  In SpaceClaim you would just need to use the ‘copy edge’ option on the pull command:

image

Now this will modify the topology and will ensure we have a separation of nodes for all of our remote objects:

image

When we solve…no warning message about MPC conflicts:

image

And when we look at the FE connectivity, there are no nodes shared by multiple remote objects:

image 

The last thing I’d like to point out is the application of a force and moment on a remote point:

image

Whenever you have two remote objects operating on the same surface (e.g. a moment and force, force and displacement, etc), you should really be using a remote point.  If I were to create two remote objects:

image

I now come right back to my original problem of conflicting constraints.  These two objects share the exact same nodal set but are creating two independent remote points.  If you want to do this, right-mouse-click on one of your remote objects and select ‘promote to remote point’:

image

Then modify the other remote objects to use that remote point.  No more conflict. 

Very last point…in R16 it will now tell you when you have ‘duplicate’ remote objects  (like the remote force + displacement shown above). 

image

Hope this helps! 

Some Stuff ANSYS Users Should Know about Excel

imageWhat is the software tool that us numerical simulation types use almost as much as ANSYS products, maybe even more?  Most of you will answer Microsoft Excel.  We all use it almost every day for a variety of things. Every time I see someone doing something sophisticated with Excel, I learn something new, a tool I can use to be more efficient. 

For this week’s The Focus posting I will be sharing some stuff in Excel, tips and tricks, that ANSYS users should find useful.  I am using Microsoft Excel 2010 and the assumption is that the reader is a good user of Excel, maybe not an expert, but good.  I have tried to pick things that have a direct impact on user efficiency.  You may already know some or even most of these things, but hopefully you will find some of it useful.  If you have something to share, please add it to the comments.

Take the Time to Setup Tables

I love tables.  I’m always getting made fun of because I always convert what I’m working on into tables.  Why are they so great? 

    • They auto-format
    • They have filtering built in
    • You can refer to the table, columns, rows, and cells in equations with names rather than ranges
    • When you add a formula in a column, it automatically copies it to the whole table (my favorite)
    • It does automatic totals, averages, etc…

Making a table is easy:

    1. Select the columns you want in your table
      1. It is a good idea to have the headers defined
    2. Go to the Insert Tab
    3. Click on Table

image

That give you:

image

Click on the downward facing triangle icons to filter.  Use the options in the Table Tools > Design tab to set the name, remove duplicates, turn on the total row, and change the basic formatting (color).  Once you have played with these for a while, you will find you can not live without them and people will ask you why you use tables so often.

Concatenation

One of the ways that we use Excel is to convert some sort of text data in row/column form into a command, mostly MAPDL commands.  A key to this is the ability to concatenate text strings and the values of cells.  I’ve even seen someone write a NASTRAN to ANSYS translator in Excel.

To do so you create a formula (start with =) and string together the text you want with ampersands: &

As an example, if we want to add a column to the table we used above to create N commands we simply click on any of the cells in the empty column next to our table and enter:

=”n, “&[@N]&”, “&[@X]&”, “&[@[Y ]]&”, “&[@Z]

Because we are using a table, the command uses the column reference [@name] from the tables rather than cells.  In a non table the command would look like:

=”n, “&$A6&”, “&$B6&”, “&$C6&”, “&$D6

Either way you are stringing the values in your cells together with text to make a command:

image

That column can be pasted into a text file, an ANSYS Mechanical code snippet window, or saved to a file.

Text to Columns

After tables, the next most useful feature in Excel for the analyst is the ability to convert the text in a column into multiple columns. This is a lot like the text import window that opens up when you open a text file, but it can be used at any time on any column in your spreadsheet.  To use it, simply select the column you want to convert:

image

Then go to the Data tab and click on “Text to Columns”

image

This will bring up the wizard that steps you through the process:

image

If you are working with a NASTRAN type input file, formatted with fixed columns, you can chose “Fixed Width” here. If not, choose delimited.  Click next.

For fixed, you get a ruler that you can drag the column lines back and forth on till you get what you want. Pretty simple.

For delimited, you get the delimiter screen.  Specify your delimiter here.  In the example, we will use a comma. But it can be spaces, tabs, or any other character. When you specify the delimiter, it shows you how Excel will break it up. 

image

I usually click finish here because the next screen is formatting and I usually play with that once I have the data in Excel.

That is it. Very simple.

image

One thing to note, it converts to columns by overwriting columns to the right. So if you have data in those columns, you should insert enough blank columns before you use this command, so you don’t overwrite anything.

Names

Usually you refer to a cell or a range of cells with the old LetterNumber syntax: A3, B7:NN2145, etc…  That can be a real pain to deal with and it really doesn’t tell you what the data in that range is.  A better way to deal with chunks of information, or critical cells, is to use names. 

Creating names is very easy.  The simplest is to click on the cell or cells you want to name and then type in the name you want in the input box in the upper left corner:

image

Now, if you want to know the max value of those numbers, you can use the formula =max(MyData)

image

If I have a lot of constants I want to define, I can use the “Create from Selection” tool in the Formulas tab:

image

This command brings up a dialog box and you can tell Excel where to grab your names from. Three or Four clicks and you have named parameters instead of cell locations.  This is very useful if you have a group of key parameters you want to use in your calculations.  Now when you look at your formulas, the descriptive name of the parameters are there rather than a reference.

image

Use the Name Manager in the same Formulas tab to view, edit, and delete your names.

Dynamic Range

A related trick for Excel is creating dynamic ranges. What do you do when you name a range and then the amount of data in that range changes? You have to redefine your range.  Nope, you don’t. You can define the range using a formula that changes as the length of the column, or row, changes. 

The name can be defined for a column as: =OFFSET(startCell,0,0,COUNTA(column)-1)

Or for a row: =OFFSET(startCell,0,0,0,COUNTA(row)-1)

This may be the most time saving trick I know in Excel.

You put the formula into the “Define Name” dialog box found on the Formulas tab:

image

Now, no matter how long the column of data is, MyVals will always contain it.  A big time saver.

Relative Reference on Record Macro

How many times have you gotten data in Excel, or imported data into Excel, where you want to make a small change to every line. But you have several thousand lines. If you do a “Record Macro” that doesn’t work because you have to click down to the next line, then run the macro and repeat that over and over again. Wouldn’t it be great if you could simply record a macro with some sort of relative reference. 

For years (maybe decades) I didn’t know you could do that. There is an option under the Developer Tab called “Relative Reference.”  Click that before you record your macro and you are good to go.

image

As an example, take a look at this data. Nodal coordinates on one line, rotations on the second. 

image

I want to grab the rotations, paste them on the same line as the coordinates, delete the rotation line, then move to the next node.

Here is a video that shows the process:

That is all fine and dandy if you have a few dozen lines, but your fingers will get tired CTRL-e’ing that many times.  I quick fix is to go into the macro and add a simple loop.  First we use CountA() to see how many nodes we have, then we loop on that with a for statement:

Sub Macro3()

'

' Macro3 Macro

'

' Keyboard Shortcut: Ctrl+e

'

    cnt = Application.CountA(Range("a:a"))

    For i = 1 To cnt

    ActiveCell.Offset(1, 1).Range("A1:C1").Select

    Selection.Cut

    ActiveCell.Offset(-1, 3).Range("A1").Select

    ActiveSheet.Paste

    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

    Selection.Delete Shift:=xlUp

    ActiveCell.Select

    Next i

End Sub

Of course you could have done this with *VREAD’s in MAPDL, or python. But sometimes Excel is just faster.