Importing Beam Force Details and Beam End Forces from STAAD to Excel through OpenSTAAD

In this post, a program will be created to import the beam forces at specific sections and Beam end forces for any member or Load case as desired.

Following is the sample excel file with the format and the code which can be pasted/modified in the Macros editor in MS Excel (alt+F11 > Insert Module) and incorporated as per requirements.

For Beam End Forces:

The excel and the macros have been made in the format shown below.

Cell A3:A4 or beyond shall have the beam numbers for which the forces are to be extracted.
Cell B3:B6 or beyond shall have the Load cases for which the forces are to be extracted.
Cell A1 and B1 shall have the formula for counting the numbers of beams and Load cases inserted.

The sheet looks something like this:

As per the official guide to Openstaad, to extract the beam end forces, the syntax shall be:

Output.GetMemberEndForces
Returns member end forces for specified member number, member end and load case.
VB Syntax
Output.GetMemberEndForces MemberNo, End, LoadCase, ForceArray
Where:
MemberNo
Long variable contains the member number.
End
Long variable contains member end.
0. = Start
1. = End
LoadCase
Long variable contains the load case number.
ForceArray
A six dimensional array which returns member end forces.

Using the above syntax, the forces can be extracted quite conveniently and directly to excel itself. However, please note that the STAAD file shall be analysed first and should be open in the background.
The above syntax has been used as shown below:

Sub Get_Mem_End_Forces()
Dim NoofMems As Integer
Dim NoofLCs As Integer
Dim Mems() As Long 
Dim LCs() As Long 
Dim Start As Long 'Start node
Dim Ed As Long 'End node
Dim ForceArray(6) As Double 'Array to extract forces
Range("L3").Select
Cells(2,4).Value="Beam Number"
Cells(2,5).Value="Loadcase"
Cells(2,6).Value="Node"
Cells(2,7).Value="Fx"
Cells(2,8).Value="Fy"
Cells(2,9).Value="Fz"
Cells(2,10).Value="Mx"
Cells(2,10).Value="My"

Cells(2,10).Value="Mz"

Range("D3:L5000").Select

Selection.ClearContents
NoofMems = Cells(1, 1)
NoofLCs = Cells(1, 2)
Start = 0
Ed = 1
ReDim Mems(1 To NoofMems)
ReDim LCs(1 To NoofLCs)
For i = 1 To NoofMems
Mems(i) = Cells(2 + i, 1)
Next i
For i = 1 To NoofLCs
LCs(i) = Cells(2 + i, 2)
Next i
Set objOpenSTAAD = GetObject(, "StaadPro.OpenSTAAD")
For i = 1 To NoofMems
For j = 1 To NoofLCs
Cells(1 + 2 * j + 2 * (i - 1) * NoofLCs, 4) = Mems(i)
Cells(1 + 2 * j + 2 * (i - 1) * NoofLCs, 5) = LCs(j)
Cells(1 + 2 * j + 2 * (i - 1) * NoofLCs, 6) = "S"
objOpenSTAAD.Output.GetMemberEndForces Mems(i), Start, LCs(j), ForceArray
For k = 1 To 6
Cells(1 + 2 * j + 2 * (i - 1) * NoofLCs, 6 + k) = ForceArray(k - 1)
Next k
Next j
Next i
For i = 1 To NoofMems
For j = 1 To NoofLCs
Cells(2 + 2 * j + 2 * (i - 1) * NoofLCs, 6) = "E"
objOpenSTAAD.Output.GetMemberEndForces Mems(i), Ed, LCs(j), ForceArray
For k = 1 To 6
Cells(2 + 2 * j + 2 * (i - 1) * NoofLCs, 6 + k) = ForceArray(k - 1)
Next k
Next j
Next i
Set objOpenSTAAD = Nothing
End Sub

However, please note that the cell addresses for the inputs and the outputs can be changed as per the user requirement.
The results would look something like this

and the imported values can be used in the excel or for any other software linked with excel as the user wants.

The second program is about extracting the section forces at predefined sections (in ratio of length of the member)

Beam Force Details at sections:

The excel and the macros have been made in the format shown below.

Cell A3:A4 or beyond shall have the beam numbers for which the forces are to be extracted.
Cell B3:B6 or beyond shall have the Load cases for which the forces are to be extracted.
Cell C1 shall have the number of sections of a member at which the forces are required (number of divisions+1 - Supposedly, you want 4 sections then the value will be 5).
Cell A1 and B1 shall have the formula for counting the numbers of beams and Load cases inserted (as earlier).

The sheet looks something like this:

As per the official guide to Openstaad, to extract the beam end forces, the syntax shall be:

