Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: EXploringEA on December 18, 2002, 03:28:28 pm
-
Hi
I'm sure I saw something in a thread sometime ago about importing requirements from excel similar but can't seem to find it today.
I have a basic model into which I wish to add a lot of requirements that have been accumulated in an excel spreadsheet. So rather than cut and paste I would like to import them as requirement objects - both to save time & minimise errors!
Once in the model I can then sort them out into packages, etc.
Can it be done using VBA within Excel or do I need to write access code or ???
Any experience / ideas / examples of code to do this - Thanks
-
I posted something a couple of weeks ago about importing around 800 requirements into EA (from Word) using the automation interface (i.e. VBA). I'll see if I can dig up the code....
Cheers,
Martin.
-
Adrian,
Attached is the code I was talking about - sorry it's abit long. It imports requirements from a table held in Word, but I think the same can be acheived quite easily in Excel (or you could copy/paste the data from Excel to Word if necessary).
Our URD had all requirements in a single Word table with the following columns...
- Section Number - our User Requirements are structured hierarchically e.g. 1.1, 1.1.1, 1.1.2 etc.
- Description - self explanatory
- Priority - numeric indication of the importance of the UR. The priority could also be "title", which means that the row doesn't contain a requirement, just a title/section in the requirements hierarchy.
The fact that the requirements are hierarchically numbered was useful because it meant that I could import them into a package hierarchy in EA. I created a package in EA called "User Requirements" and under that another one called "Test URD Import". The code then creates a "URD 1.0" package under that, and sub-packages reflecting the requirements hierarchy as found in the Word table.
If you're requirements aren't hierarchically structured you could probably achieve the same thing with much simpler code - the if-then-elses are all about keeping track of where the code is in the EA package hierarchy as it traverses the word table.
Hope that helps.
Cheers,
Martin.
Sub Import_To_EA()
Dim ReqTable As Table
Dim URNumber As String
Dim URSection As String
Dim URDesc As String
Dim URSectionLevel As Integer
Dim LastSectionLevel As Integer
Dim NewPackageName As String
Dim ParentPackageID As Long
Dim i_Nothing As Integer
Dim ReqTitle As String
Dim ReqNotes As String
' EA Objects
Dim m_Repository As Object
Dim m_Model As Object
Dim m_Package As Object
Dim m_NewPackage As Object
Dim m_CurrPackage As Object
Dim m_NewSubPackage As Object
Dim m_Requirement As Object
' Obtain a reference to the EA automation interface
Set m_Repository = CreateObject("EA.Repository")
m_Repository.OpenFile ("C:\MyModel.EAP")
' Locate a reference to the word table that contains the requirements (4th table in the doc)
Set ReqTable = ActiveDocument.Tables(4)
' Get reference to URD Import package
Set m_Model = m_Repository.Models.GetByName("Views")
Set m_Package = m_Model.Packages.GetByName("User Requirements") _
.Packages.GetByName("Test URD Import")
' Create a new package in the model that will receive the URs
Set m_NewPackage = m_Package.Packages.AddNew("URD 1.0", "Package")
If Not m_NewPackage.Update() Then
MsgBox m_NewPackage.GetLastError()
End If
' Refresh the packages collection to reflect the addition
i_Nothing = m_Package.Packages.Refresh()
' Traverse the Word requirements table, adding packages/requirements into the model
' Possible bug in EA - you need to find the package you just created, otherwise you get errors when creating sub-packages from it...
Set m_CurrPackage = m_Package.Packages.GetByName("URD 1.0")
LastSectionLevel = 0
For RowNum = 2 To ReqTable.Rows.Count
URNumber = ReqTable.Cell(RowNum, 1).Range.Text
URNumber = Left$(URNumber, Len(URNumber) - 2)
URSection = ReqTable.Cell(RowNum, 2).Range.ListFormat.ListString
URSectionLevel = ReqTable.Cell(RowNum, 2).Range.ListFormat.ListLevelNumber
URDesc = ReqTable.Cell(RowNum, 3).Range.Text
URDesc = Left$(URDesc, Len(URDesc) - 2)
URDesc = Replace(URDesc, Chr(13), Chr(13) & Chr(10))
URPriority = ReqTable.Cell(RowNum, 4).Range.Text
URPriority = Left$(URPriority, Len(URPriority) - 2)
' If the requirement is a title, create a new package to hold the requirements
' within that section. If the section level is higher than the current package,
' the new one should be created at the level of the parent package.
If URPriority = "Title" Then
' Requirement is a new title/section
'
' Package Name is section number + requirement text
NewPackageName = URSection & " - " & URDesc
If LastSectionLevel > URSectionLevel Then
' New section is above current package - so need to move up
' one or more levels in the package hierarchy
'
For Count = 1 To (LastSectionLevel - URSectionLevel + 1)
ParentPackageID = m_CurrPackage.ParentID
Set m_CurrPackage = m_Repository.GetPackageByID(ParentPackageID)
Next
ElseIf LastSectionLevel = URSectionLevel Then
' New section is same level as current package - so use the parent
' of the current package
'
ParentPackageID = m_CurrPackage.ParentID
Set m_CurrPackage = m_Repository.GetPackageByID(ParentPackageID)
End If
Set m_NewSubPackage = m_CurrPackage.Packages.AddNew(NewPackageName, _
"Package")
If Not m_NewSubPackage.Update() Then
MsgBox m_NewSubPackage.GetLastError()
End If
' Refresh the packages collection to reflect the addition
i_Nothing = m_CurrPackage.Packages.Refresh()
' Remember the section level we got to...
LastSectionLevel = URSectionLevel
Set m_CurrPackage = m_CurrPackage.Packages.GetByName(NewPackageName)
Else
' Entry is a requirement - add a requirement entry to the current package
'
ReqNotes = URNumber & " (" & URSection & ") - " & URDesc
' Object titles are restricted to 255 chars, and don't really want more than 100 chars anyway, so truncate
If Len(ReqNotes) > 100 Then
ReqTitle = Left$(ReqNotes, 100) & "..."
Else
ReqTitle = ReqNotes
End If
' Add new requirement to EA
Set m_Requirement = m_CurrPackage.Elements.AddNew(ReqTitle, "Requirement")
m_Requirement.Notes = ReqNotes
If Not m_Requirement.Update() Then
MsgBox m_Requirement.GetLastError()
End If
' Refresh the packages collection to reflect the addition
i_Nothing = m_CurrPackage.Elements.Refresh()
End If
Next
End Sub
-
Hi Martin
Thanks for the code and explanation - I'll give it go - probably be tomorrow now. :)
Its great to have a working example with which to start; there is no substitue for real code to get upto speed quiclky and obtain a practical insight on how the interface really works.
BTW: Are you (or anyone) aware of any other material in addition to the EA HTML pages on the Automation Interface? I think its a really good plus for a tool like EA when it provides a usable interface with which to bridge the gap to other worlds. 8)
Once again thanks
Regards
Adrian
-
Hi Adrian,
I'm not aware of other material apart from what Sparx provides. Much of what I wrote was based on perusing the HTML docs that you can download from Sparx.
Unfortunately the EA automation interface is late binding, which means that the usual VBA pop-dialogs telling you which methods/properties to use are not available, so its to some extent trial and error.
Good luck.
regards,
Martin.