如何使用 VBA 从 XML 文件中提取数据到 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13620011/
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
How to fetch data from XML file to Excel sheet using VBA
提问by tester
I used the following code to fetch data from XML to Excel.
But the problem is, the results are opened in a new workbook "Book1". But I want to get the results in a specific worksheet in the same Excel where I have this macro.
Please note that I don't want to create schema in the code, as the schema changes for all the XMLs. Below mentioned code does not need schema to be specified. It can dump the results in new Excel sheet with the proper column names. So, Please let me know how can I get the results in the sheet2in the same workbook?
我使用以下代码将数据从 XML 获取到 Excel。但问题是，结果在新工作簿“ Book1”中打开。但是我想在我有这个宏的同一个 Excel 中的特定工作表中获得结果。
请注意，我不想在代码中创建架构，因为所有 XML 的架构都发生了变化。下面提到的代码不需要指定架构。它可以使用正确的列名将结果转储到新的 Excel 工作表中。所以，请让我知道如何sheet2在同一个工作簿中获得结果？
Sub ImportXMLtoList()
Dim strTargetFile As String
Application.DisplayAlerts = False
strTargetFile = "C:\example.xml"
Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
Application.DisplayAlerts = True
End Sub
回答by Tim Williams
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 Alex
i created a simply xmlmap. I need a code to import xml data to excel ( right click-xml-import), to get data from an xml, or another. I want to keep the xmlmap that I created, and just replace the data in my xlxs to data from another xlm.
我创建了一个简单的xmlmap。我需要一个代码来将 xml 数据导入到 excel（右键单击 xml 导入），从 xml 或其他文件中获取数据。我想保留我创建的 xmlmap，只需将我的 xlxs 中的数据替换为来自另一个 xlm 的数据。
'Select the file
Fname = Application.GetOpenFilename(FileFilter:="xml files (*.xml), *.xml", MultiSelect:=False)
'Check if file selected
If Fname = False Then
Exit Sub
Else
Workbooks.Open Filename:=Fname
End If
