CODEWITHSUNDEEP
excelxmlvba
Łukas Ptak
Łukas Ptak

Reputation: 47

VBA - check if xml node exists - if not use other one

I want to copy data from xml file to excel workbook Every xml has got tag "Client name" but some of them also have got tag "Billing client name" I want to achieve this with my code If in xml tag "Billing client name" exists use it and ignore tag "Client name" if in xml there is no tag "Billing client name" use data from tag "Client name" My current code only paste data from tag "Client name"

My code:

Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook

mainWorkBook.Sheets("Sheet3").Activate

LR = Cells(Rows.Count, "A").End(xlUp).Row
'MsgBox LR
For x = 2 To LR

    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.SetProperty "SelectionLanguage", "XPath"
    xmlDoc.Async = False
    xmlDoc.Load ("\\path\" & Range("A" & x) & ".xml")

    Set nodeXML = xmlDoc.getElementsByTagName("BILL_TO_CLIENTCODE")
    Set nodeXML2 = xmlDoc.getElementsByTagName("CLIENTCODE")

    If nodeXML Is Nothing Then

        Range("B" & x).Value = nodeXML(i).Text

    Else

        Range("B" & x).Value = nodeXML2(i).Text

    End If

Next x

If I change last lines of code to: 

If nodeXML Is Nothing Then
    Range("B" & x).Value = nodeXML2(i).Text
Else
    Range("B" & x).Value = nodeXML(i).Text
End If

I only get value from tag "Billing client name" and no value if this tag doesn't exists

sample xml

<InvoiceData xmlns="http://tempuri.org/InvoiceData.xsd">
   <INVOICE_HEADER>
        <BILL_TO_CLIENTCODE/>
        <CLIENTCODE>61138259</CLIENTCODE>

second one has got value in tag Bill to clientcode and in clientcode but Bill to clientcode should be primary.

Upvotes: 0

Views: 2885

Answers (1)

Tim Williams
Tim Williams

Reputation: 166366

xmlDoc.getElementsByTagName always return a collection, but that collection may be empty if there are no matches, so you need to test for (eg) 

 If nodeXML.Length > 0 Then

and not check whether nodeXML is Nothing

So something like this should work (untested):

Sub Tester()

    Dim mainWorkBook As Workbook, xmlDoc As Object
    Dim sht As Worksheet

    Set mainWorkBook = ActiveWorkbook
    Set sht = mainWorkBook.Sheets("Sheet3")

    For x = 2 To sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

        Set xmlDoc = CreateObject("Microsoft.XMLDOM")
        xmlDoc.SetProperty "SelectionLanguage", "XPath"
        xmlDoc.Async = False
        xmlDoc.Load ("\\path\" & Range("A" & x) & ".xml")

        sht.Range("B" & x).Value = PreferredValue(xmlDoc, _
                  Array("BILL_TO_CLIENTCODE", "CLIENTCODE"))

    Next x
End Sub

Function PreferredValue(doc As Object, arrTags)
    Dim t, col, rv
    For Each t In arrTags
        Set col = doc.getElementsByTagName(t)
        If col.Length > 0 Then
            rv = col(0).Text
            If Len(rv) > 0 Then Exit For '<<<< EDIT
        End If
    Next t
    PreferredValue = rv
End Function

Upvotes: 3

Related Questions