在Office 2010的Excel中使用SAP2000 v15的OAPI
作者：余承飞（酷鱼，QQ: 1931045538） 2015.11.11
1 前言
2 启用Excel的开发工具
首先要启用Excel的开发工具。打开Excel，选择菜单“文件”=>“选项”，如下图。
在“Excel选项”对话框中，选择“自定义功能区”，勾选“开发工具”，如下图。
点击确定。这时可以看到出现了“开发工具”选项卡，里面有“Visaul
Basic”按钮，说明Excel已经配置好，我们可以开始编程了。
3 运行示例代码
点击上述的“Visual Basic”按钮，会出现“Microsoft Visual Basic – 工作薄
1”窗口。在左侧目录树中双击“ThisWorkBook”，如下图所示。
再给程序添加引用。选择菜单“工具/引用”，如下图所示：
在出现的“引用”对话框中，找到“Sap2000v15”并勾选它，如下图所示。
按“确定按钮”。
在SAP2000的安装目录中有OAPI文档，里面有示例代码，我们去找到这些代码。进入SAP2000的安装目录（默认是C:\Program
Files (x86)\Computers and Structures\SAP2000
15），找到CSi_OAPI_Documentation.chm，如下图。
双击打开此文件。展开“Example Code”，点击“Example(VBA)”，如下图。
将右边窗口中的代码复制下来，代码如下：
Sub VerificationExample1001()
'dimension
variables
Dim SapObject As Sap2000v15.SapObject
Dim SapModel As cSapModel
Dim ret As Long
Dim i As Long
Dim ModValue() As Double
Dim PointLoadValue() As Double
Dim Restraint() As Boolean
Dim FrameName() As String
Dim PointName() As String
Dim NumberResults As Long
Dim Obj() As String
Dim Elm() As String
Dim LoadCase() As String
Dim StepType() As String
Dim StepNum() As Double
Dim U1() As Double
Dim U2() As Double
Dim U3() As Double
Dim R1() As Double
Dim R2() As Double
Dim R3() As Double
Dim SapResult() As Double
Dim IndResult() As Double
Dim PercentDiff() As Double
Dim SapResultString() As String
Dim IndResultString() As String
Dim PercentDiffString() As String
Dim msg As String
'create Sap2000
object
Set SapObject = New SAP2000v15.SapObject
'start Sap2000
application
SapObject.ApplicationStart
'create SapModel
object
Set SapModel = SapObject.SapModel
'initialize model
ret = SapModel.InitializeNewModel
'create new blank
model
ret = SapModel.File.NewBlank
'define material
property
ret = SapModel.PropMaterial.SetMaterial("CONC",
MATERIAL_CONCRETE)
'assign isotropic
mechanical properties to material
ret = SapModel.PropMaterial.SetMPIsotropic("CONC", 3600, 0.2,
0.0000055)
'define rectangular frame
section property
ret = SapModel.PropFrame.SetRectangle("R1", "CONC", 12, 12)
'define frame section
property modifiers
ReDim ModValue(7)
For i = 0 To 7
ModValue(i) = 1
Next i
ModValue(0) = 1000
ModValue(1) = 0
ModValue(2) = 0
ret = SapModel.PropFrame.SetModifiers("R1", ModValue)
'switch to k-ft
units
ret = SapModel.SetPresentUnits(kip_ft_F)
'add frame object by
coordinates
ReDim FrameName(2)
ret = SapModel.FrameObj.AddByCoord(0, 0, 0, 0, 0, 10, FrameName(0),
"R1", "1")
ret = SapModel.FrameObj.AddByCoord(0, 0, 10, 8, 0, 16,
FrameName(1), "R1", "2")
ret = SapModel.FrameObj.AddByCoord(-4, 0, 10, 0, 0, 10,
FrameName(2), "R1", "3")
'assign point object
restraint at base
ReDim PointName(1)
ReDim Restraint(5)
For i = 0 To 3
Restraint(i) = True
Next i
For i = 4 To 5
Restraint(i) = False
Next i
ret = SapModel.FrameObj.GetPoints(FrameName(0), PointName(0),
PointName(1))
ret = SapModel.PointObj.SetRestraint(PointName(0), Restraint)
'assign point object
restraint at top
For i = 0 To 1
Restraint(i) = True
Next i
For i = 2 To 5
Restraint(i) = False
Next i
ret = SapModel.FrameObj.GetPoints(FrameName(1), PointName(0),
PointName(1))
ret = SapModel.PointObj.SetRestraint(PointName(1), Restraint)
'refresh view, update
(initialize) zoom
ret = SapModel.View.RefreshView(0, False)
'add load patterns
ret = SapModel.LoadPatterns.Add("1", LTYPE_OTHER, 1)
ret = SapModel.LoadPatterns.Add("2", LTYPE_OTHER)
ret = SapModel.LoadPatterns.Add("3", LTYPE_OTHER)
ret = SapModel.LoadPatterns.Add("4", LTYPE_OTHER)
ret = SapModel.LoadPatterns.Add("5", LTYPE_OTHER)
ret = SapModel.LoadPatterns.Add("6", LTYPE_OTHER)
ret = SapModel.LoadPatterns.Add("7", LTYPE_OTHER)
'assign loading for load
pattern 2
ret = SapModel.FrameObj.GetPoints(FrameName(2), PointName(0),
PointName(1))
ReDim PointLoadValue(5)
PointLoadValue(2) = -10
ret = SapModel.PointObj.SetLoadForce(PointName(0), "2",
PointLoadValue)
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(2), "2", 1,
10, 0, 1, 1.8, 1.8)
'assign loading for load
pattern 3
ret = SapModel.FrameObj.GetPoints(FrameName(2), PointName(0),
PointName(1))
ReDim PointLoadValue(5)
PointLoadValue(2) = -17.2
PointLoadValue(4) = -54.4
ret = SapModel.PointObj.SetLoadForce(PointName(1), "3",
PointLoadValue)
'assign loading for load
pattern 4
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(1), "4", 1,
11, 0, 1, 2, 2)
'assign loading for load
pattern 5
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(0), "5", 1, 2,
0, 1, 2, 2, "Local")
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(1), "5", 1, 2,
0, 1, -2, -2, "Local")
'assign loading for load
pattern 6
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(0), "6", 1, 2,
0, 1, 0.9984, 0.3744, "Local")
ret = SapModel.FrameObj.SetLoadDistributed(FrameName(1), "6", 1, 2,
0, 1, -0.3744, 0, "Local")
'assign loading for load
pattern 7
ret = SapModel.FrameObj.SetLoadPoint(FrameName(1), "7", 1, 2, 0.5,
-15, "Local")
'switch to k-in
units
ret = SapModel.SetPresentUnits(kip_in_F)
'save model
ret = SapModel.File.Save("C:\SapAPI\API_1-001.sdb")
'run model (this will
create the analysis model)
ret = SapModel.Analyze.RunAnalysis
'initialize for Sap2000
results
ReDim SapResult(6)
ret = SapModel.FrameObj.GetPoints(FrameName(1), PointName(0),
PointName(1))
'get Sap2000 results for
load patterns 1 through 7
For i = 0 To 6
ret =
SapModel.Results.Setup.DeselectAllCasesAndCombosForOutput
ret = SapModel.Results.Setup.SetCaseSelectedForOutput(Format(i +
1))
If i <= 3 Then
ret = SapModel.Results.JointDispl(PointName(1), ObjectElm,
NumberResults, Obj, Elm, LoadCase, StepType, StepNum, U1, U2, U3,
R1, R2, R3)
SapResult(i) = U3(0)
Else
ret = SapModel.Results.JointDispl(PointName(0), ObjectElm,
NumberResults, Obj, Elm, LoadCase, StepType, StepNum, U1, U2, U3,
R1, R2, R3)
SapResult(i) = U1(0)
End If
Next i
'close Sap2000
SapObject.ApplicationExit False
Set SapModel = Nothing
Set SapObject = Nothing
'fill Sap2000 result strings
ReDim SapResultString(6)
For i = 0 To 6
SapResultString(i) = Format(SapResult(i), "0.00000")
If Left(SapResultString(i), 1) <> "-" Then
SapResultString(i) = " " & SapResultString(i)
End If
Next i
'fill independent results
(hand calculated)
ReDim IndResult(6)
ReDim IndResultString(6)
IndResult(0) = -0.02639
IndResult(1) = 0.06296
IndResult(2) = 0.06296
IndResult(3) = -0.2963
IndResult(4) = 0.3125
IndResult(5) = 0.11556
IndResult(6) = 0.00651
For i = 0 To 6
IndResultString(i) = Format(IndResult(i), "0.00000")
If Left(IndResultString(i), 1) <> "-" Then
IndResultString(i) = " " & IndResultString(i)
End If
Next i
'fill percent
difference
ReDim PercentDiff(6)
ReDim PercentDiffString(6)
For i = 0 To 6
PercentDiff(i) = (SapResult(i) / IndResult(i)) - 1
PercentDiffString(i) = Format(PercentDiff(i), "0%")
If Left(PercentDiffString(i), 1) <> "-" Then
PercentDiffString(i) = " " & PercentDiffString(i)
End If
Next i
'display message box
comparing results
msg = ""
msg = msg & "LC Sap2000
Independent %Diff" & vbCr & vbLf
For i = 0 To 5
msg = msg & Format(i + 1) & " " & SapResultString(i)
& " " & IndResultString(i) & " " &
PercentDiffString(i) & vbCr & vbLf
Next i
msg = msg & Format(i + 1) & " " & SapResultString(i)
& " " & IndResultString(i) & " " &
PercentDiffString(i)
MsgBox msg
End Sub
粘贴后的代码窗口如下图。
这时代码会自动启动SAP2000，并自动建立一个简单的模型，如下图所示。
OK, that’s it! Enjoy!
加载中，请稍候......
加载中…
(2015-11-11 07:07:49)