How to invoke a SOAP Web Service from custom VBA Code

[Former Member]

Recently I have been involved with several integration projects between different EPM products. Initially it was quite challenging because as with any integration project the devil is always in the detail. The great thing with SAP EPM products is that there are web services for major processes and functions that need to be performed.

I needed to invoke a web service for an EPM product but had to do it in SAP BPC NW. Now there is several methods to invoke a web service from BPC NW, but it wouldnt be transportable to the MS product. So the solution was to invoke it from the client machine using VBA because on the client machine it can invoke any web service. Because SAP BPC uses Microsoft Office, you can create custom macros and VBA code to execute specific functions and functionality.

The answer to my particular challenge was to invoke a Web Service using custom VBA code embedded in an BPC Input Schedule and Report. Now there are several tutorials and links out on the internet which give you sample code to invoke a Web Service using VBA, but one of the major reasons for writing this blog is is to show how this approach can be used to solve several challenges and how powerful it can be.

Imagine some of the following scenarios:

• Invoking a FIM job from a click of a button on a report or input schedule.


• Invoking a BPC web service to perform a specific action


• Export data of BPC into another EPM application but simply invoking a web service to create a KPI in SSM

If you are able to get the WSDL of the Web Service, you can build your SOAP message and invoke the web service to perform the desired function. This VBA code is used to call a SOAP Web Service. It is possible to invoke a REST Web Service which BPC 10 uses, I will do this in another blog detailing a scenario in which this code could be used to solve certain business requirements.

VBA Code example : This is a simply Sub Routine in which will invoke a web service using the MSXML2 Object. This object is part of the Microsoft XML 6.0 library which is a pre-requisite for the EPM Add-In and should be installed on all client machines.

Sub InvokeWS()

 ' Code Snippets : Some of the source code listed below was taken from the following websites and credit show be given to the respective authors

 '#  http://www.vbaexpress.com/forum/showthread.php?t=34354

 '#  http://stackoverflow.com/questions/241725/calling-a-webservice-from-vba-using-soap

 '#  http://brettdotnet.posterous.com/excel-vba-using-a-web-service-with-xmlhttp-we

    'Declare our working variables

    Dim sMsg As String

    Dim sURL As String

    Dim sEnv As String

    'Set and Instantiate our working objects

    Set ObjHTTP = New MSXML2.XMLHTTP

    sURL = "http://ServerName:ServerPort/WebServiceName/WebServiceMethod"

    ' we create our SOAP envelope for submission to the Web Service

    'sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"

    'sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"

    'sEnv = sEnv & "  <soap:Body>"

    'sEnv = sEnv & "        <yq1:AllDBName xmlns:yq1=""http://sap.com/cpm/sm/webservices/ssm/""/> "

    'sEnv = sEnv & "  </soap:Body>"

    'sEnv = sEnv & "</soap:Envelope>"

    'we invoke the web service

    'use this code snippet to invoke a web service which requires authentication

    'ObjHTTP.Open "Post", sURL, False, "username", "password"

    'We use this code snippet to invoke a web service that doesn't require any user authentication

   ObjHTTP.Open "Post", sURL, False

    ObjHTTP.setRequestHeader "Content-Type", "text/xml"

    ObjHTTP.setRequestHeader "AllDBName ", "http://sap.com/cpm/sm/webservices/ssm/"

    ObjHTTP.send (sEnv)

    MsgBox ObjHTTP.responseText

    'clean up code

    Set ObjHTTP = Nothing

    Set xmlDoc = Nothing

End Sub 

Normally I am not a big fan of using custom VB code in BPC Input Schedules and Reports because of the complexity it can introduce and that in allot of occasions you can expect unexpected behavior on different clients. For example: It works on a Windows 7 machine, but doesn't work on Windows XP, etc. So a word of caution when using this approach is to understand the risks associated with running code on the client and its implications regarding security.

Hopefully this helps people out there solve certain challenges