The below code would allow you to select a file and open that file and check the number of passed items in Sheet3 of the workbook and write the results to a new sheet....
Dim strFile As String
Private Sub CommandButton1_Click()
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Excel Workbooks", "*.xl*", 1
.InitialFileName = "C:\"
.Show
strFile = .SelectedItems(1)
End With
End Sub
Private Sub CommandButton2_Click()
Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
Set wb = Workbooks.Open(strFile)
Set ws1 = wb.Sheets("Sheet3")
Set ws2 = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
ws2.Cells(1, 1) = "No Of Test Cases"
ws2.Cells(1, 2) = "No Of Passed Test Cases"
ws2.Range("A2") = Application.Sum(ws1.Columns(1))
ws2.Range("B2") = Application.CountIf(ws1.Range("E:E"), "Passed")
End Sub