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

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

How to Use GetElementsBytagName Method in QTP explained with Code

In this post, we will discuss how to use GetElementsbyTagName to input values in a edit box, count number of editbox, links , and count number of rows in a particular table. Please suggest in case user miss anything in the code. We can similarly use GetElementsbyTagName in a variety of purposes and manipulate the html

Code to find number of links in a page.

set objLink = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("a")

intLinks = objLink.length

Code to Identify number of edit box in the page.

set objLink = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("input")

intLinks = objLink.length

Code to input Data in a field using HTML DOM

set objEdit = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("input")

for each editbox in objEdit

       If editbox.classname = <strClassName>

editbox.innertext = <strSetVsalue>

Exit For

     End If

Next

Code to count number of rows in a table

set objTable = Browser("title:=.*","index:=0").Page(title:=.*).Object.GetElementsByTagName("table")

for each Tbl in objEdit

         If Tbl.classname = <strTableClassName>

set objrow = tbl.getelementsbyTagName("tr")

Msgbox objrow.length

        End If

Next

HTML DOM and QTP

HTML DOM is language independent model for representing and interacting with objects in html. It defines the structure for HTML and help in traversing through objects in HTML. QTP supports HTML DOM and following methods are used to identify an object in HTML. This approach can be useful in case QTP is not able to identify the object.

Following are the methods by which we can identify an object using HTML DOM using QTP. We will explain methods used to extract information from HTML DOM and will  use the below html as reference.

<!DOCTYPE html>

<html>

<body>

<p id="para">Hello World!</p>

<p>Paragraph2</p>

<a id=”Link1” name =”LinkName” class=”LinkCls”>LinkTest</a>

<a id=”Link2” name =”LinkName” class=”LinkCls2”>LinkTest2</a>

<a id=”Link3” name =”LinkName” class=”LinkCls3”>LinkTest3</a>

</body>

</html>

GetElementsbyTagName Method

 This methods gives a list of all objects with the specified TagName. For e.g.:  Set objColl =Browser(“…”).Page(“….”).object.getElementsbyTagName(“a”)

This will give the list of all elements in the Page with tag name as “a”. In above sample html code, there are three elements with tag name as a. So objColl will be a collection of three elements.

In this example, we can access the link with class name as LinkTest2, we can use the below function

           Public Function funcGetParticularElem(strObjName)

    boolelemstat = False

   set  objLinkColl=  Browser("…..").Page("title:=.*").Object.getElementsByTagName("a")

  For each objLink in objLinkColl

  If (objLink.classname =strObjName) Then

              objLink.click

              boolelem = True

              Exit For

End If

Next

If (boolelem= True) Then

         Msgbox “Element Found”

Else

       Msgbox “element not found”

End If

GetElementsByName Method

This gives the list of all objects matching the specified name.

For e.g.: Set objColl = Browser(“…”).Page(“….”).object.getElementsbyName(“LinkName”)

This will give the list of all elements in the Page with name as “LinkName”. Similar to above explained example on how to use HTML DOM for GetelementsbyTagName method, we can use the same for getelementsbyname method and extract useful information for the element.

GetElementByID Method

This gives the list of objects with a particular Id . Normally Id’s are unique for each object in the Page, else it picks the first element of the object satisfying the Id.

For e.g: Browser(“…”).Page(“….”).object.getElementbyId(“Link1”).click

This will select the object with Id as “Link1” in the Page and perform the requested operation on the page. Difference between GetElementById and above methods is whereas the above methods give collection of elements. This method gives the element with the Id provided. Also Id defined in the page is unique for

RunScript Method

 This help to execute java script statement in QTP.

For e.g: 

Browser(“…”).Page(“….”).runscript(alert(”Hello World”);)

Once we have the collection of the objects, we can iterate through the collection and compare properties of the object like innerHTML,innerText with the expected value, and perform action on a particular object.

How to identify Object properties in the HTML Page

We can use HTML DOM to extract information from the page and is very useful in case object is not identified by QTP directly.

Suppose there are two buttons with similar properties in a web table. We can use HTML DOM to identify object based on the unique object in the next cell of web Table. To traverse the DOM structure and find properties of every element in the page, there are various inbuilt and external tools based on browsers.In IE and chrome, we can press F12 to view DOM structure and inspect elements in the html Page.