TestArchitect Tutorial Part 3: Extending TestArchitect Lesson #11: Creating Excel reports using TAUtilities Creating a summary report in Excel

 Creating the macro in Microsoft Excel

Creating the macro in Microsoft Excel

In this exercise, you will create a macro in Microsoft Excel™ to interact with TestArchitect items.

1. Create an Excel spreadsheet similar to the one described earlier.

   1. Cells $A$1 thru $A$7 and rows 10 thru 18 should be populated just as shown in the picture below. This is static header information which never changes.

   2. The data to be passed to the interface is entered into cells $B$1 to $B$7. This consists, respectively, of the hostname (or IP address) of the desired repository server, the repository server’s port number, repository name, project name, username, password, and test module folder’s TestArchitect explorer tree path.

   3. Create a button, name it Generate, and place it anywhere in the spreadsheet.

      In Excel 2010, a button is created by going to Developer tab > Controls > Insert, then selecting the icon for the button control. (If your Developer tab is invisible, enable it in File > Options > Customize Ribbon.)
      
      

2. Create a new Excel macro, naming it Generate.

   In Excel 2010, go to View > Macros > View Macros, enter Generate as the macro name, and press Create.

3. Assign the macro to the button.

4. In your macro, declare variables to hold the information to be passed to the API.

   Dim g_server As String
   Dim g_serverPort As Long
   Dim g_repository As String
   Dim g_project As String
   Dim g_username As String
   Dim g_password As String
   Dim g_testpath As String
   Dim atestFolder
   Dim testModuleCollection
   Dim flag As Long
   Dim row
   

5. Create a procedure to write the returned values to Excel cells.

   'Write the returned values to Excel cell
   Private Sub WriteCell(row, col, val)
       Cells(row, col) = val
   End Sub
   

6. Create a procedure to highlight an Excel row for better visualization.

   'Highlight Excel row
   Sub SetColorForRow(row, colorIndex)
       LColorCells = "A" & row & ":" & "H" & row
       Range(LColorCells).Interior.colorIndex = colorIndex
   End Sub
   

7. Create a procedure to clear out old data in the Excel worksheet.

   'Clear data on excel sheet that has values to be updated
   Private Sub DeleteRows()
       Dim Last As Integer
       Last = ActiveSheet.UsedRange.Rows.Count
       If Last >= 18 Then
           Range(Cells(Last, "F"), Cells(18, "A")).EntireRow.Delete
       End If
   End Sub
   

8. Create an event that occurs when the Generate button is selected.

   Sub Generate_Click()
       flag = 0
       generateMethod
   End Sub
   

9. Create the generateMethod() procedure to handle the occurred event.

   Private Sub generateMethod()
       'Insert action implementation code here
   End Sub
   

Next, you will create code that implements generateMethod().