VBScript code to convert Excel data to HTML format

This article explains the code to convert content of an excel file into html code. This can be useful for better representation of excel data in html format.

Code to convert Excel data to HTML format

''strWbk - Full path of the excel workbook
''strWsheetName - Name of the worksheet
'' strHTMLFile - Name of the html file with path
Public Function CreateHTMLFromExcel(strWbk,strWsheetName,strHTMLFile)
Set oExcel = Createobject("Excel.Application")
oExcel.visible = false
Set objExcelWB = oExcel.Workbooks.Open(strWbkPath)
Set objExcelWS = objExcelWB.Worksheets(strWsheetName)
'Getting the rows and column count
 strColumnCount = objExcelWS.UsedRange.Columns.Count
 strTotRows = objExcelWS.UsedRange.Rows.Count
strTable = "<table border=""""2"""">"
'Create html table based on content of the excel file
 For j=1 to strTotRows
  strTable = strTable & "<tr>"
  For i=1 to strColumnCount
    strData = Trim(objExcelWS.Cells(j,i))
    strTable= strTable & "<td>"& strData &"</td>"
  Next
  strTable =strTable & "</tr>"
 Next
 strTable = strTable & "</table>"
 set objFSO=Createobject("scripting.FileSystemObject")
 set objtxt = objFSO.createTextFile(strHTMLFile)
    objtxt.write(strTable)
'Closing the workbook
 objExcelWB.Close
 set objFSO =nothing
End Function

Code to get title of all open browsers in QTP/UFT

We can get titles of all the open browser open using descriptive programming in QTP as explained in the code below:

Function GettitleAllbrowsers()
GettitleAllbrowsers = ""
Set objBrowser = Description.Create
objBrowser("micclass").Value = "Browser"
''Get all browsers instances in ObjBrowserLst 
Set objBrowserLst= Desktop.ChildObjects(objBrowser)
''Get the count of all the browsers open
browsercnt = objBrowserLst.count
For i=0 to objBrowserLst.count-1  
'Store title of the Page in a variable 
GettitleAllbrowsers = GettitleAllbrowsers + ">"+objBrowserLst(i).GetROProperty("title") 
Next 
Set objBrowser = nothing
Set objBrowser = nothing
End Function

For further manipulating the browser details using descriptive programming. Please go through the below articles:

Code to close multiple browsers except Quality Center/ALM in UFT/QTP?

How to close the IE browsers and Internet Explorer not responding windows forecefully -VBScript

Useful Batch file commands

Batch files help user to execute series of commands in the batch file. We can execute large number of commands in one go using bat file.

Some useful batch commands:

@echo off – does not echo the text post the command and only displayes the execution result

@echo on the text post the command and only displays the execution result

Pause- the batch file execution is halted until further intervention of user by pressing keys.

CALL - calling one batch file from another batch file

cd -  Change directory

cls - clean the window

copy -copy files from source to destination

DIR - List of directory and files

ERASE - delete files

FC - Compares files
 
IF - Conditional statement

mkdir - Make a new directory/folder

move - move files or folder

ping - Check TCP/IP connection to a remote IP address

rmdir - remove folder/directory

SC - Managing services

set - manipulate environment variable 

taskkill - kills an active process

taslist - List active processes

REM - remark statement

Arguments %1….. – This is the first argument to be provided from the

 command prompt as parameter for the batch command.

Reference: http://www.robvanderwoude.com/batchcommands.php

Starting with batch files:

Let us take a simple example of using batch file to display Create a new file with extension as .bat. Suppose we need to compare two text files. The content of batch file for this will look like

@echo off

Fc %1 %2 /n>%3

Pause

To execute this bat file, we have to write in command prompt  as:

D:\new.bat d:\testsrc.txt d:\testdest.txt d:\testlog.txt

Renaming all/specific subfolders in a folder using vbscript FSO

In this post, how to rename all the subfolders in a folder using vbscript is explained

