VBScript Code - Function to convert CSV file into excel and viceversa in QTP using VBScript

We at times are required to convert excel files into csv to read as flat files and sometime require to convert a csv file into excel file to use excel features on the data.

 
Below function shows how to convert an csv file into excel file and vice versa. We can also convert to other formats based on constants

Here constant value 23 is used to create a csv file and constant -4143 to save a file as xls file.Once the destination file is created, we can delete the source file as shown below. In case of any issue in understanding the code, please add in comment section

 Call func_ConversionCSVExcel("E:\Test.csv", "E:\Test_converted.xls", "csvtoexcel")  
 Public Function func_ConversionCSVExcel(strSrcFile, strDestFile, Conversion)  
 on error resume next  
 Set objExcel = CreateObject("Excel.application")  
 set objExcelBook = objExcel.Workbooks.Open(strSrcFile)  
 objExcel.application.visible=false  
 objExcel.application.displayalerts=false  
 If(Conversion = "ExceltoCSV") Then  
   objExcelBook.SaveAs strDestFile, 23  
 else  
   objExcel.ActiveWorkbook.SaveAs strDestFile,-4143  
 End If  
 objExcel.Application.Quit  
 objExcel.Quit    
 Set objExcel = Nothing  
 set objExcelBook = Nothing  
 Set objFSO = CreateObject("scripting.FileSystemObject")  
 objFSO.DeleteFile(strSrcFile)  
 Set objFSO =nothing  
 End Function  

How to load multiple function libraries at runtime from Quality Center in QTP

This post explains how to load multiple libraries at runtime from Quality Center in a QTP test. As the test size grows, it is better to associate or load function library at runtime with all the tests. 

Note we should associate an initialization library with each of the test and then perform following work in the initialization library. 

• Load all the function library with the test

• Load the shared object Repository with the test.

• Remove the local object repository with the test to avoid object conflict

• Load the environment variables to be used across test

• Close all instances of application.

• Providing the reporter configuration for the test. e.g: allowing reporting in QTP Reporter on scenario of error only

In the current post, we will focus only on how to load multiple libraries at runtime from Quality Center or local folder.

In the case of using local folder structure , the path [QualityCenter] Subject\Project_Name \......   changes to c:\QTP_Automation/......

 Const strLibraryNames = "[QualityCenter] Subject\Project_Name\QTP\Libraries\Library1.vbs>[QualityCenter] Subject\Project_Name\QTP\Libraries\Library2.vbs>[QualityCenter] Subject\Project_Name\QTP\Libraries\Library3.vbs>[QualityCenter] Subject\Project_Name\QTP\Libraries\Library4.vbs"  
 callfunc_LoadFunctionLibrary(strLibraryNames)  
 Function func_LoadFunctionLibrary(strLibraryName)  
  ''Load multiple Library Files seperated by '>'  
  collLibrary = split(strLibraryName, ">")  
    ''Now collLibrary is an array with each element in array storing the path of library  
  For i = 0 to ubound(collLibrary)  
  LoadFunctionLibrary collLibrary(i)  
  Next   
 End Function  

How to create and work with Class using VBScript Code in QTP

Using Class in VBScript, we are able to create an instance of object and use properties and methods of the object. 

Points to remember with VBScript Class concept:

• We can define variables, methods, properties, and event members in the class.

• All the members of a class can be declared as Private or Public.

• Members declared as Private are visible within the Class block. 

• Members declared as Public are accessible from within and outside the class.

• Members are by default Public. If we don't provide member as Private or Public

Syntax of Class Statement in VBScript

Class ClassName

       Statement of Code

End Class

Understanding Implementation of class in VBScript with an example

'' Here we have defined a class with name as VBSriptClass

Class VBScriptClass

''Once we have created a class, next we need to define the members of the VBScript.

''We have taken all the types of members in this example

'' A variable can be defined as Private or Public 

Private var_ClassName 
Public var_Name

''Class Events - Class_Initialize and Class_Terminate are events which we can define in a class

''We can define the action which we need to perform when we instantiate a class in the class_initialize sub as shown below.

Private sub Class_Initialize(  )
msgbox "We have initialised the test. This pop up will appear the moment new instance of class object is created"
End Sub

''When Object is Set to Nothing/destroyed, Class_Terminate event is executed and actions are performed as defined in the sub. 

Private Sub Class_Terminate(  )
msgbox "We have terminated the test. This pop up will appear the moment instance of class object is destroyed/set as nothing"
End Sub

'' Property Let and Get allows to set and extract values from properties in the class

''get method allows to extract value set for property in the class

Public Property Get ClassName
       ClassName = var_ClassName
End Property 

''Let method allows to set value for the Property

Public Property Let ClassName (strClassName) 
         var_ClassName = strClassName 
End Property 

''We can define function in the class. the below example will sum two numbers

''Only Public functions can be used in code outside of the class 

public function sum(a,b)
        sum = a+b
        addition a,b
End Function

''Private method cannot be called outside the class but can be called by another function within the class

Private function addition(a,b)
       msgbox a*b
End function

''A sub/function which is not defined as either Private/Public can be assesed outside the class and works as public method

Sub DisplayUserName 
      msgbox UserName 
End Sub 
End Class 

Using Class members outside the class code

''We can define an instance or object of class as set in below code

Set objClass = New VBScriptClass

''Once the instance of the clas is created, we can used the public members of the class as shown below.

'' Set value of the property of the method.

objClass.ClassName  = "Are we talking about Dance Classes?"
msgbox objClass.sum(12,15)
msgbox objClass.ClassName

'' Close the instance of the class object

Set objClass = nothing