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