Importing requirements from Excel

[EXploringEA]

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

[potterm]

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.

[potterm]

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

[EXploringEA]

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.  

Once again thanks

Regards
Adrian

[potterm]

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.