Wrong count for UsedRange rows and columns in datatable.

[qtpexpert]

Hi guys,

I have a problem in exporting the testcasefile to other sheet in Datatable

Issue is:

In Datatable 3 sheets(Global,Subdriver,Report)are there.
When i execute the script qtp will import the testcase file to 'subdriver' sheet.
here it is taking correct used range row numbers and column numbers.
Before execution I formatted testcasefile.

After execution qtp will export this 'subdriver' sheet to 'Report' sheet, and it will write pass fail column then it will create a testReport file in one specific folder.

after that it will take the used range row numbers and column numbers for setting the colors for pass,Fail values.But it is giving wrong number of rows and columns.

Example:

TestReport file having 10 rows,9 columns.But it is giving 256 columns and 65563 rows.
due to this reason during script execution it is not coming out from the loop.

I am using below mentioned code.

Code:

Public Function fnResColor(testReport)
    Set xlApp=CreateObject("Excel.Application")
    Set xlBook=xlApp.WorkBooks.Open(testReport)
    Set xlSheet=xlBook.Sheets("Report")
    Col_Cnt=xlSheet.UsedRange.columns.count
    msgbox Col_Cnt
    Row_Cnt=xlSheet.UsedRange.rows.count
    msgbox Row_Cnt
    For i=1 to Row_Cnt Step 1
        Val=xlSheet.Cells(i,9).Value
        If Val="Pass" Then
            xlSheet.cells(i,9).Font.ColorIndex=10
            'xlSheet.cells(i,9).interior.Color=vbGreen
        Elseif Val="Fail" Then
            xlSheet.cells(i,9).Font.ColorIndex=3
        Elseif Val="PassFail" Then
            xlSheet.cells(i,9).Font.ColorIndex=1

            'xlSheet.cells(i,9).interior.Color=vbRed
        End If
    xlBook.Save
    xlBook.Close
    Set xlSheet=Nothing
    Set xlBook=Nothing
    Set xlApp=Nothing
End Function

How can we improve the performance?

If anyone knows the solution pls reply.

Thanks in advance!

[SteveS]

Is your TestReport file saved as an xlsx file, if so try saving it as an xls file instead.

[qtpexpert]

Thanks for your solution.
My testcase file is xls file and TestReport file also saved as xls file.
pls reply if you have any other solution.