MSXML2.DOMDocument load function fails in VBA

Question

I've been struggling with the below issue for a while now and couldn't find the solution yet.

There is an iShare page with an XML file that I want to download using VBA code, then later process the XML file and save into MS Access database.

I've been using the below code for about 4 years now, it worked perfectly without any issues. But suddenly it stopped working this week. Any ideas why?

the code:

Private Function GetRequests() As Boolean
  On Error GoTo ErrHandler

  Dim oDoc As MSXML2.DOMDocument
  Dim Url As String
  Dim sFileName As String

  Set oDoc = New MSXML2.DOMDocument
  oDoc.async = False
  Url = cUrlDatabase & "/" & cApplicationName & "/In/" & cReqXmlFile

  UpdateStatus "Loading " & cReqXmlFile

  If Not oDoc.Load(Url) Then
    c_sLastError = "Could not load XML " & Url
    GoTo EndProc
  End If

  sFileName = sPath & "\Data\requests.xml"

  oDoc.Save sFileName

  GetRequests = True
End Function

The code fails at the oDoc.Load(Url) part, it comes back false.

Answer 1

Here's an example of how to gather error details:

      Dim xDoc As MSXML.DOMDocument
      Set xDoc = New MSXML.DOMDocument

      If xDoc.Load("C:\My Documents\cds.xml") Then
        ' The document loaded successfully.

      Else
        ' The document failed to load.

        Dim strErrText As String
        Dim xPE As MSXML.IXMLDOMParseError

        ' Obtain the ParseError object
        Set xPE = xDoc.parseError
        With xPE
            strErrText = "Your XML Document failed to load" & _
            "due the following error." & vbCrLf & _
            "Error #: " & .errorCode & ": " & xPE.reason & _
            "Line #: " & .Line & vbCrLf & _
            "Line Position: " & .linepos & vbCrLf & _
            "Position In File: " & .filepos & vbCrLf & _
            "Source Text: " & .srcText & vbCrLf & _
            "Document URL: " & .url
        End With

        MsgBox strErrText, vbExclamation End If

        Set xPE = Nothing
      End If

Example taken from here.

Answer 2

For other people finding this post: The xml parser by now has implemented different error types (see here). You would have to use the following code

Set objXML = CreateObject("Msxml2.DOMDocument.6.0")
ObjXML.async=true
objXML.load "/path/to/xml"
If objXML.parseError.errorCode <> 0 Then
   MsgBox "Error was " + objXML.parseError.reason
End If

This should help you debug your .xml file.