前端之家

  1. 首页
  2. 问答

如何使用VBA访问导入的XML中的特定值

• 问答

我有一个完整的.pdf表单（XFDF，XML），我将其导入excel。目的是从XML中获取该字段的“字段名称”和“值”。

目前，我可以使用以下简单命令来获取“值”（下图中的蓝色调）：

 list(4).text

我正在努力存储“字段名称”。在下面的图片中，该表达式/值以黄色突出显示。

我尝试了以下几种方法，但无法使其正常工作。

List(4).Attributes(1).value

关于如何访问嵌套值的任何提示？

列表->项（4）->属性->项（1）->值

Code and XML List Image

XFDF RAW：

<?xml version="1.0" encoding="utf-16"?>
<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
  <fields>
    <field name="3C_AQS_1">
      <value>2</value>
    </field>
    <field name="3C_AQS_2">
      <value>3</value>
    </field>
    <field name="3C_AQS_3">
      <value>1</value>
    </field>
    <field name="3C_AYS_1">
      <value>D:23440101</value>
    </field>
    <field name="3C_AYS_2">
      <value>D:13240101</value>
    </field>
    <field name="3C_AYS_3">
      <value>D:12340101</value>
    </field>
    <field name="3C_EEL_1">
      <value>2</value>
    </field>
    <field name="3C_EEL_2">
      <value>5</value>
    </field>
    <field name="3C_EEL_3">
      <value>2</value>
    </field>
    <field name="3C_JobProfile_1">
      <value>1235</value>
    </field>
    <field name="3C_JobProfile_2">
      <value>547</value>
    </field>
    <field name="3C_JobProfile_3">
      <value>1234 fd</value>
    </field>
    <field name="4_PME_DBT">
      <value>yuktyu</value>
    </field>
    <field name="4_PME_Launch">
      <value>ew4tw</value>
    </field>
    <field name="4_PME_Scope">
      <value>serg</value>
    </field>
    <field name="4_PPT_End">
      <value>D:19930714</value>
    </field>
    <field name="4_PPT_Start">
      <value>D:19001201</value>
    </field>
    <field name="Capital_LifeAmount">
      <value>5352</value>
    </field>
    <field name="Capital_Yr">
      <value>1</value>
    </field>
    <field name="Capital_YrAmount">
      <value>123124</value>
    </field>
    <field name="Capital_YrAmountP">
      <value>234</value>
    </field>
    <field name="CostConfidence">
      <value>Med</value>
    </field>
    <field name="Devliverables_1">
      <value>Delev 1</value>
    </field>
    <field name="Devliverables_2">
      <value>Delev 2</value>
    </field>
    <field name="Devliverables_3">
      <value>Delev 3</value>
    </field>
    <field name="Devliverables_4">
      <value>Delev 4</value>
    </field>
    <field name="Devliverables_5">
      <value>Delev 5</value>
    </field>
    <field name="HR Initiative">
      <value>HrInit</value>
    </field>
    <field name="InScope_1">
      <value>InScope 1</value>
    </field>
    <field name="InScope_2">
      <value>InScope 2</value>
    </field>
    <field name="InScope_3">
      <value>InScope 3</value>
    </field>
    <field name="InScope_4">
      <value>InScope 4</value>
    </field>
    <field name="InScope_5">
      <value>InScope 5</value>
    </field>
    <field name="In_HR_Area_1">
      <value>University Relations</value>
    </field>
    <field name="In_HR_Area_2">
      <value>Talent Development</value>
    </field>
    <field name="In_HR_Area_3">
      <value>Change Management</value>
    </field>
    <field name="In_HR_Area_4">
      <value>Global Compensation </value>
    </field>
    <field name="In_HR_Area_5">
      <value>Career Framework</value>
    </field>
    <field name="In_HR_NumEmp_1">
      <value>1</value>
    </field>
    <field name="In_HR_NumEmp_2">
      <value>42345</value>
    </field>
    <field name="In_HR_NumEmp_3">
      <value>12</value>
    </field>
    <field name="In_HR_NumEmp_4">
      <value>12</value>
    </field>
    <field name="In_HR_NumEmp_5">
      <value>11</value>
    </field>
    <field name="In_HR_NumMonth_1">
      <value>1</value>
    </field>
    <field name="In_HR_NumMonth_2">
      <value>3</value>
    </field>
    <field name="In_HR_NumMonth_3">
      <value>4</value>
    </field>
    <field name="In_HR_NumMonth_4">
      <value>5</value>
    </field>
    <field name="In_HR_NumMonth_5">
      <value>1</value>
    </field>
    <field name="In_HR_PerTime_1">
      <value>2</value>
    </field>
    <field name="In_HR_PerTime_2">
      <value>5</value>
    </field>
    <field name="In_HR_PerTime_3">
      <value>2</value>
    </field>
    <field name="In_HR_PerTime_4">
      <value>11</value>
    </field>
    <field name="In_HR_PerTime_5">
      <value>03</value>
    </field>
    <field name="Opex_LifeAmount">
      <value>23424</value>
    </field>
    <field name="Opex_Yr">
      <value>12</value>
    </field>
    <field name="Opex_YrAmount">
      <value>123123</value>
    </field>
    <field name="Opex_YrAmountP">
      <value>345235</value>
    </field>
    <field name="OutScope_1">
      <value>outScope 1</value>
    </field>
    <field name="OutScope_2">
      <value>outScope 2</value>
    </field>
    <field name="OutScope_3">
      <value>outScope 3</value>
    </field>
    <field name="OutScope_4">
      <value>outScope 4</value>
    </field>
    <field name="OutScope_5">
      <value>outScope 5</value>
    </field>
    <field name="Out_HR_Area_1">
      <value>sdfsg</value>
    </field>
    <field name="Out_HR_Area_2">
      <value>dffnjgmkgm,k</value>
    </field>
    <field name="Out_HR_Area_3">
      <value>3453wygh</value>
    </field>
    <field name="Out_HR_Area_4">
      <value>235hc bn</value>
    </field>
    <field name="Out_HR_Area_5">
      <value>234dfg435</value>
    </field>
    <field name="Out_HR_NumEmp_1">
      <value>1</value>
    </field>
    <field name="Out_HR_NumEmp_2">
      <value>6</value>
    </field>
    <field name="Out_HR_NumEmp_3">
      <value>34</value>
    </field>
    <field name="Out_HR_NumEmp_4">
      <value>2</value>
    </field>
    <field name="Out_HR_NumEmp_5">
      <value>78</value>
    </field>
    <field name="Out_HR_NumMonth_1">
      <value>23</value>
    </field>
    <field name="Out_HR_NumMonth_2">
      <value>23</value>
    </field>
    <field name="Out_HR_NumMonth_3">
      <value>1</value>
    </field>
    <field name="Out_HR_NumMonth_4">
      <value>235</value>
    </field>
    <field name="Out_HR_NumMonth_5">
      <value>52</value>
    </field>
    <field name="Out_HR_PerTime_1">
      <value>3</value>
    </field>
    <field name="Out_HR_PerTime_2">
      <value>1</value>
    </field>
    <field name="Out_HR_PerTime_3">
      <value>11</value>
    </field>
    <field name="Out_HR_PerTime_4">
      <value>1</value>
    </field>
    <field name="Out_HR_PerTime_5">
      <value>1</value>
    </field>
    <field name="Problem Statement">
      <value>Prob Statemewnt dghdheh</value>
    </field>
    <field name="ProjSponName_First">
      <value>ProjSponFirst</value>
    </field>
    <field name="ProjSponName_Last">
      <value>ProjSponLast</value>
    </field>
    <field name="Project Description">
      <value>Project Desc</value>
    </field>
    <field name="Project Name">
      <value>ProjName</value>
    </field>
    <field name="ProposedMeasure_1">
      <value>Prop meas 1 </value>
    </field>
    <field name="ProposedMeasure_2">
      <value>prop meas 2</value>
    </field>
    <field name="ProposedMeasure_3">
      <value>prop meas 3 </value>
    </field>
    <field name="ProposedMeasure_4">
      <value>prop meas 4</value>
    </field>
    <field name="ProposedMeasure_5">
      <value>prop meas 5</value>
    </field>
    <field name="Savings_Yr">
      <value>2</value>
    </field>
    <field name="Savings_YrAmount">
      <value>12313</value>
    </field>
    <field name="Savings_YrAmountP">
      <value>234254</value>
    </field>
    <field name="Submit" />
    <field name="SubmitByName_First">
      <value>SubNameFirst</value>
    </field>
    <field name="SubmitByName_Last">
      <value>SubNameLast</value>
    </field>
    <field name="TempHeadCount">
      <value>2</value>
    </field>
    <field name="TempMonths">
      <value>4</value>
    </field>
  </fields>
  <ids original="FA098893AF1E1140A46335ED9EEEA5CE" modified="A3D8A6418EFE9E439F3CADCF3350958D" />
