Page Comparison

This document will demonstrate building a client in Visual Basic for Applications (VBA), using Microsoft Excel 2010.

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:

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:

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

Save workbook to a known location

Create or download the following SOAP formatted XML document:

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

After running, check the results by looking in the same directory as the work book for the file getLandingReport_Example_Response.xml These examples were, in part, provided by a Third Party Developer.  The eLandings development team has very little experience with VBA and can not guarantee these examples demonstrate Best Practices.  These examples are very limited in scope and we do not anticipate expanding them.  You may peruse the Java Tutorials for a more thorough demonstration of Use Cases, integrating with the eLandings Public Webservices.

Child pages (Children Display)