Excel vba domdocument parsing xml from TNT tracking system in some pcs object load return no document

0 votes

I use the following query URL as an example to parse an XML document that I received from TNT courier's tracking system: https://www.tnt.it/tracking/getXMLTrack?WT=1&ConsigNos=RL38536236

Prior to yesterday, every PC had the functionality working as intended. The DocumentElement property is null and the Load(URL) of the DOMDocument object produces a false result, The curious thing is that the XML displays perfectly if I navigate to that site using Firefox, Chrome, Edge, or Internet Explorer.

Code:

Function TrackTNTlist(LDV As String) As Collection

Dim TNTlist As New Collection

Dim Obj As MSXML2.DOMDocument60
Dim Verifica As Boolean
Dim XMLTNT As String

Dim NodoLista As IXMLDOMNodeList
Dim NodoSingolo As IXMLDOMNode
Dim Nome As IXMLDOMNode
Dim DataConsegna As IXMLDOMNode
Dim NomeRicevente As IXMLDOMNode
Dim Destinatario As IXMLDOMNode
Dim ConsignmentDetails As IXMLDOMNode
Dim DataPrevConsegna As IXMLDOMNode
Dim NuovaLDV As IXMLDOMNode
Dim Dest As String, DatiSped As String

On Error GoTo RigaErrore

XMLTNT = "https://www.tnt.it/tracking/getXMLTrack?WT=1&ConsigNos=" & LDV

Set Obj = New MSXML2.DOMDocument60
Obj.async = False
Verifica = Obj.Load(XMLTNT)

If Verifica = True Then
    MsgBox "File XML " & XMLTNT & "loaded"
Else
    MsgBox "File XML NOT loaded"
    TNTlist.Add "ERROR - XML tracking data not loaded"
    Exit Function
End If

Set NodoSingolo = Obj.DocumentElement.SelectSingleNode("Consignment/StatusDetails/StatusDescription")

If NodoSingolo Is Nothing Then

    TNTlist.Add "LDV non trovata"

Else
    Set NodoList = Obj.DocumentElement.SelectNodes("Consignment/StatusDetails")
    Set ConsignmentDetails = Obj.DocumentElement.SelectSingleNode("Consignment/ConsignmentDetails")

    DatiSped = ""
    DatiSped = "LETTERA DI VETTURA: " & LDV & Chr(10)
    
    If Not ConsignmentDetails Is Nothing Then
        DatiSped = DatiSped & "RIF. MITTENTE: " & ConsignmentDetails.ChildNodes(0).Text & Chr(10)
        DatiSped = DatiSped & "TIPO SERVIZIO: " & ConsignmentDetails.ChildNodes(1).Text & Chr(10)
        DatiSped = DatiSped & "NUM. COLLI: " & ConsignmentDetails.ChildNodes(3).Text & Chr(10)
    End If
    
    
    Set NodoSingolo = Obj.DocumentElement.SelectSingleNode("Consignment/StatusDetails/StatusDescription")
    
    Dest = ""
    Set DataConsegna = Obj.DocumentElement.SelectSingleNode("Consignment/DeliveryDate")
    Set NomeRicevente = Obj.DocumentElement.SelectSingleNode("Consignment/CollectionName")
    Set Destinatario = Obj.DocumentElement.SelectSingleNode("Consignment/ReceiverDetails")
    Set DataPrevConsegna = Obj.DocumentElement.SelectSingleNode("Consignment/DueDate")
    Set NuovaLDV = Obj.DocumentElement.SelectSingleNode("Consignment/HeldInDepotDetails/HID1ReplacingDoc")
    
    If NodoSingolo.Text = "Spedizione consegnata" Then
        Dest = "CONSEGNATA A: " & Chr(13)
    Else
        Dest = "PREVISTA CONSEGNA A: " & Chr(10)
    End If
    
    If Not Destinatario Is Nothing Then
        Dest = Dest & Destinatario.ChildNodes(4).Text
        Dest = Dest & " (" & Destinatario.ChildNodes(6).Text & ")" & Chr(10)
    End If
    
    If Not DataPrevConsegna Is Nothing Then
        Dest = Dest & DataPrevConsegna.ChildNodes(0).Text & Chr(10)
    End If

    
    If Not DataConsegna Is Nothing Then
        Dest = Dest & "Data consegna: " & DataConsegna.Text & Chr(10)
    End If
    
    If Not NomeRicevente Is Nothing Then
        Dest = Dest & "Ha ritirato: " & NomeRicevente.Text & Chr(10)
    End If
    
    If Not NuovaLDV Is Nothing Then
        Dest = Dest & "NUOVA LETTERA DI VETTURA: " & NuovaLDV.Text & Chr(10)
    End If
    
    Dest = Dest & "Dettaglio tracking:" & Chr(10)
    
    TNTlist.Add DatiSped & Chr(10) & Dest & Chr(10)
    
    For Each Nome In NodoList
        
        TNTlist.Add Nome.ChildNodes(1).Text
        TNTlist.Add Nome.ChildNodes(2).Text
    
    Next
    
