Testing Excel Reports through QTP

[rajeshvelur]

Hi,
Anyone know about how to test excel reports?
My application generates run time excel reports.
I would like to validate some cells(B1, C2, etc), and look for certain strings like "#parsing" in the report.
Ankur, i have even emailed you regarding the same, sorry for doing that.
So posting it in the forum now.

Thanks
velur

[Ankur]

Please let us know what have you tried?

[rajeshvelur]

Hi Ankur,

I have now got my script to work like this:
I can scan through the excel from A1 to AA200 (this can be extended)
I can now look for particular error messages (like a string value)

The code is:

Code:

Set appExcel = CreateObject ("Excel.Application")
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open ("Q:\Results\Test.xls")  'opens the sheet
Set objSheet = appExcel.Sheets("Annual Balance Sheet")  ' To select particular sheet
wait(10)

Services.StartTransaction "check_errors"
'check for the error "#parsing error"
With objSheet.Range("A1", "AA200") ' select the used range in particular sheet

For each c in objSheet.Range("A1", "AA200")   ' Loop through the used range
    With c
     .Find ("#parsing error")' data to find  
     .Find ("@NA")' data to find  
If c="#parsing error"  then ' compare with the expected data
        c.Interior.ColorIndex = 32 'make the color if it finds the data
           print  "#parsing error found in this report"
        reporter.ReportEvent micFail, CheckForError, "#parsing error was found"
elseif c = "@NA" then
        c.Interior.ColorIndex = 30 'make the gray color if it finds the data
        print  "@NA error found in this report"
    reporter.ReportEvent micFail, CheckForError, "@NA error was found"
    End If
        Set c = .FindNext( c )   ' next search
    End with
Next
End With

Services.EndTransaction "check_errors"
objWorkBook.save
objWorkBook.close
window("Report_Excel_Window").close
set appExcel=nothing

Now instead of giving a particular name to the sheet ("Annual balance Sheet"- 4th line of the code) i want to make it more generic (like a .*)

Can i be able to use the regular expression?
I tried , but could not make it work.


Thanks
Velur