通过VBA将XML加载到Excel中 [英] Load XML into Excel through VBA

问题描述

我有一点VBA，通过VBA加载XML文件。但是当它被导入时，它全部在一列中，而不是分割成一个表。

当我通过数据选项卡手动导入时，我收到警告，没有模式，但是询问我是否希望Excel根据源数据创建一个模式。这样将所有的数据都放在一张漂亮的表格中。

我希望这可以在我目前的VBA代码中自动发生：

VBA看起来像

 子刷新（）
 
'------ -------------------------- 1。个人资料ID -----------------------------------'
 
 
 'date变量
 
 Dim start_period As String 
 start_period = Sheets（Automated）。单元格（1，6）.Value 
 Dim end_period As String 
 end_period =表单（Automated）。单元格（1，7）.Value 
 
'report id变量名称
 Dim BusinessplanningReportID As String 
 
'----- --------------------------- REST查询--------------------- -----------'
 Dim Businessplanning As String 
 
 
 
'REST查询值
 Businessplanning =URL; http ：/ / ap / 
 
 
 
'--------------------------------- -----------数据连接-----------------------------------'
'关键指标
与工作表（Sheet1）。QueryTables.Add（连接：=业务计划，目标：=工作表（Sheet1）。范围（A1））
 
 .RefreshStyle = xlOverwriteCells 
 .SaveData = True 
 
结束
  

目前，数据然后像这样呈现，非结构化。如何自动将其转换成表？

 < result> 
<条目> 
< published_date> 20130201< / published_date> 
< post_count> 18< / post_count> 
< / entry> 
  

谢谢，

::最终解决方案::

  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）。单元格（1，7）.Value 
 Dim wb As Workbook 
 Dim Col As Integer 
 Dim Row As Integer 
 
 
设置XDoc =新建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 
 
设置xresult = XDoc.DocumentElement 
设置xentry = xresult.FirstChild 
 
 
 Col = 1 
 Row = 1 
 
对于每个xentry在xresult.ChildNodes 
 Row = 1 
 
 
对于每个xChild在xentry.ChildNodes 
工作表（Sheet2）。单元格（Col，Row ）.Value = xChild.Text 
'MsgBox xChild.BaseName& & xChild.Text 
 Row = Row + 1 
'Col = Col + 1 
 
下一个xChild 
'Row = Row + 1 
 Col = Col + 1 
下一个xentry 
 
 End Sub 
  

解决方案

HARD CODED的方式是这样的：

从此开始

 < result> 
<条目> 
< published_date> 20130201< / published_date> 
< post_count> 18< / post_count> 
< / entry> 
<条目> 
< published_date> 20120201< / published_date> 
< post_count> 15< / post_count> 
< / entry> 
  

您想要获得excel与两列：

  ** published_date ** | ** post_count ** 
 20130201 | 18 
 20120201 | 15 
  

，以便我们可以假设在您的XML中，您将始终拥有

 < result>< entry>< Element> VALUE< / Element>< Element ... n> VALUE< / Element ... n& ;< /条目> 
  

重要信息：
在PowerPoint，Excel中打开VBA编辑器Word和添加对Microsoft XML，v3.0的引用（此参考适用于Office 2000 ...您可能还有其他人）。

来源： http://vba2vsto.blogspot.it/2008/12/reading-xml -from-vba.html

Employee.XML

 <？xml version =1.0encoding =UTF-8standalone =yes？> 
&EmpDetails> 
<员工> 
< Name> ABC< / Name> 
< Dept> IT软件< / Dept> 
<位置>新德里< /位置> 
< / Employee> 
<员工> 
< Name> XYZ< / Name> 
< Dept> IT软件< / Dept> 
<位置>金奈< /位置> 
< / Employee> 
<员工> 
< Name> IJK< / Name> 
< Dept> HR Operations< / Dept> 
<位置>班加罗尔< /位置> 
< / Employee> 
< / EmpDetails> 
  

要阅读的XML代码

  Sub XMLfromPPTExample（）
 Dim XDoc As MSXML2.DOMDocument 
 Dim xEmpDetails As MSXML2.IXMLDOMNode 
 Dim xEmployee As MSXML2.IXMLDOMNode 
 Dim xChild As MSXML2.IXMLDOMNode 
 
设置XDoc =新建MSXML2.DOMDocument 
 XDoc.async = False 
 XDoc.validateOnParse = False 
 XDoc.Load（ C：\Emp.xml）
设置xEmpDetails = XDoc.documentElement 
设置xEmployee = xEmpDetails.firstChild 
对于每个xEmployee在xEmpDetails.childNodes中
对于xEmployee中的每个xChild .childNodes 
 MsgBox xChild.baseName& & xChild.Text 
下一个xChild 
下一个xEmployee 
 End Sub 
  

在你的情况下，当然你需要适应你的例程：

结果 - >提供的代码中的EmpDetails

条目 - >提供的代码中的员工

加上任何其他必要的调整。

---

以这种方式，您可以拥有所需的多达条目和条目小孩元素。

实际上，循环访问你的条目内的所有元素，你将得到你的COLUMN，然后每一个新条目都是一个新的ROW。

不幸的是，我不如说MAC，所以我只是把逻辑，你应该检查你自己的sintax ...这样你在你想要的工作表上建立EXCEL表。

Dim col = 1;

Dim row = 1;

对于每个xEmployee在xEmpDetails.childNodes
col = 1
对于每个xChild在xEmployee.childNodes
工作表（NAMEOFTHESHEET）。单元格（col，row） .Value = xChild.Text
MsgBox xChild.baseName& & xChild.Text
col = col + 1;
下一个xChild
row = row + 1;
下一个xEmployee

相关方式应该是： / p>

LoadOption：= xlXmlLoadImportToList？

你从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
设置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 
  

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.

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.

I would like this to happen automatically within my current VBA code:

VBA looks like

      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

解决方案

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:

**published_date** |  **post_count**
20130201       |           18
20120201       |           15

so that we can assume that in your XML you will always have

<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).

Source: http://vba2vsto.blogspot.it/2008/12/reading-xml-from-vba.html

Employee.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

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

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.

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.

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?

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:

   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屋！