Excel 2010 has a fabulous feature under Data->"From Other Sources"->"From XML Data Import" to pull in XML from a web service, no coding required. Works great, but my service has now added a new column at the end. Refreshing the connection in Excel does not add the new column. In fact, creating a second connection to the exact same URL in the same spreadsheet won't show the new column, either (although doing the same thing on a different excel workbook will). Apparently, Excel is caching the columns somehow.
I found this link which describes a possible workaround by manually hacking the excel file; but that was 3 years ago. Surely, there's a better way now.
Any advice? You can replicate this by just saving the following XML as a text file, importing it to 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>
2 Answers 2
Options:
- Excel 2010 Developer tab (see question Excel Add A Field To An Xml Map) (search)
- Excel 2007 (maybe others) A solution appears to have been developed using VBA in the forum post: XML Question - Update Schema/Mapping (archive)(archive of the Programatically using XML Mapping Feature page that post references). The VBA code is included below.
- Excel 2003 XML Toolbox for Excel 2003
- ALL: Workaround provided below which does not disturb formulas.
I have not tried the VBA code from the linked forum post and included below. I hope it works for you. However, the workaround below appears to be easy and non-disruptive to formulas referencing data inside the mapping. Thus, I would use that, unless it does not work for you or if your mapping is complex, or you expect to need to semi-automatically update multiple times (although the workaround below takes very little time an effort).
There does not appear to be an official solution for Excel 2007. There appears to be two separate official solutions: one for Excel 2003 (add-on), and one for Excel 2010 (Developer tab).
The question Excel Add A Field To An Xml Map offered three methods which might work.
- The first was the XML Toolbox for Excel 2003 which can do this in Excel 2003. A comment to that solution indicated that it has some issues/problems and may, or may not, be functional for you. You may have to use an older version on Excel, or even an older operating system.
- The second is the "edit the .xls file" method.
- The third is to use the Excel 2010 Developer tab.
Additional options:
- Workaround provided below (probably my choice for simple one-off updates which are not expected to repeat and the mapping is simple).
- Save the worksheet as a XML Spreadsheet. Edit the schema recorded at the end of the file. Given that it is XML it is entirely in text. The schema is easy to understand. Adding a column is as simple as just copy and pasting (with modifications) a few lines of XML text. If using this methd, it is probably easiest to make a duplicate of your worksheet, delete and re-create the map and mapping then cut and paste. Example XML:
(SO-formatting required blank text)
<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>
Workaround: [added/inserted/deleted column(s)] (easy for simple mappings, e.g. one large table):
- Make a backup copy of your worksheet.
- Delete the XML map. Not the mapping in the sheet. Just the XML map.
- Re-create the map. It will now have the new XML schema.
- If you have formula/data which would be overwritten by additional columns: Insert enough columns immediately to the right of the current area on the sheet which contains the XML mapped data.
- If there are any columns which have been inserted in between older columns then Insert a column in the sheet where they will go. Yes, this is right in the XML mapped data area and a generic column name will be temporarily assigned.
- Delete any columns which do not exist in the new XML.
- Drag-and-drop the elements from the newly mapped table to the top left corner of the current mapping (or otherwise recreate the mapping on the sheet directly over the mapping which you have not deleted).
- Manually change any headings that were updated (if your data was marked as having headings).
- Repeat steps 4 through 8 for all separate mappings you have in your workbook. Usually I just have one large mapping into which I reference as opposed to multiple small mappings.
- Refresh your XML data.
Your formulas should not have changed, and cell references into the XML mapped data should remain correct. For verification that you inserted any columns in step 3 correctly, you can, prior to making any other changes (i.e. at step 1a) create a row above the XML mapping and copy and paste just the values from the top row. When you refresh the XML data in step 10 you can verify that the old row headings match the new placement.
Note: Steps 3 and 4 can alternately be done by making a more complicated mapping of the XML elements. I find it easier to just insert columns and keep a 1-1 correspondence with the XMl elements.
This worked on my machine through multiple trials and left the formulas referencing the area as correct. A file where you can experiment, called SO-XML mapping.xml is included in the example file linked below (and here)
Accounting for generic column changes in some data table obtained from a source (or using multiple sources interchangeably):
There are times when the data that you have is not dynamically provided by something like an XML map, or when you don't want to have to be fussy about remembering to account for inserted columns. The brute force workaround of accomplishing this is to create your own mapping of the data columns into a table(sheet) in your worksheet within which you control the column locations. You can do this by creating a table which indirectly references the data which you have imported/copied into a different sheet. using an indirect mapping like this can also be used to easily switch between two, or more, different data sets as input to whatever formulas you have set up.
The indirect mapping uses INDIRECT() to reference the the sheet containing the source data. All of your formulas then point to this indirect sheet instead of the source data. The contents of the columns in the indirect sheet are organized in fixed locations with the data pulled in from a column in the source data that matches the column heading specified. Then if a change is made to the organization of the columns within the source data you can just either create a new sheet with the alternately formatted data, or place it in the sheet where the original data used to be and change the column headings you are using for referencing. If switching between multiple data sets, you can then change the source data used completely by changing one cell.
This indirect mapping sheet allows the columns in your formulas (outside of the indirect sheet) to be fixed instead of having to worry about the possible reorganization of your source data. Alternately, it permits you to have two, or more, data sets which are automatically converted into the same column organization when you switch between them. You can accomplish this even if the data sets have different headers used for the same actual data (just have a line of headers which is used when the alternate set(s) of data is(are) selected).
This is probably easier to show than to explain in a post like this. Thus, here is an example which selects data from five different sheets.
In that example, all your formulas would then reference the Indirect page. The example assumes all files are placed in the C:\ directory. Unfortunately, using XML mappings required a complete file path and C:\ is the highest probability location to exist on most machines running Excel.
VBA code from KMKfan on Nov 16th, 2009:
Posted in XML Question - Update Schema/Mapping (archive)(archive of the Programatically using XML Mapping Feature page that post references).
The comment in the post were:
Here is an update for this code. This code allows for multiple XML maps to update within the same workbook as long as the xsd and xml map have the same name (ie: the source map "MyMap" is based on MyMap.xsd. Should be very helpful if you are using XML to assist in reporting and decide to add a piece of data to capture. You can update the existing mapping automatically and only have to manually update the new data elements.
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("$A1ドル")
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
Comments
Try this:
Highlight your imported data, and delete the rows they populate. Then, select the cell you want to insert the data and recreate the connection by going to Data -> From XML Data Import. When it recreates the connection, it should add your extra column. (If you try to refresh on top of the data table that's currently there, it won't append your column.)