Avatar of theology
theology asked on

VB script for excel Yes / No

I have a folder names as "19_Dec" This folder contains a excel sheet name as "ResultsFile_19_Dec.xls"
This excel contains column name as "Category      TestID      Description      TestStatus"


Now in another folder that is "folder" I have different excel files which is named as similar to Category name (ResultsFile_19_Dec.xls eg. TestCat1,TestCat2 etc)

And the excel files placed in "Folder" contains columns "TestCaseID      TestDescription      Execute"
which are similar to column name in"ResultsFile_19_Dec.xls"

Now I need to create a VB script that will check following:
1. Check column name "Category" from "ResultsFile_19_Dec.xls" for ex "TestCat1" the test status is "fail"  now for this fail check the "Execute" column from "TestCat1.xls" and change No to Yes
2. Similarly for Category TestCat2 , search for TestCat2.xls in "folder" and for all the fail , convert "execute" to pass
Microsoft ExcelVB ScriptScripting LanguagesMicrosoft Office

Avatar of undefined
Last Comment
theology

8/22/2022 - Mon
ASKER
theology

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]
ASKER
theology

Attaching a file as mentioned above
Excel.zip
Qlemo

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
theology

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.
Qlemo

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.
ASKER
theology

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
theology

@ Qlemo. Any suggestions
Qlemo

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"

Open 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

Open in new window

Please also note the first comment lines.
ASKER
theology

@ 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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
theology

thank you very much its working