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.