• Renaming all sub folders in a folder removing spaces from the file

call RemoveblankSpace_Subfolders("D:\TextTest")

 ''strbasefoldername - Folder Path in which file needs to be replaced
 Public Function RemoveblankSpace_Subfolders(strbasefoldername)
 On error resume next
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject") 
    Dim objFolder 
    For Each objSubFolder In objFSO.GetFolder(strbasefoldername).SubFolders
     FlderName = objSubFolder.Name
  FlderName = replace(FlderName," ","")
  objSubFolder.Name = FlderName
    Next 
    Set objSubFolderFolder = Nothing 
    Set objFSO = Nothing 
End Function

• Rename specific subfolder in a folder using vbscript

call Rename_Subfolders("D:\TextTest","es","twist",false,"test")

'' strbasefoldername - Folder Path in which file needs to be replaced
'' strFindtext - text in the folder name that needs to be replaced
'' strReplaceText - new text to replace the existing test
'' boolAllFlders - boolean flag to indicate if all the subfolder needs to be renamed or

'' specific folders needs to be renamed
'' OnlyfoldersWithText - replace only folder with specific text. will consider only if

'' boolAllFlders =true
 Public Function Rename_Subfolders(strbasefoldername,strFindtext,strReplaceText,boolAllFlders,OnlyfoldersWithText)
 On error resume next
 Dim objFSO
 Set objFSO = CreateObject("Scripting.FileSystemObject") 
    Dim objFolder
    For Each objSubFolder In objFSO.GetFolder(strbasefoldername).SubFolders
 FlderName = objSubFolder.Name
  if boolAllFlders = false then
   if(instr(1,FlderName,OnlyfoldersWithText)>0) then
    FlderName = replace(FlderName,strFindtext,strReplaceText)
    objSubFolder.Name = FlderName
   End If
  else
    FlderName = replace(FlderName,strFindtext,strReplaceText)
    objSubFolder.Name = FlderName
  End If
    Next 
    Set objSubFolderFolder = Nothing 
    Set objFSO = Nothing 
End Function

UFT test information and failure reason from result.xml file

While running the UFT test in batch,we need to capture executed test information including testname, test execution status and reason for failure if any in the test execution. 
Below code in vbscript can be used to extract required information from results file (result.xml) in UFT and parsing the information in xml file using MSXML.DomDocument and Microsoft.XMLDOM

xmlFilePath = "c:\Results.xml"

Set xmlDoc = CreateObject("MSXML.DomDocument")
If xmlDoc.Load (xmlFilePath) Then
'Get the test name
 Set objNode = xmlDoc.GetElementsByTagName("DName")
 .TestName = objNode(0).Text
 Set objNode = xmlDoc.GetElementsByTagName("Summary")

 'Last Node with tagName "Summary" has details of Pass/Fail status
 Set Node = objNode(objNode.Length - 1)

 intStepsPassed = Node.getAttribute("passed")
 intStepsWarning = Node.getAttribute("warning")

 ''in case of failures get the testfailureReason
 If Cint(intStepsFailed) > 0 Then
  strFailureReason = getFailureReason(xmlFilePath)
 End if
 Set FSO = Nothing
 Set xmlDoc = Nothing
End If

Public function getFailureReason(strFileName)
 Set xmlDoc = CreateObject("Microsoft.XMLDOM")
 xmlDoc.async = False
 strErrorMsg = ""
 booleanxmlParse = xmlDoc.load(strFileName)
 if(booleanxmlParse) then
  xmlDoc.setProperty "SelectionLanguage", "XPath"
  set xmlDoc_node = xmlDoc.selectNodes("//Step//NodeArgs[@status='failed']")
  for each node in xmlDoc_node
   strNode = node.parentNode.getattribute("rID")
   set TCNode = xmlDoc.selectSingleNode("//Step[@rID='" + strNode + "']/Details")
   strErrorMsg=strErrorMsg & TCNode.text
  Next
 End If
 getFailureReason = strErrorMsg 
