Fill ListObject with XML data

S

Stevenn

Active Member
Joined
Feb 8, 2012
Messages
259
I have a table (ListObject) in Excel 2007 with a total of 10 columns. I want to fill this table with data from a XML file with other column names.

I import the XML data with this code:

Code: 
Public Sub ImportXML()
    ActiveWorkbook.XmlImport URL:=Application.GetOpenFilename(FileFilter:="XML Files (*.xml*), *.xml*", Title:="Choose File To Copy", MultiSelect:=False), ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")
End Sub

I know that it's possible to handle XML data in some special ways, but I am not familiar with these. How is it possible 'directing' the XML data to the correct columns?
 
Sort by date Sort by votes
Maybe something like
Code: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Row>
        <ID>2</ID>
        <Description>My Description</Description>
        <Name>A</Name>
        <Value>4900</Value>
    </Row>
    <Row>
        <ID>3</ID>
        <Description>My new description</Description>
        <Name>B</Name>
        <Value>3905</Value>
    </Row>
</Root>

and an Excel ListObject with columns MyDescription and MyValue. So Description goes to MyDescription and Value goes to MyValue (it should not place it by the same name, but simply because I want Description to be placed in the column named MyDescription).

I know I can solve the problem with ExcelListObject.Cells(i, 1).Value = XMLData.Cells(i, 2).Value and so on, but I guess there could maybe be a way to do it with the XML environment in Excel?
 
Upvote 0
It is possible to automaate the process of reading an XML file and placing the XML Data into various cells in excel. The data you have provided doesn't give me sufficent information for me to assist you.

Typically an XML file will look similar to the follwing

