Microsoft KB Archive/307033

= How To Use an XML Web Service by Using ASP.NET from an Office VBA Macro in Word or Excel =

Article ID: 307033

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft ASP.NET 1.0
 * Microsoft Word 2000 Standard Edition
 * Microsoft Web Services Enhancements for Microsoft .NET 2.0

-



This article was previously published under Q307033



SUMMARY
This article demonstrates how to use an XML Web service using ASP.NET from a Visual Basic for Applications (VBA) macro in Word or Excel.



MORE INFORMATION
In order to successfully communicate with an XML Web service using ASP.NET from an Office macro, the SOAP Toolkit must be installed on the client computer on which the macro runs. Accordingly, the SOAP Toolkit is required for the demonstration in this article. For details on the SOAP Toolkit, including download instructions, see the following Microsoft Developer Network (MSDN) Web site:

SOAP Toolkit 2.0 SP2

http://msdn.microsoft.com/webservices/building/soaptk/

Create the XML Web Service using ASP.NET
 Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. In the New Project dialog box, click Visual Basic Projects under Project types, and then click XML Web Service using ASP.NET under Templates. Name the project SQLQuery and click OK. The design form for Service1 appears by default. On the View menu, click Code to display the code window for Service1.  Paste the following code at the top of the code window: Imports System.Data.SqlClient   Paste the following code in the Service1 class (just before End Class).

Note You must change User ID and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Private Const strConn = &quot;User ID= ;Password= ;Initial Catalog=pubs;Data Source=localhost&quot;

 Public Function GetIDs As String Dim i As Integer

' Create an open connection. Dim oConn As New SqlConnection(strConn) oConn.Open

Dim oDataset As New System.Data.DataSet ' Execute the query. Dim oAdapter As New SqlDataAdapter(&quot;SELECT au_id FROM authors&quot;, oConn) ' Fill the dataset. oAdapter.Fill(oDataset)

Dim s(oDataset.Tables(0).Rows.Count - 1) As String ' Create an array of IDs. For i = 0 To oDataset.Tables(0).Rows.Count - 1 s(i) = oDataset.Tables(0).Rows(i).ItemArray.GetValue(0) Next i   ' Return the array. Return s End Function

 Public Function QueryDatabase(ByVal sID As String) As String Dim i As Integer

' Create an open a connection. Dim oConn As New SqlConnection(strConn) oConn.Open

Dim oCommand As New SqlCommand(&quot;SELECT * FROM authors WHERE au_id='&quot; + sID + &quot;'&quot;, oConn) Dim oReader As SqlDataReader ' Execute the query and assign results to a SqlDataReader. oReader = oCommand.ExecuteReader oReader.Read

Dim s(7) As String ' Build an array of results. For i = 0 To 7 s(i) = CType(oReader.GetValue(i), String) Next ' Return the array. Return s End Function NOTE: Modify the strConn constant in the code so that it is a valid connection string for the SQL Server pubs database. 

Test the Web Service

 * 1) Press F5 to build and run the Web service solution. When you run the Web service from the .NET Integrated Development Environment (IDE), Microsoft Internet Explorer loads Service1.asmx from your solution.
 * 2) Test the GetIDs method. To do this, click the GetIDs hyperlink, then click Invoke.

The method returns a list of author IDs that the Web service extracted from the pubs database. The results are displayed in the browser as XML.
 * 1) Close the browser window or windows and return to Visual Studio.
 * 2) Press F5 again to run the Web service.
 * 3) Test the QueryDatabase method. To do this, click the QueryDatabase hyperlink, then type 409-56-7008 for the sID parameter and click Invoke.

The method returns the details for the author with the ID 409-56-7008. The results are displayed in the browser as XML.
 * 1) Close the browser window or windows to end the Web service.

Use the Web Service from Word
This sample uses the Elegant Fax template that ships with Word. This sample does not work without this template. To install the template, follow these steps:
 * 1) Start the Office Setup wizard.
 * 2) In the wizard, click Add or Remove Features.
 * 3) Under Features to Install, expand Microsoft Excel for Windows. Click Spreadsheet Templates, click Run from My Computer, and then click Update.

To use the Web service from Word, follow these steps:  Start Word. A blank document is created by default.</li> On the Tools menu, click Macro and then click Visual Basic Editor. On the Insert menu, click Module to insert a blank code module.</li> On the Tools menu, click References.</li> In the References dialog box, Select Microsoft SOAP Type Library, and then click OK.

Note If the Microsoft SOAP Type Library is not available in the References dialog box, then click Browse. In the Add Reference dialog box browse to the directory C:\Program Files\Common Files\MSSoap\Binaries and select mssoap1.dll. Click Open and then click OK.</li>  Paste the following code in the code module: Public Const sServer = &quot;localhost&quot;

Sub GenerateForm(sID As String) Dim oSOAPClient As Object On Error GoTo errhand Set oSOAPClient = CreateObject(&quot;MSSOAP.SoapClient&quot;) oSOAPClient.mssoapinit &quot;http://&quot; + sServer + &quot;/SQLQuery/Service1.asmx?wsdl&quot;, &quot;Service1&quot;, &quot;Service1Soap&quot; Dim arrTemp As String arrTemp = oSOAPClient.QueryDatabase(sID) Dim oDoc As Word.Document Set oDoc = Application.Documents.Add(&quot;Elegant Fax.dot&quot;) oDoc.Bookmarks(&quot;Company&quot;).Range.Text = arrTemp(2) + &quot; &quot; + arrTemp(1) + vbCrLf + _ arrTemp(4) + vbCrLf + arrTemp(5) + &quot;, &quot; + arrTemp(6) + &quot; &quot; + arrTemp(7) + vbCrLf + _ arrTemp(3) Exit Sub errhand: MsgBox &quot;Error #&quot; + Err.Number + &quot;: &quot; + Err.Description End Sub

Sub ShowForm ' Show the dialog box to the user. UserForm1.Show End Sub NOTE: Modify the sServer constant to point to the server that is hosting the Web service that you just created.

</li> On the Insert menu, click UserForm to insert a blank user form.</li> Place a large list box and a command button on the form.</li> On the View menu, click Code to change to the code window for the user form.</li>  Replace the contents of the code window with the following: Private Sub CommandButton1_Click ' Generate a FAX based on the ID. GenerateForm ListBox1.List(ListBox1.ListIndex) UserForm1.Hide End Sub

Private Sub UserForm_Initialize Dim oSOAPClient As Object ' Create a SOAP client. Set oSOAPClient = CreateObject(&quot;MSSOAP.SoapClient&quot;) ' Initialize the SOAP client to the Web service. oSOAPClient.mssoapinit &quot;http://&quot; + Module1.sServer + &quot;/SQLQuery/Service1.asmx?wsdl&quot;, &quot;Service1&quot;, &quot;Service1Soap&quot; Dim arrTemp As String ' Get an array of IDs. arrTemp = oSOAPClient.GetIDs

' Fill the list box with IDs. For i = 0 To UBound(arrTemp) ListBox1.AddItem arrTemp(i) Next End Sub </li> Close the VBA Editor to return to the document.</li> On the Tools menu, click Macro and then click Macros. In the Macros dialog box, run the ShowForm macro to display the list of IDs. Select an ID from the list and click the command button to generate a fax document with that user's information.</li></ol>

Use the Web Service from Excel
This sample uses the Sales Invoice template that ships with Excel. This sample does not work without this template. To install the template, follow these steps:
 * 1) Start the Office Setup wizard.
 * 2) In the wizard, click Add or Remove Features.
 * 3) Under Features to Install, expand Microsoft Excel for Windows, expand Spreadsheet Templates, and then click Sales Invoice. Click Run from My Computer and click Update.

To use the Web service from Excel, follow these steps:  Start Excel. A blank workbook is created by default.</li> On the Tools menu, click Macro and then click Visual Basic Editor. On the Insert menu, click Module to insert a blank code module.</li> On the Tools menu, click References.</li> In the References dialog box, Select Microsoft SOAP Type Library, and then click OK.

Note: If the Microsoft SOAP Type Library is not available in the References dialog box, then click Browse. In the Add Reference dialog box browse to the directory C:\Program Files\Common Files\MSSoap\Binaries and select mssoap1.dll. Click Open and then click OK.</li> <li> Paste the following code in the code module: Const sTemplatePath = &quot;C:\Microsoft Office\Templates\1033\Sales Invoice.xlt&quot; Public Const sServer = &quot;localhost&quot;

