如何使用VBA从XML文件获取数据到Excel表

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 sheet2 in 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

2 个解决方案

#1

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

#2

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或其他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