Code: 
[COLOR=blue]<?[/COLOR][COLOR=#a31515]xml[/COLOR] [COLOR=red]version[/COLOR][COLOR=blue]=[/COLOR][COLOR=black]"[/COLOR][COLOR=blue]1.0[/COLOR][COLOR=black]"[/COLOR][COLOR=blue]?>[/COLOR]
[COLOR=blue]<[/COLOR][COLOR=#a31515]catalog[/COLOR][COLOR=blue]>[/COLOR]
   [COLOR=blue]<[/COLOR][COLOR=#a31515]book[/COLOR] [COLOR=red]id[/COLOR][COLOR=blue]=[/COLOR][COLOR=black]"[/COLOR][COLOR=blue]bk101[/COLOR][COLOR=black]"[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]author[/COLOR][COLOR=blue]>[/COLOR]Gambardella, Matthew[COLOR=blue]</[/COLOR][COLOR=#a31515]author[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]title[/COLOR][COLOR=blue]>[/COLOR]XML Developer's Guide[COLOR=blue]</[/COLOR][COLOR=#a31515]title[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]genre[/COLOR][COLOR=blue]>[/COLOR]Computer[COLOR=blue]</[/COLOR][COLOR=#a31515]genre[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]price[/COLOR][COLOR=blue]>[/COLOR]44.95[COLOR=blue]</[/COLOR][COLOR=#a31515]price[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]publish_date[/COLOR][COLOR=blue]>[/COLOR]2000-10-01[COLOR=blue]</[/COLOR][COLOR=#a31515]publish_date[/COLOR][COLOR=blue]>[/COLOR]
      [COLOR=blue]<[/COLOR][COLOR=#a31515]description[/COLOR][COLOR=blue]>[/COLOR]An in-depth look at creating applications with XML.[COLOR=blue]</[/COLOR][COLOR=#a31515]description[/COLOR][COLOR=blue]>[/COLOR]
   [COLOR=blue]</[/COLOR][COLOR=#a31515]book[/COLOR][COLOR=blue]>[/COLOR]

It sounds like you want to the data assoicated with various XML Tags and copy ot to columns in excel. I would assume that each new xml record would me placed in a new row in excel.

The DOM ( Document Object Model) is the magic behind working with XML in VBA (and our languages)

If you can provide the XML, I may be able to provide some code to get you started
 
Upvote 0
I don't know what happened to my XML-data. My XML-data would look something like what you just posted:

PHP: 
<xml version="1.0"?> <rows>    <row>       <author>Gambardella, Matthew</author>       <value>44.95</value>       <description>An in-depth look at creating applications with XML.</description>    <row>
   <row>       <author>Gambardella, Matthew</author>       <value>44.95</value>       <description>An in-depth look at creating applications with XML.</description>    <row>
</rows>
 
Upvote 0
Here is some sample code that reads the xml and places the data in cell on Sheet 1

Code: 
Option Explicit
Dim Ws As Worksheet
Sub Zoe_Validate()
    Dim RowNo As Long
    
    Dim FileName As String
    Dim Tag As String
    Dim FieldName As String
    
    Set Ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim XMLdoc As New MSXML2.DOMDocument
    Dim oNodeList As IXMLDOMNodeList
    
    Dim I As Integer
    Dim XmlRow As Long
    
    FileName = OpenFileDialog("Choose XML File")
    
    If Not XMLdoc.Load(FileName) Then
        MsgBox "File not found: " & FileName, vbCritical
        Exit Sub
    End If
    
    RowNo = 1
    Ws.Cells.ClearContents
    Set oNodeList = XMLdoc.getElementsByTagName("row")
    
    For XmlRow = 0 To oNodeList.Length - 1
        RowNo = RowNo + 1
        For I = 0 To oNodeList.Item(XmlRow).ChildNodes.Length - 1
            With oNodeList.Item(XmlRow).ChildNodes.Item(I)
                
                Ws.Cells(RowNo, I + 1) = .Text
        
            End With
        Next I
    Next XmlRow
    MsgBox "Complete", vbInformation
End Sub

Function OpenFileDialog(strTitle As String) As String
    Dim fd As FileDialog
    Dim objfl As Variant
    Dim filnam As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "XML Files", "*.xml", 1
        .Title = strTitle
        .InitialView = msoFileDialogViewDetails
        .Show
        OpenFileDialog = .SelectedItems(1)
        
    End With
    Set fd = Nothing
End Function

Excel thru an error when reading the xml file you provided. I did make a change and it corrected the error. Here is the revised XML

PHP: 
<?xml version="1.0"?> 
 <rows>    
  <row>       

   <author>Gambardella, Matthew</author>       
   <value>44.95</value>       
   <description>An in-depth look at creating applications with XML.</description>    
  </row>   
  <row>       
   <author>Gambardella, Matthew</author>       
   <value>44.95</value>       
   <description>An in-depth look at creating applications with XML.</description>    
  </row>
 </rows>
 
Last edited:
Upvote 0
Here is some sample code that reads the xml and places the data in cell on Sheet 1

Code: 
Option Explicit
Dim Ws As Worksheet
Sub Zoe_Validate()
    Dim RowNo As Long
    
    Dim FileName As String
    Dim Tag As String
    Dim FieldName As String
    
    Set Ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim XMLdoc As New MSXML2.DOMDocument
    Dim oNodeList As IXMLDOMNodeList
    
    Dim I As Integer
    Dim XmlRow As Long
    
    FileName = OpenFileDialog("Choose XML File")
    
    If Not XMLdoc.Load(FileName) Then
        MsgBox "File not found: " & FileName, vbCritical
        Exit Sub
    End If
    
    RowNo = 1
    Ws.Cells.ClearContents
    Set oNodeList = XMLdoc.getElementsByTagName("row")
    
    For XmlRow = 0 To oNodeList.Length - 1
        RowNo = RowNo + 1
        For I = 0 To oNodeList.Item(XmlRow).ChildNodes.Length - 1
            With oNodeList.Item(XmlRow).ChildNodes.Item(I)
                
                Ws.Cells(RowNo, I + 1) = .Text
        
            End With
        Next I
    Next XmlRow
    MsgBox "Complete", vbInformation
End Sub

Function OpenFileDialog(strTitle As String) As String
    Dim fd As FileDialog
    Dim objfl As Variant
    Dim filnam As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "XML Files", "*.xml", 1
        .Title = strTitle
        .InitialView = msoFileDialogViewDetails
        .Show
        OpenFileDialog = .SelectedItems(1)
        
    End With
    Set fd = Nothing
End Function

Excel thru an error when reading the xml file you provided. I did make a change and it corrected the error. Here is the revised XML

HTML: 
<?xml version="1.0"?> 
 <rows>    
  <row>       
   <author>Gambardella, Matthew</author>       
   <value>44.95</value>       
   <description>An in-depth look at creating applications with XML.</description>    
  </row>   
  <row>       
   <author>Gambardella, Matthew</author>       
   <value>44.95</value>       
   <description>An in-depth look at creating applications with XML.</description>    
  </row>
 </rows>
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
VBA to import multilpe xml files
Replies
1
Views
936
PippaThePointer
P
J
  • Question Question
Merge several csv files into one workbook
Replies
6
Views
202
alansidman
alansidman
M
  • Solved
Trouble getting results from Listobject Table Column when searching
Replies
5
Views
341
malcolmlyle
M
T
  • Solved
CSV import overwriting the next column after the last one in the CSV file
Replies
7
Views
445
Joe4
Joe4
D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
427
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top