Solved

VB script for excel Yes / No

Posted on 2013-12-28
12
265 Views
Last Modified: 2014-01-05
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
0
Comment
Question by:theology
  • 7
  • 4
12 Comments
 

Author Comment

by:theology
ID: 39743521
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]
0
 

Author Comment

by:theology
ID: 39743523
Attaching a file as mentioned above
Excel.zip
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39743560
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.
0
 

Author Comment

by:theology
ID: 39743591
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.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39743602
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:theology
ID: 39743637
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
0
 

Author Comment

by:theology
ID: 39744687
@ Qlemo. Any suggestions
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39744853
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.
0
 

Author Comment

by:theology
ID: 39746251
@ 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
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39746278
The "folder" Excel files need to be exactly named as the test category - line 37 & 39.
The test IDs need to be exact matches. Even additional trailing spaces can be an issue.

If you can't get it work, create a new Excel workbook, open VBA editor (Alt-F11), and paste the code above into the ThisWorkbook module. Comment lines 14-16, and uncomment 18-20.
Then comment line 51, and uncomment 52.
Last, comment line 61 and 66.

Lines 18 and 19 need to be adapted to your test case, of course.

After all that, position the cursor within the code, and press F8 to step thru the code.
0
 

Author Closing Comment

by:theology
ID: 39757192
thank you very much its working
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now