How to import and export sheets from Quality Center using DataTable of QTP ?
I came across this problem recently when we decided to store our excel files in test plan module of Quality Center(Version 10.0.).
Joining the automation team while the project is already ON keeps you limited in your approaches. If Automation is being carried out using QTP and Quality Center with a framework, which is not flexible It limits the approaches further.
In my project, There were loads of reusable Actions , in which data was being pulled from excel sheets and we were using "DataTable.ImportSheet" for the task. Excel files were on client filesystem BUT now we wanted to keep our data at a central location so that it is created, managed and maintained better. QC was on everybody's mind as It was already being used by other testing teams as central repository. Till Date QC does not have any mechanism which can be used to take care of test data in easier and better ways. Atleast to me it is not better. I am not a very big fan of QTP and QC.
We wanted to reflect minimum changes, for data needs, in our scripts. I wish QTP could support over-ridingImportSheet method of DataTable using RegisterUserFunc. Which could solve the problem without going inside scripts and replacing DataTable.ImportSheet with search and replace option. However, We do not have that option.
I am posting the approach taken by me (May not be the best one) so that it helps you if you are also stuck on the same problem.
Importing Data tables inside QTP is quite simple using the following line of code.
DataTable.ImportSheet "C:\Full\File\path\FileName.xls" _
,"SourceSheetName","DestinationSheetName"
'OR
'Add C:\Full\File\path to folder options in QTP Options.
DataTable.ImportSheet pathfinder.locate("FileName.xls") _
,"SourceSheetName","DestinationSheetName"
If you refer the documentation provided in QTP,you will find that the Ist parameter to ImportSheet method can be a filesystem path or Quality Center Path.
Following the docs In File-->Settings-->resources if you would choose a path for importing data table using the file explorer and point it to the file stored in attachments of a test stored in Quality Center the path would be something like
[Quality Center] \Subject\path\to\yourtest\FileName.xls
We decided to go for OTA API and felt relieved that it could be accessed using set QCConn = QCUtil.QCConnection inside QTP's IDE.
Following is the algorithm, which we used.
I am posting the code below for your use and review.
Public Function DataTableImportSheetFromQC(QTPTest,sFileName,SheetSource,SheetDest)
Dim attachFact,attachList,attachObj,bResult
On Error Resume Next
'Assingments
bResult=False
Set attachFact = QTPTest.Attachments
Set attachList = attachFact.NewList("")
For each attachObj in attachlist
'Ensuring Excel File is stored as attachment to the test
'Naming Convention used by QC is TEST_[TESTID]_[FileName]
If Instr(attachObj.Name,sFileName)>0 And _
Instr(attachObj.Name,TestObject.ID)>0 Then
'Download the attachment to client filesystem
'Import Sheet Method was not working to QCPaths,
'Or We can say that QCPath is the temp path of downloaded file
attachObj.Load True, "" 'the second param is not required in vbscript
'FilName Property of attachObj gives full file path
DataTable.ImportSheet attachObj.FileName,SheetSource,SheetDest
bResult = True
Exit For ' Dont waste time in searching for others
End If
Next
If Err.Number <> 0 Or Not bResult Then
bResult = False
Err.Clear
On Error goto 0
End If
DataTableImportSheetFromQC = bResult
'release the objects
Set attachList = Nothing
Set attachFact = Nothing
End Function
After designing this function we replaced all lines, which used DataTable.ImportSheet statement with a call to this function. Hope it is of some help to you and please post replies if there is any other approach, which you have adopted.