End Function

Manipulation XML using XPath for VBScript/UFT

This article explains how to extract information from an xml file based on the element xpath using Microsoft.XMLDOM and VBSript. You can try using the code as shown below.

''---------------------------------------------------------------------------------
'' Step 1 : Copy the content removing comments in a file and save the file
''----------------------------------------------------------------------------------

'' --------------File Content ------------------------------------------------------
''XMLTextFile = "<books><name>mybook</name><price>50</price><Author rating="high">matt</Author>
''<name>his book</name><price>150</price><Author rating="low">henry</Author>
''<name>twist</name><price>550</price><Author rating="high">henry</Author></books>"

''-------------Call the functions with Parameters------------------------------------
msgbox getXMLTextFileValue("d:\testdta.xml","//price")
msgbox getXMLTextFileValue("d:\testdta.xml","//Author[@rating='high']")
msgbox getxmlAttributeValue("d:\testdta.xml","//Author","rating")
call getAllNodeValues("d:\testdta.xml","//Author")
''-----------------------------------------------------------------------------------

'' Function 1 - Reading the node text
''Arguments - FileName and xpath of node

''-----------------------------------------------------------------------------------
function getXMLTextFileValue(XMLTextFile,NodeXpath)
Set objXML = CreateObject("Microsoft.XMLDOM")

objXML.async = False
objXML.resolveExternals = False
objXML.validateOnParse = False
getXMLTextFileValue = ""
boolxmlParse = objXML.load(XMLTextFile)
if(boolxmlParse) then
 objXML.setProperty "SelectionLanguage", "XPath"
 set objXML_node = objXML.selectSingleNode(NodeXpath)
        getXMLTextFileValue = objXML_node.text
 else
 msgbox "issue in xml file"
End If
End Function

''-----------------------------------------------------------------------------------

'' Function 1 - Reading the node attribute value
''Arguments - FileName and xpath of node. attribute Name of which value is to be returned

''-----------------------------------------------------------------------------------


function getxmlAttributeValue(XMLTextFile,NodeXpath, attributeName)
Set objXML = CreateObject("Microsoft.XMLDOM")

objXML.async = False
objXML.resolveExternals = False
objXML.validateOnParse = False
getxmlAttributeValue = ""
boolxmlParse = objXML.load(XMLTextFile)
if(boolxmlParse) then
 objXML.setProperty "SelectionLanguage", "XPath"
 set objXML_node = objXML.selectSingleNode(NodeXpath)
        getxmlAttributeValue = objXML_node.getAttribute(attributeName)
 else
 msgbox "issue in xml file"
End If
End Function

''-----------------------------------------------------------------------------------

'' Function 1 - Reading the value of all the matching nodes
''Arguments - FileName and xpath of node.

''-----------------------------------------------------------------------------------

function getAllNodeValues(XMLTextFile,NodeXpath)
Set objXML = CreateObject("Microsoft.XMLDOM")

objXML.async = False
objXML.resolveExternals = False
objXML.validateOnParse = False

boolxmlParse = objXML.load(XMLTextFile)
if(boolxmlParse) then
 objXML.setProperty "SelectionLanguage", "XPath"
 set objXML_node = objXML.selectNodes(NodeXpath)
        for each nodet in objXML_node
  msgbox nodet.text
 Next
End If
End Function

VBSript Code: copying text from HTML webPage to Local

In this article, Code to copy html content from a web html page to local machine is explained. You can try the code, saving it as vbs file and running. 

''Create a file using scripting.fileSystemObject
Set objFSO=CreateObject("Scripting.FileSystemObject")
''Provide the Path of html file
testfilePath="d:/testing.html"
'' Create html file using filesystem object
Set objFile = objFSO.CreateTextFile(testfilePath,True)