Output.GetIntermediateMemberForcesAtDistance MemberNo, Distance, LoadCase,
DisplacementArray
Where:
nMemberNo
Long variable contains the member number.
Distance
Double variable contains distance from the start of the memberd in length units.
nLoadCase
Long variable contains the load case number.
pdForcesArray
A six dimensional long array, which returns member sectional forces and moments.

In order to calculate the sections in divisions of an integer, we need to get the member length first. So, again going according to the official guide to Openstaad, the syntax to extract the beam length goes as:

Geometry.GetBeamLength BeamNo
Where:
BeamNo
A long variable providing the beam member number for which the length is to be retrieved.

Both of the above mentioned programs will be running in the same command to extract the values for the input members.

The above can be framed into a program as follows

Sub Get_Mem_Sec_Forces()
Dim NoofMems As Integer
Dim NoofLCs As Integer
Dim Noofsecs As Integer
Dim Dis() As Double 'use Double for decimals
Dim Mems() As Long
Dim LCs() As Long
Dim BeamNo() As Long
Dim BeamLen As Double
Dim ForceArray(6) As Double
Dim a As Integer
Dim b As Integer
Cells(2,4).Value="Beam Number"
Cells(2,5).Value="Loadcase"
Cells(2,6).Value="Section"
Cells(2,7).Value="Fx"
Cells(2,8).Value="Fy"
Cells(2,9).Value="Fz"
Cells(2,10).Value="Mx"
Cells(2,10).Value="My"

Cells(2,10).Value="Mz"

Range("L3").Select
Range("D3:L5000").Select
Selection.ClearContents
NoofMems = Cells(1, 1)
NoofLCs = Cells(1, 2)
Noofsecs = Cells(1, 3)
Cells(1, 4).Value = Noofsecs
ReDim Mems(1 To NoofMems)
ReDim LCs(1 To NoofLCs)
ReDim Dis(1 To Noofsecs)
For i = 1 To NoofMems
Mems(i) = Cells(2 + i, 1)
Next i
For i = 1 To NoofLCs
LCs(i) = Cells(2 + i, 2)
Next i
ReDim BeamNo(1 To NoofMems)
For i = 1 To NoofMems
BeamNo(i) = Cells(2 + i, 1).Value
Next i
Set objOpenSTAAD = GetObject(, "StaadPro.OpenSTAAD")
For i = 1 To NoofMems
BeamLen = objOpenSTAAD.Geometry.GetBeamLength(BeamNo(i))
Cells(2, 20).Value = "BeamNo"
Cells(2, 21).Value = "Beam Length"
Cells(2 + i, 20).Value = BeamNo(i)
Cells(2 + i, 21).Value = BeamLen
For b = 1 To Noofsecs
Cells(2+b, 3).Value = BeamLen * (b-1/Noofsecs-1)
Dis(b) = Cells(2 + b, 3)
Next b
For j = 1 To NoofLCs
Cells(3 + (i - 1) * NoofLCs * Noofsecs, 4) = Mems(i)
Cells(3 + (i - 1) * NoofLCs * Noofsecs + (j - 1) * Noofsecs, 5) = LCs(j)
For a = 1 To Noofsecs
Cells(3 + (i - 1) * NoofLCs * Noofsecs + (j - 1) * Noofsecs + a - 1, 6) = Dis(a)
objOpenSTAAD.Output.GetIntermediateMemberForcesAtDistance Mems(i), Dis(a), LCs(j), ForceArray
For k = 1 To 6
Cells(3 + (i - 1) * NoofLCs * Noofsecs + (j - 1) * Noofsecs + a - 1, 6 + k) = ForceArray(k - 1)
Next k
Next a
Next j
Next i
Set objOpenSTAAD = Nothing
End Sub

The output will be same as in the first program. Now we have extracted the forces to excel and they can be used where ever required.

The following is a separate program just to get the beam lengths from the model:

Sub Get_BeamLength()
Dim objOpenSTAAD As Object
Dim BeamNo() As Long
Dim BeamLen As Double
Dim i As Integer
Dim NoofMems As Integer
NoofMems = Cells(1, 1).Value
ReDim BeamNo(1 To NoofMems)
For i = 1 To NoofMems
BeamNo(i) = Cells(2 + i, 1).Value
Next i
Set objOpenSTAAD = GetObject(, "StaadPro.OpenSTAAD")
For i = 1 To NoofMems
BeamLen = objOpenSTAAD.Geometry.GetBeamLength(BeamNo(i))
Cells(2 + i, 3).Value = BeamLen
Next i
Set objOpenSTAAD = Nothing
End Sub

I hope the codes were helpful. Please change the cell locations as you wish and do note that the outputs are always in KN or KNm in the programs, so be aware of the units being used in the design.

Please leave your suggestions and questions in the comments. Will be back shortly with another program soon. Be efficient, save time but do check the values.