> Start you’re webservice
> Start Microsoft Office Excel
> Show the Control Toolbox toolbar: > View > Toolbars > Control Toolbox
> Enter Design mode by clicking the “Design Mode” button on the Control Toolbox
> Add a button to you’re Excel sheet
> Double click the button, this will op en de Microsoft Visual Basic Editor
> In the Microsoft Visual Basic Editor click: Tools > Web Service Reference… >
> On the Microsoft Office 2003 Web Services Toolkit dialog click: “Web Service URL” and enter you’re Web Service URL, click Search
> In the Search Results area, check you’re Web Service and click Add


This will generate a class “clsws_Service” with methods foreach WebMethod of the WebService, but the constant “Private c_WSDL_URL As String” should be changed to “Public URL As String”, so you can set the URL dynamic like:

    Dim service As New clsws_Service
    Dim data As String
    service.URL = ActiveSheet.Cells(1, "A").Value
    data = service.wsm_GetOpsMail(ActiveSheet.Cells(2, "A").Value, ActiveSheet.Cells(3, "A").Value)

More information can be found on: http://www.brainbell.com/tutorials/ms-office/excel/Access_SOAP_Web_Services_From_Excel.htm

One Reply to “Add a WebService reference to a Microsoft Office Excel sheet”

  1. In Excel 2010, I can’t see “Web Service Reference…”, is there a way to display it or is this option unavailable on this Excel version?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts