Modify the Spreadsheet Exported from RequisitePro 2007

用RequisitePro 2007做需求文档的跟踪tracking, 会遇到一个问题。无论是trace to还是trace from， 都要将制作好的view导出到word中。但是，RequistePro 导出后的数据林乱繁杂，例如，下表。并不满足要求。

SSDD -- CI Traceability Matrix  (condensed)

 

SSDD1: Scope

CI2.1.3.2 (F)

CI2.10.10.1.1 (F)

SSDD1.2: DCT Overview

CI1.1 (F)

CI1.6.5 (F)

SSDD3: Operational Concepts

CI1.6.2 (F)

CI2.1 (F)

SSDD3.2: Mission

CI2.4.6.1 (F)

 

本人先将一个view导出到excel，然后写了一个小程序将不需要的字符删除掉。

1.       第一段，删除“：”后面不需要的标题内容

Dim strColumnLabel As String

Dim nPos As Integer

 

Sub DeleteFromColon()

 

    strColumnLabel = ""

 

    Cells.Range("A1:A100").Select

    strColumnLabel = ""

   

    Do While ActiveCell.Row <> 100

        ActiveCell.Offset(1, 0).Activate

        strColumnLabel = ActiveCell.Value

       

        nPos = InStr(1, strColumnLabel, ":")

        If nPos > 0 Then

          strColumnLabel = Left(strColumnLabel, nPos - 1)

          ActiveCell.Value = strColumnLabel

        End If

    Loop

End Sub

 

2.       压缩选定的column内容到一个column中，并删除不必要的字符，例如(F)

Sub crashColumnsIntoOne()

 

    Dim nCol, nRow As Integer

    nCol = 1

   nRow = 1

    Do While nRow <= Selection.Rows.Count

        strColumnLabel = ""

        Do While nCol <= Selection.Columns.Count

            If Len(Selection.Cells(nRow, nCol).Value) > 0 Then

                If Len(strColumnLabel) > 0 Then

                    strColumnLabel = strColumnLabel & "," & Selection.Cells(nRow, nCol).Value

                Else

                    strColumnLabel = strColumnLabel & Selection.Cells(nRow, nCol).Value

                End If

                strColumnLabel = Replace(strColumnLabel, "(F)", "")

            End If

            nCol = nCol + 1

        Loop

        Selection.Cells(nRow, 1).Value = strColumnLabel

        nCol = 1

        nRow = nRow + 1

    Loop

End Sub