Page Comparison

...

Requesting XML for a Landing Report, with a given SOAP file

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:

Code Block

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
    .send fso.OpenTextFile(varFile, ForReading, True, 0).ReadAll
    Dim xmlDoc As MSXML2.DOMDocument30
    Set xmlDoc = .responseXML
    fso.OpenTextFile(strResponseFile, ForWriting, True, 0).Write xmlDoc.Text
End With
End Function

Add a Subroutine to call the Function into the Module:

Code Block

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

Save workbook to a known location

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

Create or download the following SOAP formatted XML document:

Code Block

<?xml version="1.0"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">	
	<soap12:Body>		
		<getLandingReport xmlns="http://webservices.er.psmfc.org/">
			<arg0 xmlns="">amackerel</arg0>
			<arg1 xmlns="">A_mackerel</arg1>
			<arg2 xmlns="">2.6</arg2>
			<arg3 xmlns="">15342006</arg3>		
		</getLandingReport>	
	</soap12:Body>
</soap12:Envelope>

Save/Move the XML document to the same location as the work book.

Reference Microsoft Scripting Runtime by Microsoft XML, v3.0 and

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).

Note
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

Versions Compared

Old Version 3

New Version 4

Key

Requesting XML for a Landing Report, with a given SOAP file