通过 VBA 将 XML 加载到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16423644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Load XML into Excel through VBA
提问by Fox87
I've got a bit of VBA that is loading an XML file through VBA. However when it is imported it is all in one column and not split into a table.
我有一些通过 VBA 加载 XML 文件的 VBA。然而，当它被导入时，它全部在一列中，而不是分成一个表。
When I manually import this through the Data tab I get the warning there is no schema but asks if I would like Excel to create one based on source data. This then places all the data in a nice table.
当我通过数据选项卡手动导入时，我收到警告，没有架构，但询问我是否希望 Excel 基于源数据创建一个。然后将所有数据放在一个漂亮的表中。
I would like this to happen automatically within my current VBA code:
我希望这在我当前的 VBA 代码中自动发生：
VBA looks like
VBA 看起来像
Sub refresh()
'--------------------------------1. Profile IDs-----------------------------------'
'date variables
Dim start_period As String
start_period = Sheets("Automated").Cells(1, 6).Value
Dim end_period As String
end_period = Sheets("Automated").Cells(1, 7).Value
'report id variable names
Dim BusinessplanningReportID As String
'--------------------------------REST queries--------------------------------'
Dim Businessplanning As String
'REST query values
Businessplanning = "URL;http://api.trucast.net/2/saved_searches/00000/pivot/content_volume_trend/?apikey=0000000&start=" + start_period + "&end=" + end_period + "&format=xml"
'--------------------------------------------Data connections-----------------------------------'
'key metrics
With Worksheets("Sheet1").QueryTables.Add(Connection:=Businessplanning, Destination:=Worksheets("Sheet1").Range("A1"))
.RefreshStyle = xlOverwriteCells
.SaveData = True
End With
Currently the data then presents itself like this, unstructured. How can I automatically turn this into a table?
目前，数据本身就是这样的，非结构化的。我怎样才能自动把它变成一张桌子？
<result>
<entry>
<published_date>20130201</published_date>
<post_count>18</post_count>
</entry>
Thanks,
谢谢，
::Final solution::
::最终解决方案::
Sub XMLfromPPTExample2()
Dim XDoc As MSXML2.DOMDocument
Dim xresult As MSXML2.IXMLDOMNode
Dim xentry As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Dim start_period As String
start_period = Sheets("Automated").Cells(1, 6).Value
Dim end_period As String
end_period = Sheets("Automated").Cells(1, 7).Value
Dim wb As Workbook
Dim Col As Integer
Dim Row As Integer
Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load ("http://api.trucast.net/2/saved_searches/0000/pivot/content_volume_trend/?apikey=00000&start=" + start_period + "&end=" + end_period + "&format=xml")
LoadOption = xlXmlLoadImportToList
Set xresult = XDoc.DocumentElement
Set xentry = xresult.FirstChild
Col = 1
Row = 1
For Each xentry In xresult.ChildNodes
Row = 1
For Each xChild In xentry.ChildNodes
Worksheets("Sheet2").Cells(Col, Row).Value = xChild.Text
'MsgBox xChild.BaseName & " " & xChild.Text
Row = Row + 1
'Col = Col + 1
Next xChild
'Row = Row + 1
Col = Col + 1
Next xentry
End Sub
采纳答案by Madthew
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:
并且您想获得一个包含两列的 excel：
**published_date** | **post_count**
20130201 | 18
20120201 | 15
so that we can assume that in your XML you will always have
这样我们就可以假设在您的 XML 中您将始终拥有
<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).
重要提示：在 PowerPoint、Excel.. Word 中打开 VBA 编辑器并添加对“Microsoft XML，v3.0”的引用（此引用适用于 Office 2000...您可能还有其他的）。
Source: http://vba2vsto.blogspot.it/2008/12/reading-xml-from-vba.html
来源：http: //vba2vsto.blogspot.it/2008/12/reading-xml-from-vba.html
Employee.XML
雇员.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
阅读以上 XML 的代码
Sub XMLfromPPTExample()
Dim XDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xEmployee As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode
Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load ("C:\Emp.xml")
Set xEmpDetails = XDoc.documentElement
Set xEmployee = xEmpDetails.firstChild
For Each xEmployee In xEmpDetails.childNodes
For Each xChild In xEmployee.childNodes
MsgBox xChild.baseName & " " & xChild.Text
Next xChild
Next xEmployee
End Sub
In your case, of course, you need to adapt your routine:
当然，就您而言，您需要调整您的日常工作：
result --> EmpDetails in the code provided
entry --> Employee in the code provided
结果 --> 提供的代码中的 EmpDetails
条目 --> 提供的代码中的员工
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.
事实上，循环遍历“条目”中的所有元素，您将获得 COLUMN，然后每个新条目都是一个新的 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.
不幸的是，我在 MAC 上没有 excel，所以我只是放了逻辑，你应该检查自己的语法……这样你就可以在你想要的工作表上建立一个 EXCEL 表。
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 xChild
row = row+1;
Next xEmployee
THE CORRET WAY SHOULD BE THIS:
正确的方式应该是这样的：
LoadOption:=xlXmlLoadImportToList?
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:
您是从 URL 调用中获取 XML，但我强烈建议在开始时尝试使用磁盘上的 XML 文件，并检查它是否正确有效。所以你应该做的是从这个“WebService”中获取一个示例 XML，然后将它保存在磁盘上。尝试按以下方式加载它：
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
回答by Joe
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.
我使用了我发现的其他代码部分中的一些部分。下面的代码将提示用户选择您想要的 XML 文件，并允许他们简单地将所选文件添加/导入到他们现有的映射中，而无需打开新文件。
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:=Fname
End Sub
