In this exercise, you will create a macro in Microsoft Excel™ to
interact with TestArchitect items.
Create an Excel spreadsheet similar to the one described earlier.
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.
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
Create a button, name it Generate, and place it
anywhere in the spreadsheet.
In Excel 2010, a button is created by going to , then selecting the icon for the button control. (If your
Developer tab is invisible, enable it in .)
Create a new Excel macro, naming it Generate.
In Excel 2010, go to , enter Generate as the macro name, and
Assign the macro to the button.
In your macro, declare variables to hold the information to be passed to the
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 flag As Long
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
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
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
Create an event that occurs when the Generate button is
flag = 0
Create the generateMethod() procedure to handle the occurred
Private Sub generateMethod()
'Insert action implementation code here
Next, you will create code that implements generateMethod().