2.8.1. Accessing Results using Excel

A plugin is available to allow you to access BEAMST and FATJACK results in Microsoft® Excel®. It is intended to facilitate the development of Microsoft Excel spreadsheets to present results from code checking. The interface consists of a series of Excel functions that can be accessed within the spreadsheet to link to data directly in the results database.

The functions can be included in formulae just like ordinary Excel functions (such as sine, sum, max, etc.). If you cannot remember the order or meaning of the arguments to the function, use Excel’s function wizard - the functions are listed in a category called ASAS-Excel. All the functions have names starting with the prefix axl.

2.8.1.1. Compatibility

The plugin is available for 32-bit and 64-bit Excel versions designed to run under Microsoft Windows.

2.8.1.2. Installing the Excel Plugin

The interface for Excel® consists of two main parts:

  • A dynamic link library called axl32.dll for 32-bit versions of Excel and axl64.dll for 64-bit versions.

  • An Excel® add-in macro sheet called axl32.xla for 32-bit versions of Excel and axl64.xla for 64-bit versions.

The notes below relate to Microsoft® Excel® 2007 and 2010 on 32-bit systems. Earlier versions may vary slightly from the suggested installation. For Excel 2010 64-bit, the same instructions apply, but the axl64.xla and axl64.dll should be used.

To use the plugin functions, Excel needs to know the location of the XLA file and must be able to access the axl32.dll or axl64.dll file. To make these files accessible:

axl32.dll/axl64.dll: This file is located within the installed Ansys files (default location: C:\Program Files\Ansys Inc\version\asas\bin\platform). It must either be copied to a location on the system path or have the above folder added to the path. You may need additional permissions to perform either of these actions and therefore we recommend that you consult your IT department.

axl32.xla/axl64.xla: In Excel, select Office > Excel Options > Add-Ins, then browse to the location of the file; by default this is in the axl subfolder of the installation. Next, select Office > Excel Options > Add-Ins > Manage Excel Add-ins and ensure the Axl32 box is selected. Ensure that these settings are saved before closing Excel.

If you are presented with options to update links when opening an existing spreadsheet, select Don’t Update, then copy the path preceding a command, including apostrophes and the exclamation mark, and use Find and Replace with no entry for the Replace to remove it. Save and close your file, and this message shouldn’t be shown again when re-opening, unless the link is modified (this may occur if you transfer the file to another machine and the path to the XLA file is different).

If you are shown this repeatedly, it is likely that the links are not being updated correctly, so check the path to the link in Edit > Links, ensure that only one axl32.xla file is installed on your machine, then update the location via Tools > Add-Ins.

Test the installation by entering =AxlVersion() into a cell to ensure that you have the correct version installed. If the result cannot be evaluated, then it is likely that the axl32.dll is not located on the system path.

2.8.1.3. Available Function Descriptions

Many of the functions start with an argument called model in the descriptions below. This argument defines the project and structure being investigated, and the directory in which the files are located. It is a string consisting of three parts, separated by semi-colons:

folder;project;structure

When a Design Assessment system has been used, both the project and structure default to MECH; for example, if your files were stored in the folder c:\user the model path would be:

C:\USER;MECH;MECH

The best way of entering this data is to enter it into a single cell of the spreadsheet, then refer to this cell in each call of a function. Use the $ symbol to make this an absolute cell reference, or name the cell to make it easier. The purpose of specifying the model in each call is to allow results from different models to be intermixed in one spreadsheet at will.

FUNCTIONSDESCRIPTION
Functions to retrieve basic model information
axlbeamaxisReturns beam axis direction cosines
axlelementnodeReturns the value of a node number on an element
axlloadnameReturns the title of the specified loadcase number
axlloadnumberReturns the user loadcase number of the specified internal loadcase
axlnelementReturns the number of elements in the model
axlnloadReturns the number of loadcases on the model
axlnnodeReturns the number of nodes on the model
axlnodeReturns one of the global coordinates of a specified node
axlnodenumberReturns the user node number
axlnumbersubsetsReturns the number of subsets for a given loadcase
axlrundateReturns date and time of the BEAMST / FATJACK run
axlruntitleReturns the title of the ASAS analysis
axlsubsetnumberReturns a user subset number given a model, user loadcase and system subset number
axlunitsReturns the units associated with the analysis
axluserelementReturns the user element number
Functions to obtain results
axlgetelementReturns the requested element result
axlgetelementexReturns the requested element result, extended for additional result availability
axlgetelementfltReturns the requested element result, filtered based on the given information
axlgetequationReturns the requested equation result
axlgetglobalReturns the requested global result
axlgetnodalReturns the requested nodal result
Miscellaneous functions
axlerrorReturns a text string describing an error code returned by a function
axlversionReturns a text string identifying the version of the add-in macro sheet (XLA file) and the associated dynamic link library (DLL file) which are currently being used

