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

Ordinal Identifier in QTP

While learning the objects, QTP tries to identify objects based on the mandatory and assistive properties of the object. In case the above properties are insufficient to uniquely identify the object. QTP uses the ordinal identifier properties to uniquely identify the object together with the madatory and assistive properties.

There are three type of Ordinal Identifier in QTP which are assigned as numeric integer value for the object based on the order occurrence of object in the browser.

The three types of ordinal identifier are described below:

Ordinal Identifier	Description
Index Based	Based on the object in the window, An index value is assigned to object based on existence in the source code. Index value starts from 0.
Location Based	The Location property is based on location of object, works vertically from top to bottom and then from left to right. value starts from 0.
Creation Time	The Creation Time property is used for browser objects mainly and used to identify multiple browsers based on the time the browser/page was open. Value starts from 0

Properties for object identification in QTP

QTP learns the properties of test objects to uniquely identify the object at run time. There are predefined set of properties for object classes in each environment.

Object are classified as Object classes for each environment. For e.g. environment Standard Windows environment as shown below will have all the test object class defined and defined are the mandatory and assistive properties to identify the object. Also we can set smart identification On for the object and the default ordinal identifier.to identify the object. 

To have a look at Object Identification properties for an object, Navigate to Tools>Object Identifier.

Let us understand the object Identification Window:

1. Environment: Lists all the environment for which objects are defined.

2. Object Classes: List all the controls available for the environment selected.

3. Mandatory Properties: These are the properties QTP always learns for the test object class selected.

4. Assistive Properties: In case object is not uniquely identified using the mandatory properties, there are some assistive properties which are learned to uniquely identify the object.

5. Smart Identification is false by default, but can be checked for identification.

6. Ordinal Identifer: In case object is not identified using the above properties , Ordinal identifier is used to identify the object