QTP tutorial for beginner – great and easy excel function to import all of your data sheets!

Hi. Happy Friday once again! I have an amazing function for you today! This function saves you loads of time and coding! It imports all of your excel data sheets quickly into your script by simply adding the path of your excel sheet. Let's get started!

1. Copy and paste the two functions below into your QTP/UFT script


Function fn_importAllSheetsFromExcelIntoDataTable(strWorkbookPath)
Dim intSheetCount,strSheetName,arrSheetName()
Dim i
Set excelApp = CreateObject("Excel.Application")
excelApp.Workbooks.Open strWorkbookPath
intSheetCount = excelApp.Worksheets.Count
'Loop through the excel sheet and get all of the sheet names
For i = 1 To intSheetCount
ReDim Preserve arrSheetName(i)
arrSheetName(i) = excelApp.Worksheets(i).Name
Next
'now we must clear the excel object and release it so that we can import with QTP
excelApp.Workbooks.Close
excelApp.Quit
Set excelApp = Nothing
fn_kill_Executable "Excel.exe"
For i = 1 To UBound(arrSheetName)
DataTable.AddSheet arrSheetName(i)
DataTable.ImportSheet strWorkbookPath,arrSheetName(i),arrSheetName(i)
Next
End Function

Public Sub fn_kill_Executable(Exe_Name)
Dim strComputer,objWMIService,colProcessList,objProcess
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = '"&Exe_Name&"'")
For Each objProcess In colProcessList
On Error Resume Next
objProcess.Terminate()
On Error GoTo 0
Next
Set colProcessList = Nothing
Set objWMIService = Nothing
End Sub


2. Now call the function and enter the path where your excel sheet is located. Make sure to include the excel workbook name and extenstion at the end of the path. See example below:

importAllSheetsFromExcelIntoDataTable "C:\FolderName1\ExcelBookName.xlsx"

3. Now that your script ran, within the results report, click on the data tab in the lower right hand corner, and you should see your imported data! Amazing right!

Stay tuned for more amazing tips and tricks!! Please leave your feedback and comments below!

Pin It on Pinterest

Clef two-factor authentication