Load XML into Excel through VBA

THE "HARD CODED" WAY IS THIS:

Starting from this

<result>   <entry>      <published_date>20130201</published_date>      <post_count>18</post_count>       </entry>  <entry>      <published_date>20120201</published_date>      <post_count>15</post_count>       </entry>

and you want to obtain an excel with two column:

**published_date** |  **post_count**20130201       |           1820120201       |           15

so that we can assume that in your XML you will always have

<result><entry><Element>VALUE</Element><Element...n>VALUE</Element...n></entry>

IMPORTANT:Open up VBA editor in PowerPoint, Excel.. Word and add references to "Microsoft XML, v3.0" (this reference is for Office 2000... you might have others).

Source: http://vba2vsto.blogspot.it/2008/12/reading-xml-from-vba.html

Employee.XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><EmpDetails><Employee><Name>ABC</Name><Dept>IT-Software</Dept><Location>New Delhi</Location></Employee><Employee><Name>XYZ</Name><Dept>IT-Software</Dept><Location>Chennai</Location></Employee><Employee><Name>IJK</Name><Dept>HR Operations</Dept><Location>Bangalore</Location></Employee></EmpDetails>

CODE TO READ ABOVE XML

Sub XMLfromPPTExample()Dim XDoc As MSXML2.DOMDocumentDim xEmpDetails As MSXML2.IXMLDOMNodeDim xEmployee As MSXML2.IXMLDOMNodeDim xChild As MSXML2.IXMLDOMNodeSet XDoc = New MSXML2.DOMDocumentXDoc.async = FalseXDoc.validateOnParse = FalseXDoc.Load ("C:\Emp.xml")Set xEmpDetails = XDoc.documentElementSet xEmployee = xEmpDetails.firstChildFor Each xEmployee In xEmpDetails.childNodesFor Each xChild In xEmployee.childNodesMsgBox xChild.baseName & " " & xChild.TextNext xChildNext xEmployeeEnd Sub

In your case, of course, you need to adapt your routine:

result --> EmpDetails in the code provided
entry --> Employee in the code provided

plus any other necessary adjustment.

In this way you can have as much as many "entry" and "entry child" elements you want.

In fact, looping through all the elements inside your "entry" you will get your COLUMN, then every new entry is a new ROW.

Unfortunately, I don't have excel on the MAC so I just put the logic, you should check the sintax your own... in this way you build a EXCEL table on the worksheet you want.

Dim col = 1; Dim row=1;For Each xEmployee In xEmpDetails.childNodes    col = 1    For Each xChild In xEmployee.childNodes       Worksheets("NAMEOFTHESHEET").Cells(col, row).Value = xChild.Text       MsgBox xChild.baseName & " " & xChild.Text       col = col + 1;    Next xChildrow = row+1;Next xEmployee

THE CORRET WAY SHOULD BE THIS:

LoadOption:=xlXmlLoadImportToList?

You are getting the XML from a URL call, but I strongly suggest to try to work with an XML file on disk at the beginning, and check if it's correctly valid. So what you should do is get a sample XML from this "WebService" then save it on disk. An try load it in the following way:

   Sub ImportXMLtoList()    Dim strTargetFile As String    Dim wb as Workbook         Application.Screenupdating = False         Application.DisplayAlerts = False         strTargetFile = "C:\example.xml"         Set wb = Workbooks.OpenXML(Filename:=strTargetFile,        LoadOption:=xlXmlLoadImportToList)         Application.DisplayAlerts = True         wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")         wb.Close False         Application.Screenupdating = True    End Sub

I used a few sections from other sections of code I've found. The below code will prompt the user to select the XML file you want and allows them to simply add/import the selected file into their existing mapping without opening a new file.

Sub Import_XML()'' Import_XML Macro'    'Select the file    Fname = Application.GetOpenFilename(FileFilter:="xml files (*.xml), *.xml", MultiSelect:=False)    'Check if file selected    If Fname = False Then        Exit Sub        Else    End If    'Import selected XML file into existing, custom mapping    Range("B5").Select    ActiveWorkbook.XmlMaps("Result_file_Map").Import URL:=FnameEnd Sub