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.