cancel
Showing results for 
Search instead for 
Did you mean: 

Test result status not refreshing

SOLVED
Contributor

Test result status not refreshing

I have this function which I have picked up from a few examples online and managed to get it to write to excel with the result of the test that run. However it seems that either it doesn't refresh after every run or it gets the previous result log. I have created a test to test this function which writes log messages and putting the result in the 3 possible outcomes and everything I run it it puts the precious result to excel... Could someone point me in the right direction as I believe I'm using this method incorrectly.


function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount)

{



var app = Sys.OleObject("Excel.Application");

var book = app.Workbooks.Open(fname);

var sheet = book.Sheets.Item(sheetName);

var rowCount = sheet.UsedRange.Rows.Count+1;

var columnCount = sheet.UsedRange.Columns.Count;



var Logs;

var Count;

var Name;

var Status = null;



// Obtains the object that holds the list of project logs

Logs = Project.Logs;

Count = Logs.LogItemsCount

// Iterates through the list of project logs

for (var i = 0; i < Count; i++)

{

var LogItem = LogsCol.LogItem(i);

Name = Logs.LogItem(i).Name;

Status = Logs.LogItem.Status;

}



var TestResult = Status;





switch (Status)

{

case 0:

TestResult = "Passed"

break;



case 1:

TestResult = "Warnings"

break;



case 2:

TestResult = "Failed"

break;



default :

TestResult = "Error"

break;

}





app.Cells(rowCount,columnCount-5) = (aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"));

app.Cells(rowCount,columnCount-4) = Name;

app.Cells(rowCount,columnCount-3) = TestResult;

app.Cells(rowCount, columnCount-2) = testTime;

app.Cells(rowCount, columnCount-1) = errorCount;

app.Cells(rowCount, columnCount-0) = warnCount;

book.Save();

app.Quit();



}
Tags (1)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Hero

Re: Test result status not refreshing

The reason being is that the current log MIGHT not be saved to disk at the time you go to access the Project.Logs object.   There is a setting to save the log every n minutes but that means that you might still have missing data of up to 60 seconds of time.

 

So... taking your code as you have it, I would add the following (in bold)

 

unction WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount)
{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fname);
var sheet = book.Sheets.Item(sheetName);
var rowCount = sheet.UsedRange.Rows.Count+1;
var columnCount = sheet.UsedRange.Columns.Count;
var Logs;
var Count;
var Name;
var Status = null;

// Obtains the object that holds the list of project logs
Log.SaveToDisk();
Logs = Project.Logs;
Count = Logs.LogItemsCount

// Iterates through the list of project logs

This will flush all log data that is in memory to disk before you start working with it.  


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Community Hero

Re: Test result status not refreshing

So, I have changed some of the lines which are highlighted and added function to get properly enter the data into excel sheet.

function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount){

var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fname);
var sheet = book.Sheets.Item(sheetName);
var rowCount = sheet.UsedRange.Rows.Count+1;
var columnCount = sheet.UsedRange.Columns.Count;

var Logs;
var Count;

// Obtains the object that holds the list of project logs
Logs = Project.Logs;
Count = Logs.LogItemsCount;
var TestResult = Logs.LogItem(Count-1).Status;

switch (TestResult) //previously it is like Status which is undefined
{ case 0: TestResult = "Passed" break; case 1: TestResult = "Warnings" break; case 2: TestResult = "Failed" break; default : TestResult = "Error" break; } var arrayLogData = [aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"), Logs.LogItem(Count-1).Name, TestResult, testTime, errorCount, warnCount] // When you need to add additional data, just add the new element here sheet.Range("A" + rowCount).Value = alignData(arrayLogData[0]); sheet.Range("B" + rowCount).Value = alignData(arrayLogData[1]); sheet.Range("C" + rowCount).Value = alignData(arrayLogData[2]); sheet.Range("D" + rowCount).Value = alignData(arrayLogData[3]); sheet.Range("E" + rowCount).Value = alignData(arrayLogData[4]); sheet.Range("F" + rowCount).Value = alignData(arrayLogData[5]); // If you want add more columns then you can add like below //sheet.Range("G" + inputRow).Value = alignData(arrayLogData[6]); book.Save(); app.Quit(); } function alignData(datatoEnter) { try { var tempValue = aqConvert.VarToStr(datatoEnter); if(tempValue != "") { return tempValue; } else { return ""; } } catch(ex) { return "ERROR FIELD"; } }

Let me know your results.


Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”


Extension Available

18 REPLIES 18
Community Hero

Re: Test result status not refreshing

The reason being is that the current log MIGHT not be saved to disk at the time you go to access the Project.Logs object.   There is a setting to save the log every n minutes but that means that you might still have missing data of up to 60 seconds of time.

 

So... taking your code as you have it, I would add the following (in bold)

 

unction WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount)
{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fname);
var sheet = book.Sheets.Item(sheetName);
var rowCount = sheet.UsedRange.Rows.Count+1;
var columnCount = sheet.UsedRange.Columns.Count;
var Logs;
var Count;
var Name;
var Status = null;