</xfdf>

EXCEL VBA RAW：

Sub AddFormData()    

    Dim FName As String,FD As FileDialog
    Dim WApp As Object,WDoc As Object,WDR As Object
    Dim ExR As Range    

    Set FD = Application.FileDialog(msoFileDialogOpen)
    FD.Filters.Add "PDF FORM DATA","*.xfdf",1
    FD.Show

    If FD.SelectedItems.Count <> 0 Then        
        For yi = 1 To FD.SelectedItems.Count
            FName = FD.SelectedItems(yi)

            '''''''''''''''''''''''''''''''''''''''''
            Set oXMLFile = CreateObject("MSXML2.DOMDocument") '''“microsoft.XMLDOM”)
            XMLFileName = FName
            oXMLFile.Load (XMLFileName)

            Set List = oXMLFile.SelectNodes("//fields/")    

            ''''''''''''''''''''''''''''''''''''''''

            '''''''''''''''''''''''''''''''''''''
        Next yi    
    End If    
End Sub
baogang888999 回答：如何使用VBA访问导入的XML中的特定值

考虑XPath，它是一种比向下浏览.Item，.ChildNodes，.Attributes和其他元素集合时更具表现力的解析工具。另外，您在XML文档中有一个默认的命名空间xmlns="..."。要解决此问题，请为默认名称空间分配一个用冒号分隔的前缀，例如 pdf ，以访问其范围内的所有节点。下面是一个通用版本，用于遍历树的嵌套级别：

Set oXMLFile = CreateObject("MSXML2.DOMDocument") '''“Microsoft.XMLDOM”)
XMLFileName = FName
oXMLFile.Load XMLFileName