Sub GenerateForm(sID As String) Dim oSOAPClient As Object On Error GoTo errhand Set oSOAPClient = CreateObject(&quot;MSSOAP.SoapClient&quot;) oSOAPClient.mssoapinit &quot;http://&quot; + sServer + &quot;/SQLQuery/Service1.asmx?wsdl&quot;, &quot;Service1&quot;, &quot;Service1Soap&quot; Dim arrTemp As String arrTemp = oSOAPClient.QueryDatabase(sID) Dim oBook As Excel.Workbook Set oBook = Application.Workbooks.Add(sTemplatePath) With oBook.ActiveSheet If If CInt(Application.Version) = 10 Or CInt(Application.Version) = 11 Then .Range(&quot;D13&quot;).Value = arrTemp(2) + &quot; &quot; + arrTemp(1) .Range(&quot;D14&quot;).Value = arrTemp(4) .Range(&quot;D15&quot;).Value = arrTemp(5) .Range(&quot;F15&quot;).Value = arrTemp(6) .Range(&quot;H15&quot;).Value = arrTemp(7) .Range(&quot;D16&quot;).Value = arrTemp(3) .Range(&quot;M13&quot;).Value = CStr(Date) .Range(&quot;C19&quot;).Value = &quot;45.8&quot; .Range(&quot;D19&quot;).Value = &quot;Consulting hours&quot; .Range(&quot;L19&quot;).Value = &quot;75&quot; ElseIf CInt(Application.Version) = 9 Then .Range(&quot;data5&quot;).Value = arrTemp(2) + &quot; &quot; + arrTemp(1) .Range(&quot;data6&quot;).Value = arrTemp(4) .Range(&quot;data7&quot;).Value = arrTemp(5) .Range(&quot;data8&quot;).Value = arrTemp(6) .Range(&quot;data9&quot;).Value = arrTemp(7) .Range(&quot;data10&quot;).Value = arrTemp(3) .Range(&quot;data11&quot;).Value = &quot;45.8&quot; .Range(&quot;data12&quot;).Value = &quot;Consulting hours&quot; .Range(&quot;data13&quot;).Value = &quot;75&quot; End If   End With Exit Sub errhand: MsgBox &quot;Error #&quot; + Err.Number + &quot;: &quot; + Err.Description End Sub

Sub ShowForm ' Show the dialog box to the user. UserForm1.Show End Sub Note Modify the sServer constant to point to the server that is hosting the Web service that you just created. Modify the sTemplatePath constant to point to the correct path for the invoice file. For Office 2000, the default location for this file is C:\Microsoft Office\Templates\1033\Invoice.xlt. For Office XP, the default location is C:\Microsoft Office\Templates\1033\Sales Invoice.xlt.

</li> <li>On the Insert menu, click UserForm to insert a blank user form.</li> <li>Place a large list box and a command button on the form.</li> <li>On the View menu, click Code to change to the code window for the user form.</li> <li> Replace the contents of the code window with the following: Private Sub CommandButton1_Click ' Generate a FAX based on the ID. GenerateForm ListBox1.List(ListBox1.ListIndex) UserForm1.Hide End Sub

Private Sub UserForm_Initialize Dim oSOAPClient As Object ' Create a SOAP client. Set oSOAPClient = CreateObject(&quot;MSSOAP.SoapClient&quot;) ' Initialize the SOAP client to the Web service. oSOAPClient.mssoapinit &quot;http://&quot; + Module1.sServer + &quot;/SQLQuery/Service1.asmx?wsdl&quot;, &quot;Service1&quot;, &quot;Service1Soap&quot; Dim arrTemp As String ' Get an array of IDs. arrTemp = oSOAPClient.GetIDs

' Fill the list box with IDs. For i = 0 To UBound(arrTemp) ListBox1.AddItem arrTemp(i) Next End Sub </li> <li>Close the VBA Editor to return to the workbook.</li> <li>On the Tools menu, click Macro and then click Macros. In the Macros dialog box, run the ShowForm macro to display the list of IDs. Choose an ID from the list and click the command button to generate an invoice with that user's information.</li></ol>

<div class="references_section">