''Define the Page URL from which html is to be extracted
WebURL="http://seleniumbites.blogspot.in/2016/12/install-jenkins-as-windows-service.html"
Set httpcontent = CreateObject("Microsoft.XmlHttp")

On Error Resume Next
httpcontent.open "GET", URL, False
httpcontent.send ""
if err.Number = 0 Then
     objFile.Write httpcontent.responseText  
Else
     msgbox err.description
End If
Set httpcontent = Nothing
objFile.Close

Sorting excel data multiple times based on column Name using VBScript

''Input Information - Workbook file Name with path
''WorkSheet Name
'' Provide the sort criteria based on which data needs to be filtered seperated by |

WorkBookFile = "C:\\testing.xls"
SheetName = "testing"
MultipleSortCriteria = "TestName|TestClass|TestPath"
'' Define the constants to be used in the script
  Const xlYes = 1
  Const xlAscending = 1
  Const xlDescending = 2
  Set oXL = CreateObject("Excel.Application")
  Set oWB = oXL.WorkBooks.Open(WorkBookFile)
  Set oWS = oWB.Worksheets(SheetName)  
  Set oRnge = oWS.UsedRange
  ColCnt = oRnge.columns.count
  '' Create an array based on the sort criteria provided 
  aSrtCriteria = split(MultipleSortCriteria,"|")
  For i = ubound(aSrtCriteria) to 0 step -1
  ''Loop the times data needs to be sorted
   For j = 1 to ColCount step 1
    
    If (oWS.cells(1,j).value =aSrtCriteria(i)) Then
     '' Sort the data based on the column name
     Set oRngeSrt = oXL.Range(Chr(asc("A")- 1+j) & "1")
     oRnge.Sort oRngeSrt, xlDescending, , , , , , xlYes 
     oWB.save
      Exit For
    End If    
   Next
  Next
   oWB.Close
   oXL.Quit
   Set oWB = Nothing
   Set oXL = Nothing
 

VBScript Code to close the IE browsers and Internet Explorer not responding window

• VBScript Code to close the IE browsers and Internet Explorer not responding window

Set WShell = CreateObject("WScript.Shell")
''This will close all the instances of task iexplore.exe.
''Parameter /f kills the process forcefully
WShell.Exec("taskkill /fi ""imagename eq iexplore.exe"" /f")
WScript.Sleep 100

''At time Internet explorer is not responding error is displayed
''The task has window title as internet explorer not responding.
''We can close the task based on window title with regular expression as Internet *
WShell.Exec("taskkill /fi ""WINDOWTITLE eq Internet*"" /f")
WScript.Sleep 100

''Once the window is closed, another window appear with similar title

'' which needs to be closed
WShell.Exec("taskkill /fi ""WINDOWTITLE eq Internet*"" /f")

• VBScript Code to save the list of running tasks and save it in file abc.txt

''Below command will return the service for the process and save it in file abc.txt

  Set WShell = CreateObject("WScript.Shell")

''This will close all the instances of task iexplore.exe.
''Parameter /f kills the process forcefully

WShell.Exec("tasklist /fi /svc>test.txt")

VBA Code- Extracting Database data into excel sheet using macro

Code to read the data from database and writing data in excel file using VB Macro and VBA.

'''' Naming the sub as auto_open will trigger the sub automatically on opening the file.

'''' We can create a sub with different name and assign macro to a control in the excel

''''file 

Sub auto_open()
'''' define the connection string to connect to the database, it can be a dsn created,

'''' or connection string to create to the database. Please refer connectionstrings.com
'''' to connect to different database using connection string.
connStr = "dsn=testthedb''
'''' Query to fetch data from the database
strGetEmployeeDetails = "select * from employee order by UserName asc;"
''''Provide reference of sheet to add data to in the current workbook. We can add a

