如何在VBA中处理可选的XML属性？

Question

我已经编写了一些代码，从XML文件导入数据到Excel中，它的工作方式是尝试读取不存在的属性时出现问题。这些属性在文件中是可选的，我无法添加它们，因此需要在代码中处理它们。

我尝试使用If Is Not Nothing处理对象，但这并不起作用，If <> ""或If <> Null也没有成功。

如果有人能给我任何帮助，我将非常感激。

Public Sub import()

    Dim oDoc As MSXML2.DOMDocument
    Dim fSuccess As Boolean
    Dim oRoot As MSXML2.IXMLDOMNode
    Dim oSoftkey As MSXML2.IXMLDOMNode
    Dim oAttributes As MSXML2.IXMLDOMNamedNodeMap
    Dim oSoftkeyName As MSXML2.IXMLDOMNode
    Dim oSoftkeyDescriptor As MSXML2.IXMLDOMNode
    Dim oSoftkeyStyleName As MSXML2.IXMLDOMNode

    Dim oChildren As MSXML2.IXMLDOMNodeList
    Dim oChild As MSXML2.IXMLDOMNode
    Dim intI As Integer
    On Error GoTo HandleErr

    Set oDoc = New MSXML2.DOMDocument

    oDoc.async = False
    oDoc.validateOnParse = False
    fSuccess = oDoc.Load(ActiveWorkbook.Path & "\keys.xml")

    If Not fSuccess Then
      GoTo ExitHere
    End If

    intI = 2
    ActiveSheet.Cells(1, 1).CurrentRegion.ClearContents
    ActiveSheet.Cells(1, 1) = "Name"
    ActiveSheet.Cells(1, 2) = "TextDescriptor"
    ActiveSheet.Cells(1, 3) = "StyleName"

    ' Get the root of the XML tree.
    ' Set oRoot = oDoc.DocumentElement
    Set oRoot = oDoc.SelectSingleNode("//IMS_Softkeys")

    ' Each IMS_Softkey in IMS_Softkeys
    For Each oSoftkey In oRoot.ChildNodes

      Set oAttributes = oSoftkey.Attributes

      Set oSoftkeyName = oAttributes.getNamedItem("Name")
      Set oSoftkeyDescriptor = oAttributes.getNamedItem("TextDescriptor")
      Set oSoftkeyStyleName = oAttributes.getNamedItem("StyleName")

      ActiveSheet.Cells(intI, 1).Value = oSoftkeyName.Text

      'Can't handle optional attribute "TextDescriptor" or "SoftkeyStyle"
      ActiveSheet.Cells(intI, 2).Value = oSoftkeyDescriptor.Text
      ActiveSheet.Cells(intI, 3).Value = oSoftkeyStyleName.Text

      intI = intI + 1
    Next oSoftkey
ExitHere:
    Exit Sub
HandleErr:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume ExitHere
    Resume
End Sub

一个XML文件示例（keys.xml）：

<BATCH>
  <IMS_BATCH>
    <IMS_Softkeys>
      <IMS_Softkey Name="Donut" StyleName="Mer-Green-Yellow" TextDescriptor="1 Donut" />
      <IMS_Softkey Name="Hotdog" StyleName="Mer-White-Black" TextDescriptor="11&quot; Hotdog" />
      <IMS_Softkey Name="Coke_Image" TextDescriptor="Coke" />
      <IMS_Softkey Name="DietCoke_Image" StyleName="Style for DietCocaCola" />
    </IMS_Softkeys>
  </IMS_BATCH>
</BATCH>

- sab0tage

Answer 1

它们是对象，在VBA中，您可以使用以下语法检查它们是否为空（已分配）

If Not (Object Is Nothing) Then

如果您想检查属性是否从XML中检索并分配，则可以：

' Print only if the `oSoftKeyDescriptor` is not nothing
If Not (oSoftkeyDescriptor Is Nothing) Then
    ActiveSheet.Cells(intI, 2).Value = oSoftkeyDescriptor.Text
End If

If Not (oSoftkeyStyleName Is Nothing) Then
    ActiveSheet.Cells(intI, 3).Value = oSoftkeyStyleName.Text
End If

我相信这是你想要的结果。

- user2140173

---

非常感谢，我之前使用了错误的 If Not Is Nothing 语法。现在它已经完美运行了。 - sab0tage

@sab0tage，我很高兴能够帮助你。在Stack Overflow上，我们通过“接受有帮助的答案”来表示感谢（http://stackoverflow.com/help/accepted-answer）。左侧答案旁边有一个绿色的勾号，你可以勾选它 :) - user2140173