在Office 2010的Excel中使用SAP2000 v15的OAPI

2015-11-11 07:07阅读：

http://blog.sina.cn/dpool/blog/u/1402712231

在Office 2010的Excel中使用SAP2000 v15的OAPI
作者：余承飞（酷鱼，QQ: 1931045538） 2015.11.11
酷鱼的博客: http://blog.sina.com.cn/xcoolfish
1 前言
在2009年我在博客写了一篇《SAP2000 API入门指南（三）在Excel中使用SAP2000 API》，当时用的是Office 2003、2007和SAP2000 v12。现在工具都更新了，我再以Office 2010中的Excel和SAP2000 v15为例介绍一下。
2 启用Excel的开发工具
首先要启用Excel的开发工具。打开Excel，选择菜单“文件”=>“选项”，如下图。

在Office <wbr>2010的Excel中使用SAP2000 <wbr>v15的OAPI

在“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

粘贴后的代码窗口如下图。