''''sheet in excel, or refer to an external excel file/Sheet using excel.application.

''''In this example, Sheet Employee_Details exist in the current excel file
Set worksht = ThisWorkbook.Sheets("Employee_Details")
''''--------------------------
'''' Create database connection
   Set Conn = CreateObject("ADODB.Connection")
   Set rset = CreateObject("ADODB.Recordset")
   Conn.Open connStr
   rset.Open strGetEmployeeDetails, Conn, adOpenStatic

''''The recordset is stored in rset.
''''The first row of data is the column details of the query results

''''and is added in the results header

For i = 0 To rset.Fields.Count - 1
worksht.Cells(2, i + 2) = rset.Fields(i).Name
Next i

''''Copy the recorset data into excel file starting at row 3 and column 2
worksht.Cells(3, 2).CopyFromRecordset rset
rset.Close
Set rset = Nothing
ThisWorkbook.Save
Conn.Close
Set Conn = Nothing
End Sub

VBA Code: How to auto trigger operation on opening or closing an excel file

Problem : We need to perform or auto-trigger some operation while opening or closing an excel file. There may be operations required by excel user like connecting to database and auto populating the data from database  on opening the excel file and generating pivot tables and charts automatically based on database data. Similarly it can be any other event that user want to perform on opening excel file(saved in macro-enabled xlsm file format)

How to create a module in excel VBA?

Open the VBA editor by clicking Alt + F11 or go to Developer tab and click on Visual basic. Below image shows how to create a sub or function in VBA. Following are the steps for creating a sub or function in VBA:

1. Click on Developer

