VBA: Consume Soap Web Services

November 14th, 2004 | Categories: VBA | Tags:

Are you looking for a way to integrate a web service with VBA? Download the unsupported Microsoft Office XP Web Services Toolkit 2.0 or Microsoft Office 2003 Web Services Toolkit 2.01

After doing so myself, I went browsing this Xmethods list for a public web service and found the Random Bushism Web Service. Now my goal was to create a Macro that would display a message box with a random “Bushism” in it, using the Web Services Toolkit.


Some notes if you would like to try it yourself…

1. If you haven’t done so already, install the Web Services Toolkit.

2. Open Excel.

3. Open the Visual Basic Editor(Alt + F11).

4. Click the new option on your Tools menu – “Web Service References…”


5. In your newly opened Toolkit Form click the “Web Service URL” radio button and enter this address: http://greg.froh.ca/fun/random-bushism/soap/?wsdl


6. Click the search button.

7. Hopefully it has found the Bushism web service and listed it in the “Search Results” box. Place a checkmark to select the web service and click the “Add Button”.


8. Insert a module. Your Editor should now look something like this:


9. Add this code to your module, sprinkle with error handling if desired, run or call the GetRandomBushism Macro:

Sub GetRandomBushism()

    Dim BushRandom As New clsws_RandomBushismService
    Dim BushStruct As struct_RandomBushism
    Set BushStruct = BushRandom.wsm_getRandomBushism

    MsgBox BushStruct.bushism & vbCrLf & _
    vbCrLf & BushStruct.context, , "Bushism"
End Sub
  1. June 11th, 2005 at 02:28
    Reply | Quote | #1

    Hi Mark,
    Had a play around with the xmethods service, but it’s not going to be powerful enough for what i had in mind.

    Very easy to get working though, so i’ll keep SOAP in mind :)

    cheers mate

  2. Dharnendra
    February 19th, 2010 at 09:37
    Reply | Quote | #2

    Hi Mark,

    If i wann to provide special credentials to call the web service, how should i do ?

    • Thierry
      April 15th, 2010 at 21:39
      Reply | Quote | #3

      I’ve got the example working, but I need to know how to pass credentials (username & password)…not much info on the web about this. Does anyone know how to do this? Thanks!

  3. Vergie
    November 26th, 2010 at 08:25
    Reply | Quote | #4

    Hi, do you have example about passing complex type variable? im having a hard time on that.

    Thank you

  4. Vergie
    November 26th, 2010 at 09:56
    Reply | Quote | #5

    Hi, do you know how to use or pass a complex type variable?

    I have this code:
    Public Function wsm_GPDB_ProductGetDetails_MI(ByVal any_GPDB_ProductGetDetails As MSXML2.IXMLDOMNodeList) As MSXML2.IXMLDOMNodeList
    ‘Proxy function created from http://gdd.na.pg.com:50100/WSDL/GPDB_ProductGetDetails.wsdl.

    ‘”wsm_GPDB_ProductGetDetails_MI, any_GPDB_ProductGetDetails” is defined as XML. See Complex Types: XML Variables in
    ‘Web Service References Tool 2.0 Help for details on implementing XML variables.

    ‘Error Trap
    On Error GoTo wsm_GPDB_ProductGetDetails_MITrap

    Set wsm_GPDB_ProductGetDetails_MI = sc_GPDBProductGetDetails.GPDB_ProductGetDetails_MI(any_GPDB_ProductGetDetails)

    Exit Function
    GPDBProductGetDetailsErrorHandler “wsm_GPDB_ProductGetDetails_MI”
    End Function

    I dont know what to pass to the variable that is nodelist.
    maybe you could give me a hint or a useful reference.. thanks!

  5. Parth
    January 1st, 2011 at 05:23
    Reply | Quote | #6

    I am using the same web service toolkit, and while using that i am getting an error “Maximum retry on the connection exceeded”
    Some people say this is an authentication related Issue.

    Please help me out with this.

1 trackbacks

  1. Google API Code for Ross Pingback | 2008/08/17