VB script for excel Yes / No

Data stored in ResultsFile_19_Dec.xls

S.No      Category      TestID      Description      TestStatus      ExecutionStartTime
1      TestCat1      TESTID1      TESTIDDESC1      Fail      [19 Dec 2013 7:33:2]
2      TestCat1      TESTID2      TESTIDDESC2      Fail      [19 Dec 2013 7:33:2]
3      TestCat1      TESTID3      TESTIDDESC3      Pass      [19 Dec 2013 7:33:2]
4      TestCat2      RESTID1      RESTIDDESC1      Fail      [19 Dec 2013 3:22:4]
5      TestCat2      RESTID2      RESTIDDESC2      Fail      [19 Dec 2013 3:22:4]
6      TestCat2      RESTID3      RESTIDDESC3      Pass      [19 Dec 2013 3:22:4]
7      TestCat3      ROMANS1      ROMANSTESTIDDESC3      Fail      [19 Dec 2013 3:22:4]
8      TestCat3      ROMANS2      ROMANSTESTIDDESC4      Fail      [19 Dec 2013 3:22:4]
9      TestCat3      ROMANS3      ROMANSTESTIDDESC5      Fail      [19 Dec 2013 3:22:4]
10      TestCat3      ROMANS4      ROMANSTESTIDDESC6      Pass      [19 Dec 2013 3:22:4]
11      TestCat3      ROMANS5      ROMANSTESTIDDESC7      Fail      [19 Dec 2013 3:22:4]
12      TestCat4      TESTID4      TESTIDDESC4      Fail      [19 Dec 2013 3:22:4]
13      TestCat5      TESTID5      TESTIDDESC5      Fail      [19 Dec 2013 3:22:4]

Attaching a file as mentioned above
Excel.zip

There is something probably wrong in your description: "2. Similarly for Category TestCat2 , search for TestCat2.xls in "folder" and for all the fail , convert "execute" to pass" - do you want to change the TestStatus in Results_File*?

Where should the VBA code reside (if used)? Or are you trying to do that exclusively via VBS? Note: Because of having to use Office Automation, VBS isn't that fast with accessing Excel cells and performing database lookups.

BTW, the Results file isn't XLS, it is XLSX. That might be an issue when trying to open it in Excel via Automation.

Hi,

below are the answers for your questions:
=========


There is something probably wrong in your description: "2. Similarly for Category TestCat2 , search for TestCat2.xls in "folder" and for all the fail , convert "execute" to pass" - do you want to change the TestStatus in Results_File*?
Answer>> Yes, It was a typo mistake, sorry about it.
I need to change the column "execute" to "Yes" in "TestCat2.xls"


Where should the VBA code reside (if used)? Or are you trying to do that exclusively via VBS? Note: Because of having to use Office Automation, VBS isn't that fast with accessing Excel cells and performing database lookups.
Answer>> I need this exclusively via VBS. Thanks for your suggestion that  this process will not fast but I can wait till the time the process is performing(I hope the process will take around 10 to 15 mins)


BTW, the Results file isn't XLS, it is XLSX. That might be an issue when trying to open it in Excel via Automation.
Answer>> I have re-cehcked the excel versions and both are in xls
Folder\TestCat1.xls
19_Dec\ResultsFile_19_Dec.xls

In a summary ,  ResultsFile_19_Dec.xls is a sheet that contains all my results with status pass/ fail. I need to change the status to No for all Pass case and Yes to all Fail case so that I can execute all my test fail cases.

In a summary ,  ResultsFile_19_Dec.xls is a sheet that contains all my results with status pass/ fail. I need to change the status to No for all Pass case and Yes to all Fail case so that I can execute all my test fail cases.
That is a change. You need to have all rows changes in the category files?

For better performance and effectivity, which of the files contain the most rows? E.g. if the Results file contains only 100 out of 10000 tests, we can perform searches in that, but if there is a 1:1 relation (each category and test ID will be found in both files), it's different.

there is a 1:1 relationship.

Means open  ResultsFile_19_Dec.xls check the Category if its is "TestCat1" then open "TestCat1.xls" and mark it as Yes if "Test Status" is fail in ResultsFile_19_Dec.xls
and in case "Test Status" is Pass in ResultsFile_19_Dec.xls then mark the "Execute" as No in "TestCat1.xls"

Similar process need to be done for all other "TestId" and Category Present in  ResultsFile_19_Dec.xls

But Final change is required in "TestCat1.xls" , "TestCat2.xls" etc

I hope I am clear ....

Thanks

@ Qlemo. Any suggestions

The following code works for me. The VBS file needs to be stored in the folder "Excel" of your testcase, and called with the relative path and filename of the Results XLS file, like

cscript //nologo c:\Temp\EE\Excel\EvalTestResults.VBS "19_Dec\ResultsFile_19_Dec.xls"

Select allOpen in new window

Since VBS does not allow for debugging, and the script is very fragile in regard of unexpected failures, we might have to switch to VBA for debugging purposes. Some (comment) parts of the code are from my VBA test.

Option Explicit

Sub Test()
Rem Code requires sorted XLS files
Rem   Results:  Category, TestID
Rem   Test*:    TestID
Rem as we do a manual search ("sort-merge-join") to improve performance.

Dim excel
Dim fileResults, wbResults, wsResults, rgRow, Cat, TestID
Dim fileCat, wbCat, wsCat, rgRowCat, strExec
Dim root

root = Left(WScript.ScriptFullName, Len(WScript.ScriptFullName) - Len(WScript.ScriptName))
fileResults = root & WScript.Arguments.Item(0)
Set excel = WScript.CreateObject("Excel.Application")

' root = "C:\Temp\EE\Excel\"
' fileResults = root & "19_Dec\ResultsFile_19_Dec.xls"
' Set excel = Application

excel.Visible = True

Set wbResults = excel.Workbooks.Open(fileResults)
Set wsResults = wbResults.Worksheets("Summary")

Cat = ""
For Each rgRow In wsResults.Rows
  If rgRow.Row > 1 Then
    If Cat <> rgRow.Range("B1").Value Then
      Rem other test category => other file, so need to close the old one.
      If Cat <> "" Then
        wbCat.Save
        wbCat.Close 0
        Set rgRowCat = Nothing
      End If
      Cat = rgRow.Range("B1").Value
      If Cat = "" Then Exit For
      fileCat = root & "folder\" & Cat & ".xls"
      Set wbCat = excel.Workbooks.Open(fileCat)
      Set wsCat = wbCat.Worksheets(1) ' "TestDriver"
      Set rgRowCat = wsCat.Range("A2").EntireRow
    End If
    TestID = rgRow.Range("C1")
  
    Do Until rgRowCat.Range("A1").Value = TestID
      Set rgRowCat = rgRowCat.Offset(1, 0)
      If rgRowCat.Range("A1").Value = "" Then Exit Do
    Loop
    If rgRowCat.Range("A1").Value = "" Then
      WScript.Echo "Testcase " & TestID & " not found in category file for " & Cat
      ' Debug.Print "Testcase " & TestID & " not found in category file for " & Cat
    Else
      strExec = "No": If rgRow.Range("E1").Value = "Fail" Then strExec = "Yes"
      rgRowCat.Range("C1") = strExec
    End If
  End If
Next

wbResults.Close 0
excel.Quit
Set excel = Nothing

End Sub

Test

Select allOpen in new window

Please also note the first comment lines.

@ Qlemo, Thank you so much.

I tried and it is working but when I executed this with more data it not able to recognize the test case id in the "folder" excel files
I m trying to understand code and once again thanks for your help

thank you very much its working