// Obtains the object that holds the list of project logs
Log.SaveToDisk();
Logs = Project.Logs;
Count = Logs.LogItemsCount

// Iterates through the list of project logs

This will flush all log data that is in memory to disk before you start working with it.  


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Test result status not refreshing

Thanks very much tristaanogre, you are the TestComplete Master. You have been a great help with all my posts.


Contributor

Re: Test result status not refreshing

Is there a better way to write out to excel other than my example, the reason being it isn't a great way to write as I have to take off the number of cells to go back to the start position and if I need to add further info in the future it becomes cumbersome and not a great design. I added it at the time as it worked but for a scenario to use at work it will need to be more flexible.

I don't get any intelli sense on the excel specific stuff to try and pick it up which makes it difficult to work with and figure out

 

app.Cells(rowCount,columnCount-5) = (aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"));

app.Cells(rowCount,columnCount-4) = Name;

app.Cells(rowCount,columnCount-3) = TestResult;

app.Cells(rowCount, columnCount-2) = testTime;

app.Cells(rowCount, columnCount-1) = errorCount;

app.Cells(rowCount, columnCount-0) = warnCount;

 

Community Hero

Re: Test result status not refreshing

Well... couple of things:

The data you want to write to Excel, I would store it in an Array rather than individual variables.  The reason being is that you're going to write a "for" loop to loop through the cells in your Excel row and input the data in each cell.

 

If I were to re-write, it looks like this:

function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount){

    var app = Sys.OleObject("Excel.Application");
    var book = app.Workbooks.Open(fname);
    var sheet = book.Sheets.Item(sheetName);
    var rowCount = sheet.UsedRange.Rows.Count+1;
    var columnCount = sheet.UsedRange.Columns.Count;

    var Logs;
    var Count;
    
    // Obtains the object that holds the list of project logs
    Logs = Project.Logs;
    Count = Logs.LogItemsCount;
    var TestResult = Logs.LogItem(Count-1).Status;

    switch (Status) {
        case 0:
            TestResult = "Passed"
            break;

        case 1:
            TestResult = "Warnings"
            break;

        case 2:
            TestResult = "Failed"
            break;

        default :
            TestResult = "Error"
            break;
    }


    var arrayLogData = [aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"), 
                    Logs.LogItem(Count-1).Name,
                    TestResult,
                    testTime,
                    errorCount,
                    warnCount]
    // When you need to add additional data, just add the new element here
    
    for (i = 0; i < columnCount; i++) {
        app.Cells(rowCount, i) = arrayLogData[i]; //This then maps your array of data to yhour column count and sets the values
    }
    book.Save();
    app.Quit();
}

Then, if you need to add more data, the column count automatically follows through.  You can increase the used columns in your Excel sheet before you add the code here to plan ahead.  Then you just need to assign the new data to a new element of your array and the for loop takes over.

I THINK this is a better design... but I'd be willing to bet someone will come along and give even better advice. Smiley Happy


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Test result status not refreshing

This makes a lot more sense, I'm still getting used to programming but picking up as I go along. You have again been a great help and thanks for the example.

 

 

Contributor

Re: Test result status not refreshing

Sorry to keep on.

 

Could I add this as an Event Handler to run over all my tests - if so how would I utilise the parameters for errorcount, testduration etc

Community Hero

Re: Test result status not refreshing

That's a good question.  I'm not 100% certain how to do that...  

 

Log.ErrCount and Log.WrnCount return the error and warning counts posted to the log by the current test item.  so, if you are executing tests with nested test items or multiple test items executing in sequence, this MIGHT not give you what you want natively.  You'll have to build some code to keep track of the project level error count your self (that would be built into the OnLogError event handler as well) and then make that value available to the event handlers (Project variables might work here).

 

As for test duration, again, I'm not finding anything native.  What I would do in that case is, again, write a value to a globally accessible variable with the start date/time stamp for starting the run.  And then, in your event handler code, do the calculation to get the current run duration.

 

 


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

Re: Test result status not refreshing

I attempted to use the function above however i keep getting an error on the last part: "Expected ';'
Error location: Line: 130 Column: 9."

 

Do you have any idea what could be causing this.

 

app.Cells(rowCount, i) = arrayLogData[i]; 
Community Hero

Re: Test result status not refreshing

When you get this error, did you checked whether arrayLogData[i] had data or not?.

 

If the arrayLogData[i] is having null then there might be a error. But please let me know the result above scenario.

 


Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”


Extension Available

New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors