Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 May 13th, 2011, 07:13 AM
Registered User
 
Join Date: May 2011
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default Extracting Values from XML file

Hello,
I have an xml file. I am having trouble extracting values from the XML file using XMLDOM.

XML file:

<html>
<body>
<table border="0" cellspacing="0" cellpadding="0" width="100%" class="yfnc_datamodoutline1">
<tr valign="top">
<td>-
<table border="0" cellspacing="1" cellpadding="2" width="100%">
<tr>
<th width="16%" class="yfnc_tablehead1" align="right" scope="col">Date</th>
<th width="12%" class="yfnc_tablehead1" align="right" scope="col">Open</th>
<th width="12%" class="yfnc_tablehead1" align="right" scope="col">High</th>
<th width="12%" class="yfnc_tablehead1" align="right" scope="col">Low</th>
<th width="12%" class="yfnc_tablehead1" align="right" scope="col">Close</th>
<th width="16%" class="yfnc_tablehead1" align="right" scope="col">Volume</th>
<th width="15%" class="yfnc_tablehead1" align="right" scope="col">Adj Close* </th></tr>-
<tr>
<td class="yfnc_tabledata1" align="right">May 12, 2011</td>
<td class="yfnc_tabledata1" align="right">12,629.81</td>
<td class="yfnc_tabledata1" align="right">12,746.09</td>
<td class="yfnc_tabledata1" align="right">12,497.97</td>
<td class="yfnc_tabledata1" align="right">12,695.92</td>
<td class="yfnc_tabledata1" align="right">3,777,210,000</td>
<td class="yfnc_tabledata1" align="right">12,695.92</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>


Here is the code I use to get the values of the <th> tag one by one:


oXML.Load ("C:\Users\Sai\Desktop\newstockdata.xml")
Set oXMLNodes = oXML.SelectNodes("/html/body/table/tr/td/table/tr")
For Each oXMLNode In oXMLNodes
Debug.Print oXMLNode.Text
Next


I felt this should print the value of one <th> tag per iteration, but it concatenates the text of all <th> tags and prints it as one string. And in the next iteration, it concatenates the text of all <td> tags of the next <tr> tag and prints it as one string and so on.

I am pasting a sample of the first two iterations below:

1st iteration:DateOpenHighLowCloseVolumeAdj Close*
2nd iteration:May 12, 201112,629.8112,746.0912,497.9712,695.923,777,210, 00012,695.92


Kindly explain where I am going wrong and also point out how I should tweak the above lines of code so that I get to print the text of one <th> tag per iteration.
 
Old May 13th, 2011, 08:30 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

HI,

you should be able to get the childnodes one by one.

Something like this:
Code:
 
For Each oXMLNode In oXMLNodes
	For Each oXMLChildNode in oXMLNode.ChildNodes
		Debug.Print oXMLChildNode.Text
	Next 
Next
Hope this helps
The Following User Says Thank You to disel2010 For This Useful Post:
saikrishnan7 (May 13th, 2011)
 
Old May 13th, 2011, 10:16 AM
Registered User
 
Join Date: May 2011
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Thanks a lot diesel2010. It works awesome.

A small question. How can I copy each output value and populate a spreadsheet? As in html table, every <tr> corresponds to a new row in the spreadsheet. How do I do this? Is there a way in vba to open a new Excel Document on the fly and populate the spreadsheet?
 
Old May 13th, 2011, 01:58 PM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

Hi,

perhaps I'm on the wrong foot here, but if you already have a html file there is no need to start parsing this html file and create an excel file from the parsed elements.. just renaming your html to a file with .xls extension will already do the trick.

But just in case, renaming is not enough for you and you want to start parsing specific parts, this should work in excel vba :
Code:
    ' First your stuff to load the xhtml table in XML DOM here

    ' Create a new Workbook
    Set oBook = Workbooks.Add
    
    ' Load the data in the first worksheet
    With oBook.Worksheets(1)
        Dim i, j As Integer
        i = 0
        For Each oXMLNode In oXMLNodes
            i = i + 1
            j = 0
            For Each oXMLChildNode In oXMLNode.ChildNodes
                j = j + 1
                .Cells(i, j) = oXMLChildNode.Text
            Next
        Next
    End With
    
    oBook.SaveAs Filename:="MyWorkbook.xls"
Hope this helps
The Following User Says Thank You to disel2010 For This Useful Post:
saikrishnan7 (May 18th, 2011)
 
Old May 15th, 2011, 03:47 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Quote:
Originally Posted by saikrishnan7 View Post
Thanks a lot diesel2010. It works awesome.

A small question. How can I copy each output value and populate a spreadsheet? As in html table, every <tr> corresponds to a new row in the spreadsheet. How do I do this? Is there a way in vba to open a new Excel Document on the fly and populate the spreadsheet?
Hi

Just curious to know. Did you ever try opening the XML through Excel. IMHO Excel 2007, 2010 has built in feautres to understand the XML and render it in a workbook

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
how to avoid duplicate values from the xml file us balasundarrao XSLT 2 June 4th, 2014 11:56 PM
Fetching the values from XML file dhrumil BOOK: XSLT 2.0 and XPath 2.0 Programmer's Reference, 4th Edition ISBN: 978-0-470-19274-0 5 March 30th, 2010 07:52 PM
Extracting values from list. rupen Oracle 1 September 29th, 2009 09:16 AM
Extracting subset of XML using XSLT sentah XSLT 3 September 3rd, 2008 02:44 PM
how to pass values from javascript to xml file satish_sgk Javascript How-To 1 November 24th, 2007 07:27 PM





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