如何使用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屋!