How To Implement a Web Service Call From Excel

In this article, we will explaining how to implement a web service call from Microsoft Excel. The prerequisite for this? We must have web service toolkit installed.

EXCEL and the Web Services Toolkit

First, the Microsoft Excel environment has to be prepared. For use with Office 2003, Microsoft delivers the Web Services Toolkit 2.01 for download at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=fa36018a-e1cf-48a3-9b35-169d819ecf18&DisplayLang=en

After the installation, the Web Services plug-in can be found in the Excel VB editor under Extras > Web Service References.
Excel is now ready to import WSDL files for the automatic creation of classes and data types to call the corresponding web services.

Creating the Web Service in SAP

The next step is to generate a web service from the SAP RFC function module and t expose it as WSDL file.
For example: convert RFC function module Z_ BAPI_XXXX_CREATEFROMDATA (any RFC enables function module) into a web service.

  • In SE37, go to Utilities->Create Web Service and follow the simple steps.

  • Go to transaction SOAMANGER and generate the WSDL.

Building the EXCEL Application

Now everything is prepared for the EXCEL application.

  • From the VB editor, choose "Web Service Reference" and mark the "Web Service URL" checkbox.

  • Type the full address of the WSDL file (e.g. C:\Z_XYZ.xml).
  • A search result should appear (as shown in below snapshot).
    If the WSDL file is correctly spelled and no search result appears, then it means WSDL file may be incorrect. One reason may be that in SAP, the input and output types are different (e.g. upper case for input fields, lower case for output fields).
  • Check the service and press the "add" button. The toolkit will parse the file and create type definitions and classes in the VB editor.

Wizard Results

After the upload of the WSDL file, the toolkit has added new classes of different types to the EXCEL project.
Some classes represent the table structures. In the example, the interface consists of the tables and other simple import structures leading to the structure classes (for the businesspartner tables e.g.:struc_BAPIBUS1037VBKAKOMCR).

Two other classes provide the objects for working with the web service. The method for calling the service in the example is in a class called

clsws_zcreatesalesactivity               

And here the method for calling the service is implemented:

Public Sub wsm_Z_XXXX_CREATEFROMDATA (ByRef ar_BUSINESSPARTNER As

Variant, ByRef ar_GENERALDATA As Variant, ByRef ar_RETURN As Variant,

ByVal obj_SENDER As struct_BAPISENDER, ByVal str_TESTRUN As String)

Note that all table parameters are of type "variant", simple structures are of respective type "struc_xxx" and simple variables are of type "string".
In case of individual other applications, the classes and type definitions need to be checked: All tables and structure parameters from the function interface should appear as class modules named struc_xxx, including the type definitions for all fields. If not, check for restricted words in the data definitions of the WSDL file.

These fields are converted by the Web Services Toolkit in Excel according to

Public FROM_DATE As date

To avoid deserialization failed errors in the Web Service call, all "date" fields must be redefined to "string".

Public FROM_DATE As String

Calling the Service

To call the web service, an object needs to be created:

Dim SalesActivityWS As New clsws_zCreateSalesActivityS

Then all input tables need to be filled. This can be achieved for all tables as follows. First declare the table variables as ?Variant?. For example, for the businesspartners:

Dim ar_BUSINESSPARTNER as variant

Additionally, a variable of type struc_BAPIBUS1037VBKAKOMCR is needed to fill the input data. To account for more than one entry, the variable is defined as array.

Dim _StrucBusinesspartner(2) as struc_BAPIBUS1037VBKAKOMCR

Now the structure _StrucBusinesspartner can be filled from a corresponding EXCEL table or by use of VB input forms according to the needs. Finally, the input variables are transferred:

ar_BUSINESSPARTNER = _StrucBusinesspartner

And the service is called.

Call SalesActivityWS. wsm_Z_XXXX_CREATEFROMDATA ( ar_BUSINESSPARTNER,

ar_GENERALDATA, ar_RETURN, obj_SENDER, str_TESTRUN)

After the execution of the service, all ar_RETURN fields can be evaluated.

Possible Errors

If the deserialization error occurs, check again for remaining fields of type date or lower case field names.

Another error might read "too many open connections". This means the service could not log on to SAP. Check the user name and password provided in transaction SICF. 

Author: Vivek Kumar

VIVEK06338

SAP ABAP Technical Consultant/Vistex Technical/CRM Technical Consultant More than 5 years of total IT industry experience encompassing skill set, roles and industry verticals. Experience involved with requirement designing in ERP domain as a technical consultant. Currently Working as ABAP team member. The functional module where provided technical solutions are Order to cash ( OTC/ Sales & Distribution), Procure to Plant ( PTP/Material Management and Production Planning ), Plant to Distribution (PTD/ Production Planning). Knowledge of ABAP Programming Techniques with different modules like Vistex and different functional module. Worked on Social CRM platform as CRM technical consultant and BRF+ Consultant for 1.5 years. Worked on DDIC, Report, Enhancement,screen design, Conversion, Function Module, Interface, Transaction Variant etc. Analyses of Functional Specification, Technical Specification and Unit Test Plan preparation. Experience of 1 implementation, 2 post implementation support, 1 Upgrade project and 2 continuous Enhancement Projects.

Module(s): ABAP, Basis, CRM