End If

salto = 1
If salto <> 1 Then
    Set NodoSingolo = Obj.DocumentElement.SelectSingleNode("Consignment/StatusDetails/StatusDescription")
    
    
    If NodoSingolo Is Nothing Then
        TNTlist.Add "LDV non trovata"
    Else
        If NodoSingolo.Text = "Spedizione consegnata" Then

            Set DataConsegna = Obj.DocumentElement.SelectSingleNode("Consignment/DeliveryDate")
            Set NomeRicevente = Obj.DocumentElement.SelectSingleNode("Consignment/CollectionName")
            Set Destinatario = Obj.DocumentElement.SelectSingleNode("Consignment/ReceiverDetails")
            Dest = Destinatario.ChildNodes(4).Text
            Dest = Dest & " (" & Destinatario.ChildNodes(5).Text & ")"
            
            TNTlist.Add NodoSingolo.Text & " : " & Dest & " - " & NomeRicevente.Text & " - " & DataConsegna.Text
            TNTlist.Add DataConsegna.Text
        
        End If
    
    End If
End If

Set TrackTNTlist = TNTlist

Exit Function

RigaErrore:
MsgBox Err.Number & vbNewLine & Err.Description
Application.EnableEvents = True
Resume Next

End Function

Below are two screenshots, one from a computer where the function works correctly and one from another where the problem occurs. The problem only affects a small number of computers, all of which have identical system configurations. screenshot showing a problem with proper execution:

debug screenshot of correct execution

debug screenshot of error execution

When visiting the URL on both PCs, the XML is displayed appropriately. Could someone please explain the potential causes of the issue to me?

Sep 20 in Others by Kithuzzz
• 12,240 points
35 views

1 answer to this question.

0 votes
This error is related to something in the XML which is not wrong. please check if you have an LDV which generates an error on some PCs. Please have a look at DOMDocument.parseError. Simplest usage in your code: MsgBox "File XML NOT loaded. Reason: " & Obj.parseError.reason.
answered Sep 21 by narikkadan
• 20,880 points

Related Questions In Others

0 votes
0 answers

Remove formulas from all worksheets in Excel using VBA

I'm having trouble getting Excel to remove ...READ MORE

2 hours ago in Others by Kithuzzz
• 12,240 points
1 view
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 18,960 points
337 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
71 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
114 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered 6 days ago in Others by narikkadan
• 20,880 points
26 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
402 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,095 views
0 votes
0 answers

how do i perform XML parsing in python?

can you give me the python code ...READ MORE

Apr 10, 2019 in Python by Waseem
• 4,540 points
235 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 20,880 points
22 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21 in Others by narikkadan
• 20,880 points
17 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP