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