VBA Tutorials

This document will demonstrate building a client in 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

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

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

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