Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.
Reply With Quote
  #2 (permalink)  
Old May 13th, 2011, 08:30 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
The Following User Says Thank You to disel2010 For This Useful Post:
saikrishnan7 (May 13th, 2011)
  #3 (permalink)  
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?
Reply With Quote
  #4 (permalink)  
Old May 13th, 2011, 01:58 PM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
The Following User Says Thank You to disel2010 For This Useful Post:
saikrishnan7 (May 18th, 2011)
  #5 (permalink)  
Old May 15th, 2011, 03:47 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
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)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:33 PM.


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