I have Excel VBA code that makes a SOAP call to a web service, and extracts the results from the XML response, then writes the values to WorkSheet cells. It was a long painful process, but eventually I got this working, and it appears fairly robust. However, I will soon be releasing this code to a larger user base (it's in limited release right now), and I'd like to add more "on error" error-handling to avoid any runtime errors.
In particular, I need advice/help about adding error-handling to the code that uses selectSingleNode. I do check it afterward to ensure it's not empty, but obviously, that doesn't cover the situation in which there may be an error in the "Set objXmlNode..." statement. I'd like to keep the "if objXmlNode Is Nothing" code (if y'all think that makes sense). How can I add "on error" code to this code?
'...[more code here]
Dim objXmlHttp As MSXML2.XMLHTTP60
Static objXmlDoc As MSXML2.DOMDocument60
Dim objXmlNode As MSXML2.IXMLDOMNode
'...[more code here]
Set objXmlNode = objXmlDoc.selectSingleNode("//*[local-name()='commandExitCode' and namespace-uri()='ABC_IPAM:IPAM_methods']/text()")
If objXmlNode Is Nothing Then
bProvisionFailFlag = True
strCommandExitCode = "Nothing"
strProvisionerResultsMsg = strProvisionerResultsMsg & "ERROR: Unable to extract exit code from web service (i.e., objXmlNode is Nothing, commandExitCode node selection came up empty)" & vbLf
Else
strCommandExitCode = objXmlNode.Text
End If
'...[more code here]
P.S. I already have "on error" code in place and tested for the instantiation of the MSXML2.XMLHTTP60 and MSXML2.DOMDocument60 objects, but omitted it here for brevity.
Bookmarks