Monday, January 28, 2008

Program in VBscript for XML parser

Friendz,

I have written a program which actually parses XML into Excel(in my application)

the output will be like

Nodename Value Parentnode


Dim xml_file
xml_file = InputBox("Enter xml file")
Call xmltoexcel(xml_file)

Function xmltoexcel(xmlfile)
Dim i
i=1
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set objWorkbook = xlApp.Workbooks.Add("C:\Documents and Settings\BhavanaA\Desktop\VBscript\Book3.xls")
set xmlDoc=CreateObject("Microsoft.XMLDOM")
xmlDoc.load(xmlfile)
For j = 1 To 4
xlApp.Worksheets(1).Cells(1, j).Interior.color = 10092543
Next
xlApp.Range("A" & 1).Value="NODENAME"
xlApp.Range("B" & 1).Value="DATA"
xlApp.Range("C" & 1).Value="PARENTNODE"

xlApp.Worksheets(1).Cells.Range("A1:C1").ColumnWidth = 35
Call displaynode(xmlDoc.documentElement,xlApp,i)
objWorkbook.close
xlApp.quit
End Function

Function displaynode(ByVal node_name,ByRef xlApp,Byref i )
i=i+1
xlApp.Range("A" & i).Value = node_name.nodename
xlApp.Range("B" & i).Value = ""
xlApp.Range("C" & i).Value = node_name.parentnode.nodename
j=0
for each cnt in node_name.childNodes
if cnt.HasChildNodes Then
Call displaynode(cnt,xlApp,i)
Else
If (j>0)Or (j=0 And cnt.nodename<>"#text") Then
i=i+1
xlapp.Range("A" & i).value = cnt.nodename
xlApp.Range("C" & i).Value = cnt.parentnode.nodename
End If
xlApp.Range("B" & i).Value = cnt.text
end If
j=j+1
Next
End Function

No comments: