vba Excel 将字段添加到 Xml 映射

Excel Add A Field To An Xml Map

提问by TSUK

I've a XML map on an Excel 2010 worksheet, that I refresh from the contents of an XML file on a regular basis. I recently added a new field to the XML data and wish this to show up on my XML map.

我在 Excel 2010 工作表上有一个 XML 映射，我定期从 XML 文件的内容中刷新它。我最近向 XML 数据添加了一个新字段，并希望它显示在我的 XML 映射上。

I simply want to be able to add the new field however it appears although ill have to import the whole structure again, I dont want to do this as it will require redeveloping alot of custom formating.

我只是希望能够添加新字段，但它看起来虽然必须再次导入整个结构，但我不想这样做，因为它需要重新开发大量自定义格式。

Any help appreciated.

任何帮助表示赞赏。

回答by i_saw_drones

Unfortunately there is no easy "refresh" method for schema in the XML object model in Excel at the moment. The XML Toolbox for Excel 2003used to be able to do this, but I am not sure if this still runs in Excel 2010 (worth a try). Alternative to this is to write your own VBA code which would import your "new" schema into a new map, and then look at the existing element maps for the "old" schema and then remap these to the "new" schema, finally deleting the "old" one. Sounds a bit hairy I know, but if your schema doesn't change significantly then it could be the answer.

不幸的是，目前 Excel 中的 XML 对象模型中的架构没有简单的“刷新”方法。Excel 2003的XML 工具箱曾经能够做到这一点，但我不确定这是否仍能在 Excel 2010 中运行（值得一试）。替代方法是编写您自己的 VBA 代码，将您的“新”模式导入到新映射中，然后查看“旧”模式的现有元素映射，然后将它们重新映射到“新”模式，最后删除“旧”的。我知道这听起来有点麻烦，但如果您的架构没有显着变化，那么它可能就是答案。

There is a less graceful method than this, which involves changing the schema in the workbook's underlying XML directly. If have a look inside the workbook structure under the xlfolder, you will see that there is a file named xmlMaps.xmland inside this file will be a copy of your schema - you can then edit this directly (add new elements etc.) and the new fields will then show up for use in your workbook when you open it again in Excel, leaving your original cell formatting unchanged.

还有一种不那么优雅的方法，它涉及直接更改工作簿底层 XML 中的架构。如果查看xl文件夹下的工作簿结构，您将看到有一个名为的文件xmlMaps.xml，该文件内将是您的架构的副本 - 然后您可以直接编辑它（添加新元素等）和新字段当您在 Excel 中再次打开工作簿时，它将显示在您的工作簿中以供使用，而原始单元格格式不变。

回答by Rubans

I extracted the xlsx as a zip and was able to make the changes manually without having to delete and add the mappings again following the instructions on this site: http://davidovitz.blogspot.com/2010/05/howto-refresh-xml-schema-in-excel.html

我将 xlsx 提取为 zip 文件，并且能够手动进行更改，而无需按照此站点上的说明再次删除和添加映射：http: //davidovitz.blogspot.com/2010/05/howto-refresh-xml -schema-in-excel.html

回答by mrtsherman

This isn't exactly what the OP asked for, but it worked in my case, so I am adding it as a probable solution (using Office 2013)

这并不完全是 OP 所要求的，但它在我的情况下有效，因此我将其添加为可能的解决方案（使用 Office 2013）

1. Right click in your ribbon and Enable Developer Tools

1. 右键单击您的功能区并启用开发人员工具

1. Go to the new Developer Tools section on your ribbon and click Source

2. 转到功能区上的新开发人员工具部分，然后单击源

1. This will open a righthand side flyout. Elements in bold are currently mapped to your columns. Note where things map to by clicking on them. In my case it was pretty straightforward with the first element mapping to column A, etc. Then right click on anything in bold and unmap it.

3. 这将打开右侧弹出窗口。粗体元素当前映射到您的列。通过单击它们来注意事物映射到的位置。在我的例子中，第一个元素映射到列 A 等非常简单。然后右键单击任何粗体并取消映射。

1. Click the XML Mapsbutton at bottom of flyout

2. Click Addand navigate to a file or enter a URL

4. 单击弹出底部的XML Maps按钮

5. 单击添加并导航到文件或输入 URL

1. Remove the old mapping if you want

2. Highlight all your new elements and right click to assign a new mapping. Use the notes you jotted down earlier to do this. Again, my case was very straightforward since columns were merely appended to the end. If you have columns added in the middle, you would want to remap them to the end of your table.

6. 如果需要，请删除旧映射

7. 突出显示所有新元素并右键单击以分配新映射。使用您之前记下的笔记来执行此操作。同样，我的案例非常简单，因为列只是附加到末尾。如果您在中间添加了列，您可能希望将它们重新映射到表格的末尾。