How to handle optional XML attributes in VBA?

Question

I wrote some code to import some data from an XML file into excel, it works until it tries to read attributes that do not exist; they are optional in the file, and I cannot add them, so I need to process it in the code.

I tried to process objects using If Is Not Nothing , but it will not work, and it does not matter If <> "" or If <> Null .

If anyone can help me, I would be very grateful.

 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 

Example XML file (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> 

Answer 1

These are objects and in VBA you check to see if they are empty (were assigned) using the following syntax

If Not (Object Is Nothing) Then

So, if you want to check if the attributes were retrieved and assigned from XML, you can:

 ' 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 

and I believe that this is the result that you after