How to import all working sheets of an excel file to QTP DataTable: Excel Automation

Hello, friends

This post is meant for QTP learner and who are novice at it. You know that DataTable plays a big role in parameterizing our automation test. We pass arguments to our test script using this dataTable provided by QTP. However, in automation testing of a complex and large scale project we become less dependent on DataTable. Most of our test data is stored in Excel sheet because it gives us a flexibility to modify when needed. We know that to import and excel sheet, we use following commands: 

                               i)DataTable.Import(FilePath)

                               ii) DataTable.ImportSheet "FilePath", "SourceSheet", "DestinationSheet"

If your excel file contains more than one sheet, then none of the above command gives you the ability to import all the sheets at a time. In this case, you have to meet the requirement pragmatically. Sometimes, there is a requirement of using different local sheets at a time during execution. To get ride of such situation or to ease the process, I would like to share the following code snippet with you to achieve that goal. Code is given in Function format for reusability.

   Function ImportAllSheetsToDataTable(FileName)

       Dim objExl, objWBook

       If  Not IsNull(FileName) Then        'Check if the File name has been passed.

    Set objExl=CreateObject("Excel.Application")

    Set objWBook=objExl.Workbooks.Open(FileName,,True) 'Open the excel file

For each oWSheet in objWBook.WorkSheets  'Read all the worksheets

DataTable.AddSheet oWSheet.Name

DataTable.ImportSheet FileName, oWSheet.Name,oWSheet.Name

Next

        Set objWBook = Nothing

    objExl.Quit      'Quit the Application

    Set objExl = Nothing

Else

msgbox "No File name has been passed"

End IF

   End Function

   'To call the Function

   sPath="C:\TestData\TestData_QA.xls"

   ImportAllSheetsToDataTable(sPath)

Hope this code will also help