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

问题描述

我有一些通过 VBA 加载 XML 文件的 VBA.然而，当它被导入时，它全部在一列中，而不是分成一个表.

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.

当我通过数据"选项卡手动导入它时，我收到警告，没有架构，但询问我是否希望 Excel 基于源数据创建一个.然后将所有数据放在一个漂亮的表中.

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.

我希望这在我当前的 VBA 代码中自动发生:

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

VBA 看起来像

      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>

谢谢，

::最终解决方案::

 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

推荐答案

硬编码"方式是这样的:

从这里开始

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

并且你想获得一个有两列的excel:

and you want to obtain an excel with two column:

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

这样我们就可以假设在您的 XML 中您将始终拥有

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

<result><entry><Element>VALUE</Element><Element...n>VALUE</Element...n></entry>

重要提示:在 PowerPoint、Excel.. Word 中打开 VBA 编辑器并添加对Microsoft XML，v3.0"的引用(此引用适用于 Office 2000...您可能还有其他的).

来源: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>

阅读以上 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
条目 --> 员工在提供的代码中

result --> EmpDetails in the code provided
entry --> Employee in the code provided

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

plus any other necessary adjustment.

通过这种方式，您可以拥有任意数量的entry"和entry child"元素.

In this way you can have as much as many "entry" and "entry child" elements you want.

事实上，循环遍历条目"中的所有元素，您将获得 COLUMN，然后每个新条目都是一个新的 ROW.

In fact, looping through all the elements inside your "entry" you will get your COLUMN, then every new entry is a new ROW.

不幸的是，我在 MAC 上没有 excel，所以我只是把逻辑，你应该检查你自己的语法......这样你就可以在你想要的工作表上建立一个 EXCEL 表.

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

正确的方式应该是:

LoadOption:=xlXmlLoadImportToList?

LoadOption:=xlXmlLoadImportToList?

您是从 URL 调用中获取 XML，但我强烈建议在开始时尝试使用磁盘上的 XML 文件，并检查它是否正确有效.所以你应该做的是从这个WebService"中获取一个示例 XML，然后将它保存在磁盘上.尝试按以下方式加载它:

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