How to Call VBS File (VB Library) from script in QTP

When a function is called in QTP , there is a need to add VB Library or VBS file before calling that function.From the following command that Library or VBS File can be called through QTP script.


ExecuteFile("C:\Script\Function_Any.vbs")

This command load the VB Library named Function_Any.vbs and then all the function written in that file can be called in QTP.(This is tested in QTP 9.0)

How to Read Text File data from QTP

We can create File System Object for the text file and read the desired text from that file.Here is the function to perform that :


Function ReadText(Filepath,Row,ToSkip,ToRead)
Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(Filepath,1,false,0)

'For Reaching the desired ROW
If Row <> 1 Then
For i =1 to (Row-1)
tso.SkipLine
Next
End If

'Skip charecters as per required
tso.Skip(ToSkip)

'Read charecters as per required
ReadText = tso.Read(ToRead)

Set tso = Nothing
Set fso = Nothing

End Function

Call to function :
ReadText("C:\Test\TextFile.txt",3,4,5)

This function return the 5 charecters of the 3rd row after skipping 4 charecters of the File present at give path.

For example text file is as like this :

This is line 1.
We are in the second line of this file.
Welcome here is the third line of the text file we will end here we can go forward too.

Function will return a value = ome h

How to Read CSV (Text) File data from QTP

We can read CSV (Text) File data by ceating File System Object.Here is the function to perfrom that:


Function ReadCSV(Filepath,Row,Occurrence)

Set fso = CreateObject("Scripting.FileSystemObject")
Set tso = fso.OpenTextFile(Filepath,1,false,0)

'For Reaching the desired ROW
If Row <> 1 Then
For i =1 to (Row-1)
tso.SkipLine
Next
End If

'Capturing the entire desired ROW and its width
RequiredLine = tso.ReadLine
Width = tso.Column

z=1
'Loop for finding start and end position
For i=1 to (Occurrence+1)
y = Instr(z, RequiredLine , "," , 1)

'Special handling if the comma is last
If y = 0 Then
y = Width
End If

'Capturing Start Position
If i = Occurrence Then
StartPosition=y+1
End If

z=y+1

Next

'Special Handling of Condtion of Zero Occourence
If Occurrence = 0 Then
StartPosition = 1
End If

EndPosition = (y-StartPosition)

'The logic to read file without double quotes
ReadCSV = mid(RequiredLine,StartPosition+1,EndPosition-2)

Set tso = Nothing
Set fso = Nothing

End Function


Call to Function :

ReadCSV("C:\Test\CSVFile.txt",4,3)

This function return the data present after 3 occourance of the COMMA in the 4th Row of the CSV file that is present at the path we specified.

For example our file is like this :

"Tag1","Tag2","Tag3","Tag4","Tag5"
"1","12","123","1234","12345"
"a","ab","abc","abcd","abcde"
"x","xy","xyz","xyz1","xyz12"
"a","aa","aaa","aaaa","aaaaa"

We get the output of function = xyz1
Note that in function code itself we removed the double quotes also from data.

How to Write data in Xls Sheet through QTP

We can create Xls object (As that is created in VB) and call that objest as a function.By this we can easily write data in any Xls file placed at any where in our machine (May be on network also).
For this script there is NO need of Object Repository of QTP.
Here is the Function :


Function WriteValueinXls(filepath,sheetname,row,col,texttowrite)

Dim cellvalue1
Dim ExcelApp 'As Excel.Application
Dim NewWorkbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet

Set ExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
ExcelApp.Visible = False
Set NewWorkbook = ExcelApp.Workbooks.Open(filepath)
Set worksheet= NewWorkbook.Sheets(sheetname)

worksheet.Cells(row,col)=texttowrite

NewWorkbook.Save

NewWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Function


Call to Function :

WriteValueinXls "C:\Test\test1.xls","sheet3",2,4,"Sample Text by QTP"


Result of this Call :

This call write the data "Sample Text by QTP" in Second Row and D Column (D means 4th column of Xls).The path of Xls file include the NAME of file also.And the sheet name is also specified.Remember that Xls sheet should not be READ Only.

How to Read Xls Sheet data from QTP

We can create Xls object (As that is created in VB) and call that objest as a function.By this we can easily read any Xls file placed at any where in our machine (May be on network also).
For this script there is NO need of Object Repository of QTP.
Here is the Function :

Function ReadfromXls(absolutepath,sheetname,row,col)

Dim cellvalue1Dim ExcelApp 'As Excel.Application
Dim NewWorkbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet

Set ExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
ExcelApp.Visible = False
Set NewWorkbook = ExcelApp.Workbooks.Open(absolutepath)
Set worksheet= NewWorkbook.Sheets(sheetname)
cellvalue1 = worksheet.Cells(row,col)

ReadfromXls = cellvalue1

NewWorkbook.Close
ExcelApp.Quit
Set ExcelApp = Nothing

End Function


Call to Function :ReadfromXls("C:\Test\test1.xls","sheet3",2,4)


Result of this Call :

This call return the data present in Second Row and D Column (D means 4th column of Xls).
The path of Xls file include the NAME of file also.And the sheet name is also specified.