Excel "From Importing XML Data" will not add new columns

Question

Excel 2010 has an awesome feature in the section "Data from Other Sources" → "From Importing XML Data" to pull XML from a web service without requiring coding. Works great, but a new column is now added to my service. Refreshing a connection in Excel does not add a new column. In fact, creating a second connection with the same URL in the same spreadsheet also does not display the new column (although it does the same in another excel workbook). Apparently, Excel caches the columns somehow.

I found this link that describes a possible workaround by manually hacking the excel file; but that was 3 years ago. Of course, it’s better now.

Any tips? You can replicate this by simply saving the following XML as a text file, importing it into Excel, then editing the file and adding a new column.

<Table>
    <Row>
        <First>1</First>
        <Second>2</Second>
    </Row>
    <Row>
        <First>3</First>
        <Second>4</Second>
    </Row>
</Table>

Answer 1

Parameters:

• Excel 2010 developer tab (see Excel question Add a field to an Xml map ) ( search )
• Excel 2007 (possibly others) The decision seems to have been developed using VBA on the forum post: the XML-issue - update scheme / comparison ( archive The ) ( archive pages using XML Mapping Feature functions, which publishes reference ). The VBA code is below.
• Excel 2003 XML Toolbox Excel 2003
• : , , .

VBA . , . , . , , , , ( ).

, Excel 2007 . : Excel 2003 () Excel 2010 ( ).

Excel Xml- , .

• XML Toolbox for Excel 2003, Excel 2003. , / . Excel .
• - " .xls".
• -, " Excel 2010".

:

• , (, , , ).
• XML. , . , XML, . . , ( ) XML. , , , , . XML:

( SO )

<x2:Field x2:ID="Add-E-head">
 <x2:Range>RC[4]</x2:Range>
 <x2:XPath>Add-E-head</x2:XPath>
 <x2:XSDType>string</x2:XSDType>
 <ss:Cell>
 </ss:Cell>
 <x2:Aggregate>None</x2:Aggregate>
</x2:Field>

: [// ] ( , ):

• .
• XML. . XML.
• . XML-.
• /, : , , XML.
• , , , . , , XML, .
• , XML.
• ( , ).
• , ( ).
• 4 8 , . , , .
• XML.

, XML- . , 3 , (.. 1a) XML- . XML 10, , .

. 3 4 XML. 1-1 XMl.

, , . , , SO-XML mapping.xml, , ( )

, ( ):

, , , - XML, . () , . , , , / . , , , .

INDIRECT() , . . , , . , , , , , . , , , .

( ) , . , , . , , ( , , ).

, , , . , .

. , C:\. , XML- , C:\ - , , Excel.

VBA KMKfan 16 2009 :

XML- - / (archive) ( XML Mapping Feature, ).

:

. XML , xsd xml- ( : "MyMap" MyMap.xsd. , XML . .

Dim r, c As Integer
Dim wb1, wb2 As Workbook
Dim StrMap, StrWS, StrRng, StrXPath As String
Dim nStrWS, nStrRng, nStrXPath As String
Dim nStrMap As XmlMap

Sub Update_XML()
    Call Get_XPath
    Call Add_NewMap
    Call Assign_Elements
End Sub

Sub Get_XPath()
'Gets Available XML Mappings (XPath) for current workbook and sends the text information to a temp file.
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add
wb1.Activate
For Each Sheet In wb1.Sheets
Sheet.Select
    Range("A1").Select
    Selection.UnMerge
    For c = 1 To ActiveSheet.UsedRange.Columns.Count
        For r = 1 To ActiveSheet.UsedRange.Rows.Count
            If ActiveCell.Offset(r - 1, c - 1).XPath <> "" Then Call Send_XPath
            wb1.Activate
        Next r
    Next c
    Selection.Merge
Next Sheet

End Sub

Sub Send_XPath()
'Sends text information to a temporary workbook for use later.
    StrWS = ActiveSheet.Name
    StrRng = ActiveCell.Offset(r - 1, c - 1).Address
    StrXPath = ActiveCell.Offset(r - 1, c - 1).XPath
    StrMap = ActiveCell.Offset(r - 1, c - 1).XPath.Map.Name
    With wb2
        .Activate
        ActiveCell = StrMap
        ActiveCell.Offset(0, 1) = StrWS
        ActiveCell.Offset(0, 2) = StrRng
        ActiveCell.Offset(0, 3) = StrXPath
        ActiveCell.Offset(1, 0).Select
    End With
End Sub

Sub Add_NewMap()
'Delete the current XML map and add a new XML Map that has the same schema structure.
'XML Map and XSD schema must be named identically.  Only the .xsd extension should be different.
Dim MyPath, MyMap As String
MyPath = 'Path of .xsd file goes here
    For Each XmlMap In wb1.XmlMaps
        MyMap = XmlMap.Name
        wb1.XmlMaps(XmlMap.Name).Delete
        wb1.XmlMaps.Add(MyPath & "\" & MyMap & ".xsd").Name = MyMap
    Next XmlMap
End Sub

Sub Assign_Elements()
'Assign XPath of new XML Map to ranges based on the information in the temp workbook.  Close 2nd workbook w/o saving.
With wb2
    .Activate
    Application.Goto Range("$A$1")
End With
Do Until ActiveCell = ""
    Set nStrMap = wb1.XmlMaps(ActiveCell.Text)
    nStrWS = ActiveCell.Offset(0, 1)
    nStrRng = ActiveCell.Offset(0, 2)
    nStrXPath = ActiveCell.Offset(0, 3)
    With wb1
        .Activate
        Sheets(nStrWS).Select
        Range(nStrRng).XPath.SetValue nStrMap, nStrXPath
    End With
        wb2.Activate
        ActiveCell.Offset(1, 0).Select
Loop
    wb2.Close False
End Sub

Answer 2

:

. , , , Data → From XML Data Import. , . ( , , .)