Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 14th, 2011, 02:40 PM
Registered User
 
Join Date: May 2011
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default XML parsing

I have the following xml file:


<?xml version="1.0" encoding="UTF-16"?>
<Interest-List xmlns:i="http://www.tfs.com/common/interests:i">
<Interest>
<Date>18 Jun 2010 18:30:07</Date>
<Broker>BHKP PIX $</Broker>
<Term>6M</Term>
<Best-Bid>810</Best-Bid>
<Best-Offer>885</Best-Offer>
</Interest>
<Interest>
<Date>18 Jun 2010 18:30:07</Date>
<Broker>BHKP PIX $</Broker>
<Term>1Y</Term>
<Best-Bid>730</Best-Bid>
<Best-Offer>805</Best-Offer>
</Interest>
<Interest>
<Date>18 Jun 2010 18:30:07</Date>
<Broker>BHKP PIX $</Broker>
<Term>2Y</Term>
<Best-Bid>695</Best-Bid>
<Best-Offer>750</Best-Offer>
</Interest>
</Interest-List>


I want to get the child nodes of the <Interest> tag and populate a spreadsheet.

I use the following vba code:


oXML1.Load "C:\Transforms\Prices.xml"
Set oXMLNodes = oXML1.SelectNodes("/Interest-List/Interest")
Debug.Print oXMLNodes.Length

For Each oXMLNode In oXMLNodes
For Each oXMLChildNode In oXMLNode.ChildNodes
If cols <= oXMLNode.ChildNodes.Length Then
ActiveSheet.Cells(rows, cols).Value = oXMLChildNode.Text
cols = cols + 1
End If
Next
cols = 1
rows = rows + 1
Next


I don't know where I am going wrong. I need to get the values of each "<Interests>" node in a row of the spreadsheet.

Debug.Print oXMLNodes.Length is always '0' as opposed to some integer.

Kindly help.
 
Old June 15th, 2011, 09:09 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Can you post the full snippet if possible for us to test.

The snippet throws some errors

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 16th, 2011, 09:09 AM
Registered User
 
Join Date: May 2011
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Hello,

Here is the full code:

Sub GetData()
Dim oXML As DOMDocument
Dim oXSL As DOMDocument
Dim oXML1 As DOMDocument
Dim oXMLNode As IXMLDOMNode
Dim oXMLNodes As IXMLDOMNodeList
Set oXML = New DOMDocument
Set oXML1 = New DOMDocument
Set oXSL = New DOMDocument
Dim rows As Integer
Dim cols As Integer


oXML.Load ("C:\Examples\distr_reports_schedule_t xml.xml")
oXSL.Load ("C:\Transforms\Untitled8.xsl")

Dim fileContent As String
fileContent = oXML.transformNode(oXSL)

Open "C:\Transforms\Prices.xml" For Output As #1
Print #1, fileContent
Close #1

oXML.async = False
oXML.Load ("C:\Transforms\Prices.xml")

rows = 2
cols = 1

Set oXMLNodes = oXML.SelectNodes("/Interest-List/Interest")
Debug.Print oXMLNodes.Length

ActiveSheet.Cells(1, 1).Value = "Date"
ActiveSheet.Cells(1, 2).Value = "Broker"
ActiveSheet.Cells(1, 3).Value = "Term"
ActiveSheet.Cells(1, 4).Value = "Best-Bid"
ActiveSheet.Cells(1, 5).Value = "Best-Offer"


For Each oXMLNode In oXMLNodes
For Each oXMLChildNode In oXMLNode.ChildNodes
If cols <= oXMLNode.ChildNodes.Length Then
ActiveSheet.Cells(rows, cols).Value = oXMLChildNode.Text
cols = cols + 1
End If
Next
cols = 1
rows = rows + 1
Next
End Sub

 
Old June 16th, 2011, 10:11 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi Sai

The namespace attribute was giving me a problem. When I replaced

<Interest-List xmlns:i="http://www.tfs.com/common/interests:i">
with
<Interest-List>

It worked. Thought it might help you a bit. Not sure if you need to set any property to include the namesmace too

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Parsing an Xml with No Xml Declaration Zoheb XML 2 March 26th, 2011 05:31 AM
XML::Simple parsing xml geek.shrek Perl 1 August 19th, 2009 10:32 PM
parsing xml kri_hegde XML 5 July 24th, 2007 11:37 AM
XML parsing denzil_cactus Perl 0 June 11th, 2007 02:34 AM
XML Parsing tgopal .NET Web Services 1 June 15th, 2004 03:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.