Note:  When entering string arguments to functions, they should be enclosed in double quotes.


2.8.1.3.1. Functions to Retrieve Basic Model Information

The following functions can be used to retrieve basic model information, such as the number of nodes, elements and loadcases.

axlbeamaxis

Purpose:Returns the direction cosine components for beam element local axes.
Syntax:axlbeamaxis (model, element, axisname)
Notes:

axisname corresponds to the required axis direction cosine and may be one of the following:

MnemonicDescription

XX

YX

XZ

Direction cosines for local X axis

YX

YY

YZ

Direction cosines for local Y axis

ZX

ZY

ZZ

Direction cosines for local Z axis

The axis information returned relates to the final member orientation i.e. taking account of any rigid offsets.

Example:axlbeamaxis(Jacket, 1001, ”Zy”) returns the y component of the local Z axis for element 1001 in the model defined by the named cell Jacket.

axlelementnode

Purpose:Returns the value of a node number on an element. Nodes are returned in the same order as they are defined in the model.
Syntax:axlelementnode (model, element, index)
Notes:index is the position of the node in the element definition, between 1 and the number of nodes that the element supports.
Example:axlelementnode(Jacket, 2050, 2) returns the number of the second node on element 2050 in the model defined by the named cell Jacket.

axlloadname

Purpose:Returns the value of a node number on an element. Nodes are returned in the same order as they are defined in the model.
Syntax:axlloadname (model, loadcase)
Notes:loadcase is the user defined loadcase number.
Example:axlloadname(Jacket, 10) returns the title of loadcase 10 in the model defined by the named cell Jacket.

axlloadnumber

Purpose:Purpose: Returns the user loadcase number of the specified internal loadcase.
Syntax:axlloadnumber (model, loadcaseindex)
Notes:The purpose of this function is to allow users to generate a list of all the load cases on the model by creating a table with one column containing the sequence 1, 2, 3, ... and the next column using this function to determine the load case number. See also axlnload.
Example:axlloadnumber(Jacket, 2) returns the user loadcase number corresponding to the second loadcase for the model defined by the named cell Jacket.

axlnelement

Purpose:Returns the number of elements in the model.
Syntax:axlnelement (model)
Notes:This function will exclude any components that have been defined in the model.
Example:axlnelement(Jacket) returns the number of elements in the model defined by the named cell Jacket.

axlnload

Purpose:Returns the number of loadcases on the model.
Syntax:axlnload (model)
Notes:May be useful used in conjunction with axlloadnumber above to generate a list of user loadcase numbers.
Example:axlnload(Jacket) returns the number of loadcases in the model defined by the named cell Jacket.

axlnnode

Purpose:Returns the number of nodes on the model.
Syntax:axlnnode (model)
Notes:Nodes are counted on the basis of generating equations in the stiffness matrix, so nodes which are only used as guide points to define local axes or skew systems are not included.
Example:axlnnode(Jacket) returns the number of structural nodes in the model defined by the named cell Jacket.

axlnode

Purpose:Returns one of the global coordinates of a specified node.
Syntax:axlnode (model, nodenumber, axis)
Notes:axis may be specified by name (“X”, “Y” or “Z”) or by number (1, 2 or 3).
Example:axlnode(Jacket, 1010, ”y”) returns the y global coordinate for node 1010 in the model defined by the named cell Jacket.

axlnodenumber

Purpose:Returns the user node number by reference to an index in increasing node number order.
Syntax:axlnodenumber (model, index)
Notes:index alludes to the reference to a given node within a list of ascending user node numbers. index should be between 1 and the number of nodes in the model; see axlnnode.
Example:axlnodenumber(Jacket, 216) returns the node number at position 216 of the sorted node number list for the model defined by the named cell Jacket.

axlnumbersubsets

Purpose:Returns the number of subsets which constitute a loadcase.
Syntax:axlnumbersubsets (model,loadcase)
Notes:The function returns the number of subsets within a loadcase of a model, for example when a transient analysis is being code checked.
Example:axlnumbersubsets(Pile, 4) returns the number of subsets contained within the fourth load of the model defined by Pile.

axlrundate

Purpose:Returns the date and time for a given analysis.
Syntax:axlrundate (model)
Notes:The function returns a 17 character text string containing the date and time in the form “08.41 1-JUL-97”.
Example:axlrundate(Jacket) returns the date and time information for the model defined by the named cell Jacket.

axlruntitle

Purpose:Purpose: Returns the title used for a given analysis.
Syntax:axlruntitle (model)
Notes:This function returns an 81 character text string containing the title in the form “Example 2.1 Extreme Wave Analysis”.
Example:axlruntitle(Jacket) returns the title for the model defined by the named cell Jacket.

axlsubsetnumber

Purpose:Returns the user number of a subset.
Syntax:axlsubsetnumber (model,loadcase,subset)
Notes:The function returns the user number of a subset denoted by its model, load and system subset number.
Example:axlsubsetnumber(Jacket, 6, 2) returns the user subset number of the second subset contained within the sixth load of the model defined by Jacket.

axlunits

Purpose:Returns the user number of a subset.
Syntax:axlunits (model, unitname)
Notes:

unitname refers to the particular unit type required and may be one of the following:

FORCE
LENGTH
ROTATION
MASS
TEMPERATURE
TIME

Abbreviations are permitted; for example: F, FOR, FORCE will all retrieve the force unit.

If units were not used in the analysis an appropriate error message is returned.

The function returns a 13-character unit name.

Example:axlunits(Jacket, ”length”) returns the length unit the model defined by the named cell Jacket.

axluserelement

Purpose:Returns the user element number by reference to an index in increasing user element order.
Syntax:axluserelement (model, index)
Notes:index alludes to the reference to a given element within a list of ascending user element numbers. index should be between 1 and the number of elements in the model; see axlnelement. Components are excluded from this list.
Example:axluserelement(Jacket, 115) returns the user element number at position 115 of the sorted user element list for the model defined by the named cell Jacket.

2.8.1.3.2. Results Database Functions

These functions allow access to results from BEAMST and FATJACK. See BEAMST Results and FATJACK Results for a description of the result type and result component referred to in the following function definitions.

axlgetelement

Purpose:Returns the requested element result.
Syntax:axlgetelement (model, element, load, subset, result type, result component, surface, node )
Notes:

The result type and result component indicate which information will be retrieved. For example the result type may be “stress” and the result component “sxx”, “syy”, “szz”, and so on. The result type and component can be entered as upper or lower case. An element may have more than one surface or node and therefore the surface number and node must be entered in this function. Zero should be entered for subset if the load contains no subsets.

In instances where beam type elements have results at section positions along their length, the node field is used to identify the section positions. Care should be taken if section steps are used, as two results will be generated at these positions, one on either side of the step. For example, if you have a 10m long tube, with a section change at 2m from one end, and section results are requested at 10% intervals, you will have twelve result positions (two at the ends, nine at intermediate points, one at the step).

Example:axlgetelement(Jacket, 35, 2, 10, “force/moment”, “x”,1,3) returns the force in the x direction at the third node on the first surface of element 35. The results are retrieved for the tenth subset of loadcase 2 within the model referenced by the named cell Jacket.

axlgetelementex

Purpose:Returns the requested element result, extended for additional result availability.
Syntax:axlgetelementex (model, element, load, subset, result type, result component, surface, node, result position)
Notes:This is an extended version of axlgetelement; all input is the same as that except for the additional result position. This is used for results where there are multiple values of a result; for example, STRESS in a time history based FATJACK analysis with rainflow counting.
Example:axlgetelementex(“C:\;ABCD;WXYZ”, 72, 11, 1, “RANGE HISTOGRAM”, “STRESS”,1,2,10) returns the 10th STRESS in the RANGE HISTOGRAM values at the second node on the first surface of element 72. The results are retrieved for loadcase 11, subset 1 within the project ABCD, structure WXYZ, located in C:\.

axlgetelementflt

Purpose:Returns the requested element result, filtered based on the given information.
Syntax:axlgetelementflt (model, element, load, subset, result type, result component, surface, node, result position, filter type, return type)
Notes:

This is an extended version of axlgetelement; however it allows for the filtering of a range of values to be obtained. Model, result type, result position and result component all function as per axlgetelementex. The filtering is controlled by the filter type and requested value, for which the following should be entered:

Filter TypeFiltering Algorithm
1Maximum
2Minimum
3Absolute Maximum (i.e. furthest from zero)
4Absolute Minimum (i.e. closest to zero)

Return TypeReturned Result
1Value of Result
2Element Number at which the value can be found
3Load Case / Set for which the value can be found
4Load Subset for which the value can be found
5Surface at which the value can be found
6Local node at which the value can be found

Element, load, subset, surface and node should have either a particular value assigned to limit the searching to a particular set of results, or -1 entered to loop over each result available. For example, entering a loadcase of -1 will mean that all loadcases will be searched according to the filter type and requested value.

Example:axlgetelementflt(“C:\;ABCD;WXYZ”, -1, 100, 0, “API LRFDALLOED1 UC”, “UC.AXIAL”,1,-1,1,2,1) returns the maximum unity check value for all positions on all elements for loadcase 100. The results are retrieved for the project ABCD, structure WXYZ, located in C:\.

axlgetequation

Purpose:Returns the requested equation result.
Syntax:axlgetequation (model, node, load, subset, result type, result component)
Notes:The result type and result component indicate which information will be retrieved. For example the result type may be “displacement” and the result component “x”, “y”, “z”, and so on. The result type and component can be entered as upper or lower case. Zero should be entered for subset if the load contains no subsets.
Example:axlgetequation(Rig, 2, 3, 0, “displacement”, “y”) returns the displacement in the y direction at the second node of loadcase 3 within the model “Rig”.

axlgetglobal

Purpose:Returns the requested global result.
Syntax:axlgetglobal (model, load, subset, result type, result component)
Notes:Global results are specific to the load case and/or subset and not to a certain element or node. The result type and result component indicate which information will be retrieved. For example the result type may be “reaction sum” and the result component “x”, “y”, “z”, and so on. The result type and component can be entered as upper or lower case. Zero should be entered for subset if the load contains no subsets.
Example:axlgetglobal(Jacket, 10, 0, “prescribed reac sum”, “z”) returns the prescribed reaction global sum in the z direction for loadcase 10 within the model “Jacket”.

axlgetnodal

Purpose:Returns the requested nodal result.
Syntax:axlgetnodal (model, node, load, subset, result type, result component, freedom)
Notes:Nodal results are generally concerned with the history of the displacement, velocity and acceleration of a node. The result type and result component indicate which information will be retrieved. For example the result type may be “history displacement” and the result component “x”, “y”, “z” or “time”, and so on. The result type and component can be entered as upper or lower case. Zero should be entered for subset if the load contains no subsets. For example, if “max displacement” is entered as a result type this will retrieve the maximum displacement from all the loadcases, and therefore the loadcase should be entered as one and the subset zero. The freedom input indicates which freedom (“x”, “y”, “z” for example), to retrieve for a particular result type (“max displacement” for example).
Example:axlgetnodal(Rig, 12, 1, 0, “max acceleration”, “time”, 2) returns the time at which the maximum acceleration occurred for freedom number 2 when considering all loadcases within the results set.

2.8.1.3.3. Miscellaneous Functions

axlerror

Purpose:Returns a text string describing an error code returned by a function.
Syntax:axlerror (error)
Notes:The description may be useful in determining the cause of a problem.
Example:

axlerror (302)

or:

axlerror (“axl:302”)

will return the description: “AXL: invalid element number”. This function should only be used when another function in a cell displays a message of the form axl:nnn where nnn is an error number. For example, if cell C22 is showing axl:302, then typing:

=axlerror(C22)

in an empty cell will show a description of the error, as above.

axlversion

Purpose:Returns a text string identifying the version of the add-in macro sheet axl.xla and the associated dynamic link library axl.dll which are currently being used. This is mainly intended so that support personnel can identify possible problems due to the use of out-of-date software.
Syntax:axlversion ( )
Notes:

This returns a string something like:

“axl.xla version 12.00.01.0, axl.dll version 12.00.01.0”

Note that the function does not require an argument.

Example:Not required.


Release 18.2 - © ANSYS, Inc. All rights reserved.