如何使用VBA从XML文件获取数据到Excel表 [英] How to fetch data from XML file to Excel sheet using VBA

问题描述

我使用以下代码从XML获取数据到Excel。
但问题是，结果在新的工作簿 Book1 中打开。但是我想在同一个Excel中的特定工作表中获取结果，我有这个宏。

请注意，我不想在代码中创建模式，因为所有模式的模式都会更改个XML。下面提到的代码不需要指定模式。它可以使用正确的列名称将结果转储到新的Excel工作表中。所以，请让我知道如何在同一份工作簿中的 sheet2 中获得结果？

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?

Sub ImportXMLtoList()
 Dim strTargetFile As String
 Application.DisplayAlerts = False
 strTargetFile = "C:\example.xml"
 Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
 Application.DisplayAlerts = True

End Sub

推荐答案

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

这篇关于如何使用VBA从XML文件获取数据到Excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！