none
Excel VBA to Parse XML data RRS feed

  • Question

  • Hi All,

    I want to read xml data from xml file saved on my desktop and parse the data and copy it to excel using VBA. Below is the xml data sample.

    <?xml version="1.0"?>
    
    <query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:created="2014-09-17T18:09:21Z" yahoo:lang="en-us"><results><quote symbol="GOOG"><Ask>583.17</Ask><AverageDailyVolume>1541200</AverageDailyVolume><Bid>582.87</Bid><AskRealtime>583.17</AskRealtime><BidRealtime>582.87</BidRealtime><BookValue>141.66</BookValue><Change_PercentChange>+3.30 - +0.57%</Change_PercentChange><Change>+3.30</Change><Commission/><Currency>USD</Currency><ChangeRealtime>+3.30</ChangeRealtime><AfterHoursChangeRealtime>N/A - N/A</AfterHoursChangeRealtime><DividendShare>0.00</DividendShare><LastTradeDate>9/17/2014</LastTradeDate><TradeDate/><EarningsShare>19.30</EarningsShare><ErrorIndicationreturnedforsymbolchangedinvalid/><EPSEstimateCurrentYear>0.00</EPSEstimateCurrentYear><EPSEstimateNextYear>0.00</EPSEstimateNextYear><EPSEstimateNextQuarter>0.00</EPSEstimateNextQuarter><DaysLow>578.775</DaysLow><DaysHigh>584.25</DaysHigh><YearLow>502.80</YearLow><YearHigh>604.83</YearHigh><HoldingsGainPercent>- - -</HoldingsGainPercent><AnnualizedGain/><HoldingsGain/><HoldingsGainPercentRealtime>N/A - N/A</HoldingsGainPercentRealtime><HoldingsGainRealtime/><MoreInfo>npmiIed</MoreInfo><OrderBookRealtime/><MarketCapitalization>394.5B</MarketCapitalization><MarketCapRealtime/><EBITDA>19.424B</EBITDA><ChangeFromYearLow>+80.45</ChangeFromYearLow><PercentChangeFromYearLow>+16.00%</PercentChangeFromYearLow><LastTradeRealtimeWithTime>N/A - &lt;b&gt;583.25&lt;/b&gt;</LastTradeRealtimeWithTime><ChangePercentRealtime>N/A - +0.57%</ChangePercentRealtime><ChangeFromYearHigh>-21.58</ChangeFromYearHigh><PercebtChangeFromYearHigh>-3.57%</PercebtChangeFromYearHigh><LastTradeWithTime>1:54pm - &lt;b&gt;583.25&lt;/b&gt;</LastTradeWithTime><LastTradePriceOnly>583.25</LastTradePriceOnly><HighLimit/><LowLimit/><DaysRange>578.775 - 584.25</DaysRange><DaysRangeRealtime>N/A - N/A</DaysRangeRealtime><FiftydayMovingAverage>576.498</FiftydayMovingAverage><TwoHundreddayMovingAverage>560.328</TwoHundreddayMovingAverage><ChangeFromTwoHundreddayMovingAverage>+22.922</ChangeFromTwoHundreddayMovingAverage><PercentChangeFromTwoHundreddayMovingAverage>+4.09%</PercentChangeFromTwoHundreddayMovingAverage><ChangeFromFiftydayMovingAverage>+6.752</ChangeFromFiftydayMovingAverage><PercentChangeFromFiftydayMovingAverage>+1.17%</PercentChangeFromFiftydayMovingAverage><Name>Google Inc.</Name><Notes/><Open>580.01</Open><PreviousClose>579.95</PreviousClose><PricePaid/><ChangeinPercent>+0.57%</ChangeinPercent><PriceSales>6.02</PriceSales><PriceBook>4.09</PriceBook><ExDividendDate/><PERatio>30.05</PERatio><DividendPayDate/><PERatioRealtime/><PEGRatio/><PriceEPSEstimateCurrentYear/><PriceEPSEstimateNextYear/><Symbol>GOOG</Symbol><SharesOwned/><ShortRatio>1.60</ShortRatio><LastTradeTime>1:54pm</LastTradeTime><TickerTrend>&amp;nbsp;++++-+&amp;nbsp;</TickerTrend><OneyrTargetPrice/><Volume>746144</Volume><HoldingsValue/><HoldingsValueRealtime/><YearRange>502.80 - 604.83</YearRange><DaysValueChange>- - +0.57%</DaysValueChange><DaysValueChangeRealtime>N/A - N/A</DaysValueChangeRealtime><StockExchange>NasdaqNM</StockExchange><DividendYield/><PercentChange>+0.57%</PercentChange></quote></results></query>
    
    <!-- total: 69 -->
    
    <!-- engine8.yql.bf1.yahoo.com -->
    
    

    Thanks,

    Zaveri

    Wednesday, September 17, 2014 7:24 PM

Answers

  • Hi,

    In fact, we can open an XML file directly in Excel application with Workbooks.OpenXML Method. In addition, we can import an XML file into the specific cells of Excel workbook with Workbook.XmlImport Method. But only if we use a standard XML file, the data will be hierarchical displayed as expected.

    If you just want to open and read the node data from the XML file one by one, I think it may be not related to Excel development. To interact with XML file, you can resort to XML Document Object Model (DOM). You can refer to the link below to learn more about XML DOM and how to use it in VBA.

    A Beginner's Guide to the XML DOM

    If you have any questions about XML DOM, I suggest you posting in VBA forum for more effective responses.

    After reading data from XML file, you can set Range.Value property to fill data into specific cells of Excel workbook.

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 18, 2014 8:39 AM
    Moderator