Thursday, March 7, 2013

Convert an Excel file into XMLfile for QTP Environment Variables

Hi All,

Here I'm giving a sample code which converts an Excel file into xml file which contains environment variable values in the format of excel in a single row for QTP usage.

If you want to change entire excel file into xml. you can use the below code as a reference.

Note: This will not work in VBScript. QTP is must for below code, because I've used XMLUtil object to create XML file.

even you can use methods available on excel COM object to save excel file as XML file. But, some times I've observed that wasn't working fine. So, I've come up of with below procedure.

‘’* Convert Excel file into xml file for QTP Environment Variables

Option Explicit
Dim gstrDtPat,  xlapp, mybook, cc, xmlDoc, i, xmlRoot, newNode, val, Nam
gstrDtPath = "Path of your excel file have environment values in single row"

''* Convert an Excel file into XMLfile
set xlapp = CreateObject("Excel.Application")
set mybook = xlapp.Workbooks.Open(gstrDtPath)
set cc = mybook.Sheets("Sheet1")
'msgbox cc.usedrange.columns.count
'The code given below will create the specified XML at runtime
Set xmlDoc = XMLUtil.CreateXML
' Create a document with Environment as the root node
xmlDoc.CreateDocument "WFN_EnvironmentVariables"
For i = 1 to cc.usedrange.columns.count
                Set xmlRoot = xmlDoc.GetRootElement()
                'Add a new variable node
                xmlRoot.AddChildElementByName "Variable", ""
                Set newNode = xmlRoot.ChildElements()
                Set newNode = newNode.item(newNode.count)
                'Add the name and value child nodes
                val = Cstr(cc.cells(2, i).value)
                Nam = Cstr(cc.cells(1,i).value)
                newNode.AddChildElementByName "Name", Nam
                newNode.AddChildElementByName "Value", val
                xmlDoc.SaveFile "Path to save XML"&"\"&"EnvironmentVariables.xml"

set newNode = Nothing
Set xmlRoot = Nothing
Set xmlDoc = Nothing
mybook.Close True
Set mybook = Nothing
Set xlapp = Nothing

"A good threat is worth a thousand tests" - Boris Beizer

1 comment:

Vishal said...

Thanks for the code. I have one question.

How to deal with Environment variables .

Could you send or show us an example to convert excel to xml file using environment variables. my email id is

There was an error in this gadget