2. Click on Visual basic icon. (The above two steps can be achieved by clicking on Alt + F11.

3. In the workbook, add new module.

4. In the module, add sub as shown below.

How to create a sub that will be auto-triggered on opening the excel file?

Create a sub with name as auto_open as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file opens.

 Sub auto_open()  
   MsgBox "Show message while opening the excel file"  
 End Sub  

How to create a sub that will be auto-triggered on closing the excel file?

Create a sub with name as auto_close as shown in the code below. Try writing a small code as shown below which will display a message box when the excel file closes.

 Sub auto_close()  
   MsgBox "Show message while closing the excel file"  
 End Sub  

Save the excel file in .xlsm format. 

How to compare arrays after sort data in Array using VBScript in QTP

In this article, we will discuss how to work with arrays explaining the below concepts:

• How to sort data in the array.

• How to compare two arrays.

• How to view elements in an array

'''''''''''''''''''''''''''''''''''''''''''''''''Test Data for the example''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
arrayDataA = Array(43,54,65,76,87)
arrayDataB = Array(87,65,54,43,7)


'''''''''''''''''''''''''''''''''''''''''''''''''Calling the functions for arrays''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
call funcArrayComparision(arrayDataA, arrayDataB)
arrayData = sortArray(arrayDataA)
ViewsortArray(arrayDataA)

'''''''''''''''''''''''''''''''''''''''''''''''''Function to sort data in an array'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

public function sortArray(arrayData)
For i = LBound(arrayData) to UBound(arrayData)
  For j = LBound(arrayData) to UBound(arrayData)
    If j <> UBound(arrayData) Then
      If arrayData(j) > arrayData(j + 1) Then
         TempValue = arrayData(j + 1)
         arrayData(j + 1) = arrayData(j)
         arrayData(j) = TempValue
      End If
    End If
  Next
Next
sortArray = arrayData
End Function


'''''''''''''''''''''''''''''''''''''''''''''''''Function to view data in an array'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

public function ViewsortArray(arrayData)
For i = LBound(arrayData) to UBound(arrayData)
 msgbox arrayData(i)
Next
End Function


'''''''''''''''''''''''''''''''''''''''''''''''''Function to compare two arrays'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function funcArrayComparision(arrayA, arrayB)
''Firstly we will check whether the size of array matches, In case, It does not match, we can conclude values do not match 
   If ubound(arrayA) <> ubound(arrayB) Then
msgbox ("The array size is not same, so it is not possible array will match")
   else
   ''Now first of all we need to sort the array content, since we are compring array value by value, we are sorting the data without taking as/desc order in focus
  arrayA = sortArray(arrayA)
  msgbox ("Sorted data in the ArrayA")
  arrayB = sortArray(arrayB)
msgbox ("Sorted data in the ArrayB")
  boolflag = 0
For i = 0 to ubound(arrayA)-1
If arrayA(i) = arrayB(i) Then
msgbox arrayb(i)
boolflag = 1
Exit For
End If
Next
If (boolflag > 0) Then
msgbox ("congratulation ! array content in both the files match")
else
msgbox ("array content in both the files does not match")
End If
End If
End Function

What is the difference between ByRef and ByVal? –QTP Interview question

ByRef and ByVal are two ways to pass arguments to a function or subroutine in VBScript. Understanding the difference between two is simple.

Let us start with ByVal. Suppose a function or subroutine has an argument which accepts a variable with byval,suppose a variable x is passed as argument to the function, The value of variable does not change once the function is executed successfully.

Now let us move to ByRef. ByRef means passing value as a reference. Suppose a function or subroutine has an argument which accepts a variable with byref, suppose variable x is passed as argument to the function, The value of variable changes once the function is executed successfully.

Writing the below function in a notepad and running as a vbscript will help to differentiate ByVal and ByRef

By Ref Code:

Dim x

x= 5

call squareofNumber(x)

msgbox x

Function squareofNumber(ByRef varx)

        varx = varx*varx

        msgbox varx

End function

ByVal Code:

Dim x

x= 5

call squareofNumber(x)

msgbox x

Function squareofNumber(Byval varx)

        varx = varx*varx

        msgbox varx

End function

 By default, if we do not provide ByVal or Byref, it takes the argument as ByRef.

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 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

How to use err object to continue test execution in VBSript and QTP

''Err object holds the information for last runtime error that occurred in the test execution

'' In the piece of code, we can add on error resume next to continue with execution . It will not abort the execution but will store the information of the error.

'' Once a new runtime error occur, in that case , the err object stores the information of new error. So handle must be taken to add reporting in test and clearing the error

''Methods for err object are err.raise and err.clear

'' On error Resume next will continue with execution. try to comment the On error resume next , and then see what happens in case on error resume next statement is not provided.

On Error Resume Next 

'' Err.raise <number> will generate err object for err number provided. At runtime as an error is encountered, err object populates the information of error. 

Err.Raise 9err_number= Err.number 

'' Using err.description, we can extract information about the error

err_description= Err.description 

'' In case no error is encountered in the execution , the err. number has number as 0. Therefore we can use If else loop based on value of err.num and report to the QTP Report.

If numerr <> 0 Then 

 ''Reporter.Reportevent ........

msgbox (numerr & " Some error in application")

Else

''Reporter.reportevent ........

End If

'' Once we have validated a piece of code, we should clear the err using err.clear

err.clear

msgbox err.number

''you will see msgbox will display value as 0 now. 

How to create array from string separated by delimiter using vbscript split function

Suppose we have a string with value as : stringA = "nitin>joshi>qa>qtp>automation"

'' We can create an array using split function

Strarray = Split(StringA, ">")
'' we can loop through the array element using:

 intPropSet = Ubound(Strarray)  ''this gives the length or upper bound index of array.

  for i = 0 to intPropSet

      msgbox strarray(i)

  Next

''This can be useful to loop in validating multiple values ''e.g : Validating existence of multiple links with name as nitin or joshi or qtp or automation

''We can combine the values in an array using join statement

If (IsArray(Strarray)) then

   stringB = Join(Strarray, ">")

   msgbox "array with text: " + stringB

Else

   msgbox "not an array"

End If

So while understanding How to create array from string separated by delimiter using vbscript split function, we have now understanding of Join, split, IsArray,Ubound and lbound also