Requesting XML for a Landing Report, with a programatic generated SOAP document

(You may download this example, but do not forget to Enable Macro Content the first time you open it.)

Open Microsoft Excel

Click the Visual Basic Editor button in the top left hand corner (Alternatively, click Alt-F11)

In Insert Menu select Module (Alternatively, click Alt-i, and then Alt-m)

Add a Function into the Module, for the web service call:

Function getLandingReportWith(varServer As Variant, varFile As Variant)
Dim XWebService As Object 'XMLHTTP'
Dim fso As FileSystemObject
Dim strResponseFile As String
Set XWebService = CreateObject("Microsoft.XMLHTTP")
Set fso = CreateObject("Scripting.FileSystemObject")
With XWebService
    .Open "POST", varServer, False
    strResponseFile = Replace(varFile, ".xml", "_Response.xml")
    On Error Resume Next
    Kill strResponseFile
    On Error GoTo 0
    
    Dim node
    Dim xmlSrcDoc As New MSXML2.DOMDocument30
    xmlSrcDoc.async = False
    xmlSrcDoc.validateOnParse = False
    xmlSrcDoc.resolveExternals = False
    xmlSrcDoc.preserveWhiteSpace = True
    Set node = xmlSrcDoc.createProcessingInstruction("xml", "version=""1.0""")
    ' issue: cannot do this: node.setAttribute "encoding", "utf-8"
    xmlSrcDoc.appendChild (node)
    Set node = Nothing
    ' Create the Soap Envelope
    Dim soapEnvelope As IXMLDOMElement
    Set soapEnvelope = xmlSrcDoc.createElement("soap12:Envelope")
    ' Create the attributes for it
    soapEnvelope.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"
    soapEnvelope.setAttribute "xmlns:xsd", "http://www.w3.org/2001/XMLSchema"
    soapEnvelope.setAttribute "xmlns:soap12", "http://www.w3.org/2003/05/soap-envelope"
    xmlSrcDoc.appendChild soapEnvelope
    ' Create the Soap Body
    Dim soapBody As IXMLDOMElement
    Set soapBody = AddElement(xmlSrcDoc, soapEnvelope, "soap12:Body", Empty, Empty, Empty)
    ' Create the node for the call we are making
    Dim soapCall As IXMLDOMElement
    Set soapCall = AddElement(xmlSrcDoc, soapBody, "getLandingReport", "xmlns", "http://webservices.er.psmfc.org/", Empty)
    ' finally add the argument(s)
    AddElement xmlSrcDoc, soapCall, "arg0", "xmlns", "", "amackerel"
    AddElement xmlSrcDoc, soapCall, "arg1", "xmlns", "", "A_mackerel"
    AddElement xmlSrcDoc, soapCall, "arg2", "xmlns", "", "2.7"
    AddElement xmlSrcDoc, soapCall, "arg3", "xmlns", "", "15342006"
    
    .send (xmlSrcDoc.XML)
    
    Dim xmlDoc As MSXML2.DOMDocument30
    Set xmlDoc = .responseXML
    fso.OpenTextFile(strResponseFile, ForWriting, True, 0).Write xmlDoc.Text
End With
End Function

Add a helper function:

Function AddElement(xmlSrcDoc As Variant, parentElement As IXMLDOMElement, newElementName As String, attributeName As Variant, attributeValue As Variant, argValue As Variant) As IXMLDOMElement
    Dim newElement As IXMLDOMElement
    Set newElement = xmlSrcDoc.createElement(newElementName)
    
    If Not attributeName = Empty Then
        newElement.setAttribute attributeName, attributeValue
    End If
    
    If Not argValue = Empty Then
        newElement.Text = argValue
    End If
    parentElement.appendChild newElement
    Set AddElement = newElement
End Function

Add a Subroutine, in the Module, to call the Function:

Sub getLandingReport()
	Call getLandingReportWith("http://elandingstest.alaska.gov/ElectronicReportingWebServices/ProcessorReportManagementService?wsdl", ThisWorkbook.Path & "\getLandingReport_Example.xml")
End Sub

Save workbook to a known location

When saving, be sure to select the XLSM file type.

Reference Microsoft Scripting Runtime by Microsoft XML, v3.0 and Save/Move the XML document to the same location as the work book.

In Tools menu, select References (Alternatively, click Alt-t and then Alt-r)
- If References is disabled, click the blue reset square in the tool bar (Alternatively, click Alt-r and then Alt-r)
In Available References, scroll to 'Microsoft Scripting Runtime' and enable the check box.
Sroll to Microsoft XML, v3.0 and enable the check box
Click the OK button

Run Subroutine by clicking the green run arrow in the tool bar (Alternatively, click F5).

If it prompts you to select a Macro, select getLandingReport and click Run.

After running, check the results by looking in the same directory as the work book for the file getLandingReport_Example_Response.xml