' TEMPORARILY DEFINE PREFIX FOR DEFAULT NAMESPACE
oXMLFile.setProperty "SelectionNamespaces","xmlns:pdf='http://ns.adobe.com/xfdf/'"

' PRINT ALL THIRD NESTED LEVEL NODE VALUES
Set myList = oXMLFile.SelectNodes("/*/*/*")
For Each var In myList
    Debug.Print var.Text
Next var
' 2
' 3
' 1
' D:23440101
' D:13240101
' D:12340101

' PRINT ALL THIRD NESTED LEVEL ATTRIBUTES VALUES
Dim var as Variant
...
Set myList = oXMLFile.SelectNodes("/*/*/*/@*")
For Each var In myList
    Debug.Print var.Text
Next var
' 3 C_AQS_1
' 3 C_AQS_2
' 3 C_AQS_3
' 3 C_AYS_1
' 3 C_AYS_2
' 3 C_AYS_3

如果需要访问索引值，仍然可以使用XPath作为节点或属性值

' PRINT INDEXED NODE AND ATTRIBUTE
Set myList = oXMLFile.SelectNodes("/*/*/*")         ' MOVE TO SECOND NESTED LEVEL
Debug.Print myList(0).SelectSingleNode("*").Text
Debug.Print myList(0).SelectSingleNode("@*").Text
' 2
' 3 C_AQS_1

Debug.Print myList(1).SelectSingleNode("*").Text
Debug.Print myList(1).SelectSingleNode("@*").Text
' 3
' 3 C_AQS_2

Debug.Print myList(2).SelectSingleNode("*").Text
Debug.Print myList(2).SelectSingleNode("@*").Text
' 1
' 3 C_AQS_3

并循环

Dim var as Variant
...
For Each var In myList
    If Not var.SelectSingleNode("*") Is Nothing Then
        Debug.Print var.SelectSingleNode("*").Text
        Debug.Print var.SelectSingleNode("@*").Text
    End If
Next var
excelvbaxfdfxml
本文链接：https://www.f2er.com/3164505.html

大家都在问