Web Services from Excel

[Former Member]

Hi,

i want to use Excel (office 2003) with the web services toolkit from Microsoft (2.01) to call Web services from SAP (ABAP 7.0).

In the 6.40 version of ABAP i was able to call a web service also with some complex data types (internal tables) from excel. Finally the trick was to avoid empty input complex types.

Now trying this with the new version seems impossible. The system always respond "deserialisation failed". Tracking the problem shows a nice SOAP request in the SAP system, however, somehow the "deserialisation" error occurs here.

Is there any hint on how to deal with this kind of error ?

Thanks for any advice.

Marcus

[Former Member]

Hi,

I am new to VBA and excel.

I am trying to use wsdl file in Excel VBA. but i am getting error.

Some details are as follows.:-

We are using wsdl file and calling web service using EXCEL VBA, but while passing node ACCOUNTGL its giving error like u201CType conversion failure for element ACCOUNTGLu201D.

In wsdl file for ACCOUNTGL code is like

<xsd:element name="ACCOUNTGL" minOccurs="0">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="item" type="BAPIACGL09" minOccurs="0" maxOccurs="unbounded" xmlns:xsd="http://www.w3.org/2001/XMLSchema" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

Whereas in wsdl for BAPIACGL09 code is like this

<xsd:sequence>

<xsd:element name="ITEMNO_ACC" minOccurs="0">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="10" />

<xsd:pattern value="\d+" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="GL_ACCOUNT" minOccurs="0">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="10" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

. . . . .etc for all nodes.

when i checkd the node account GL it's like this

By VBA code I have created the ACCOUNTGL node like

<ACCOUNTGL>

<item>

<ITEMNO_ACC>1</ITEMNO_ACC>

<GL_ACCOUNT>0019990016</GL_ACCOUNT>

<ITEM_TEXT>test</ITEM_TEXT>

<DOC_TYPE>RJ</DOC_TYPE>

<COMP_CODE>283</COMP_CODE>

<FIS_PERIOD>2</FIS_PERIOD>

<FISC_YEAR>2009</FISC_YEAR>

<PSTNG_DATE>2008-08-21</PSTNG_DATE>

<COSTCENTER/>

<PROFIT_CTR>1DDEFAULT</PROFIT_CTR>

<WBS_ELEMENT/>

<ORDERID/>

</item>

<item>

<ITEMNO_ACC>2</ITEMNO_ACC>

<GL_ACCOUNT>0019990016</GL_ACCOUNT>

<ITEM_TEXT>test</ITEM_TEXT>

<DOC_TYPE>RJ</DOC_TYPE>

<COMP_CODE>283</COMP_CODE>

<FIS_PERIOD>2</FIS_PERIOD>

<FISC_YEAR>2009</FISC_YEAR>

<PSTNG_DATE>2008-08-21</PSTNG_DATE>

<COSTCENTER/>

<PROFIT_CTR>1DDEFAULT</PROFIT_CTR>

<WBS_ELEMENT/>

<ORDERID/>

</item>

</ACCOUNTGL>

But its giving error.

Hope i'll get some inputs.

[gregorw]

Hi Marcus,

can you post some example coding you have used? Then we can try to reproduce your problem.

Regards

Gregor

[marcus_schiffer]

Hi,

first here is the soap request from the SAP trace in SICF: ( a response is not visible due to the deserialisation error)

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>

- <SOAP-ENV:Envelope xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema" xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">

- <SOAP-ENV:Body>

- <SOAPSDK4:ZBpcontactCreatefromdata xmlns:SOAPSDK4="urn:sap-com:document:sap:soap:functions:mc-style">

- <SOAPSDK4:Businesspartner SOAPSDK3:arrayType="SOAPSDK4:VbkaVbpa2kom[3]" SOAPSDK3:offset="[0]" SOAPSDK2:type="SOAPSDK3:Array">

- <item>

<Refobjecttype SOAPSDK2:nil="true" />

<Refobjectkey SOAPSDK2:nil="true" />

<DocNumber>1</DocNumber>

<ItmNumber>1</ItmNumber>

<CountParvw>1</CountParvw>

<PartnRole>AG</PartnRole>

<PartnRoleOld>AG</PartnRoleOld>

<PartnId>0000002000</PartnId>

<PartnIdOld>0000002000</PartnIdOld>

<AddrNo SOAPSDK2:nil="true" />

<PersNo SOAPSDK2:nil="true" />

<Addrtype SOAPSDK2:nil="true" />

<AddrOrigin SOAPSDK2:nil="true" />

<UnloadPt SOAPSDK2:nil="true" />

<CalendarUpdate SOAPSDK2:nil="true" />

<AddrLink SOAPSDK2:nil="true" />

</item>

- <item>

<Refobjecttype SOAPSDK2:nil="true" />

<Refobjectkey SOAPSDK2:nil="true" />

<DocNumber>1</DocNumber>

<ItmNumber>1</ItmNumber>

<CountParvw>1</CountParvw>

<PartnRole>AP</PartnRole>

<PartnRoleOld>AP</PartnRoleOld>

<PartnId>0000000005</PartnId>

<PartnIdOld>0000000005</PartnIdOld>

<AddrNo SOAPSDK2:nil="true" />

<PersNo SOAPSDK2:nil="true" />

<Addrtype SOAPSDK2:nil="true" />

<AddrOrigin SOAPSDK2:nil="true" />

<UnloadPt SOAPSDK2:nil="true" />

<CalendarUpdate SOAPSDK2:nil="true" />

<AddrLink SOAPSDK2:nil="true" />

</item>

- <item>

<Refobjecttype SOAPSDK2:nil="true" />

<Refobjectkey SOAPSDK2:nil="true" />

<DocNumber>1</DocNumber>

<ItmNumber>1</ItmNumber>

<CountParvw>1</CountParvw>

<PartnRole>ZM</PartnRole>

<PartnRoleOld>ZM</PartnRoleOld>

<PartnId>0000001708</PartnId>

<PartnIdOld>0000001708</PartnIdOld>

<AddrNo SOAPSDK2:nil="true" />

<PersNo SOAPSDK2:nil="true" />

<Addrtype SOAPSDK2:nil="true" />

<AddrOrigin SOAPSDK2:nil="true" />

<UnloadPt SOAPSDK2:nil="true" />

<CalendarUpdate SOAPSDK2:nil="true" />

<AddrLink SOAPSDK2:nil="true" />

</item>

</SOAPSDK4:Businesspartner>

- <SOAPSDK4:Generaldata SOAPSDK3:arrayType="SOAPSDK4:Bus1037VbkakomCr[1]" SOAPSDK3:offset="[0]" SOAPSDK2:type="SOAPSDK3:Array">

- <item>

<Refobjecttype SOAPSDK2:nil="true" />

<Refobjectkey SOAPSDK2:nil="true" />

<DocNumber>1</DocNumber>

<Refdoctype SOAPSDK2:nil="true" />

<Refreltype SOAPSDK2:nil="true" />

<ActivityType>0001</ActivityType>

<Salesorg>1000</Salesorg>

<DistrChan>10</DistrChan>

<Division>00</Division>

<SalesOff>1000</SalesOff>

<SalesGrp>100</SalesGrp>

<FromDate>11/7/2006</FromDate>

<ToDate>11/7/2006</ToDate>

<FromTime>12:00:00 AM</FromTime>

<ToTime>12:00:00 AM</ToTime>

<Reason>003</Reason>

<Result>005</Result>

<ResultExplanation>005</ResultExplanation>

<State>1</State>

<FollowUpType SOAPSDK2:nil="true" />

<FollowUpDate>12:00:00 AM</FollowUpDate>

<ActivityComment>Das ist ein Test</ActivityComment>

<Descrpt01 SOAPSDK2:nil="true" />

<Descrpt02 SOAPSDK2:nil="true" />

<Descrpt03 SOAPSDK2:nil="true" />

<Descrpt04 SOAPSDK2:nil="true" />

<Descrpt05 SOAPSDK2:nil="true" />

<Descrpt06 SOAPSDK2:nil="true" />

<Descrpt07 SOAPSDK2:nil="true" />

<Descrpt08 SOAPSDK2:nil="true" />

<Descrpt09 SOAPSDK2:nil="true" />

<Descrpt10 SOAPSDK2:nil="true" />

<TxtKonse SOAPSDK2:nil="true" />

<Direction SOAPSDK2:nil="true" />

<PartnRole>AG</PartnRole>

<PartnId>0000002000</PartnId>

<ContactRole>AP</ContactRole>

<Contact>0000000005</Contact>

<Langu SOAPSDK2:nil="true" />

<LanguIso SOAPSDK2:nil="true" />

</item>

</SOAPSDK4:Generaldata>

<SOAPSDK4:Return SOAPSDK2:nil="true" SOAPSDK3:arrayType="SOAPSDK4:Bapiret2[0]" SOAPSDK3:offset="[0]" SOAPSDK2:type="SOAPSDK3:Array" />

- <SOAPSDK4:Sender>

<LogSystem SOAPSDK2:nil="true" />

</SOAPSDK4:Sender>

<SOAPSDK4:Testrun SOAPSDK2:nil="true" />

</SOAPSDK4:ZBpcontactCreatefromdata>

</SOAP-ENV:Body>

</SOAP-ENV:Envelope>

here is the VB coding in Excel:

The call of the WS is done in standard generated classes from the WS plugin in Excel:

Call contractWS.wsm_ZBpcontactCreatefromdata

Dim contractWS As New clsws_zcreatecontact2Servic

Dim ar_Businesspartner() As New struct_VbkaVbpa2kom

Dim ar_Generaldata(0) As New struct_Bus1037VbkakomCr

Dim ar_Return() As Variant

Dim obj_Sender As New struct_Sender

Dim str_Testrun As String

With ar_Generaldata(0)

.DocNumber = "1"

.ActivityType = "0001"

.Salesorg = "1000"

.DistrChan = "10"

.Division = "00"

.SalesOff = "1000"

.SalesGrp = "100"

.ActivityComment = Range("C9").Text

.FromDate = Format(Range("C8").Text, "yyyy-mm-dd")

.ToDate = Format(Range("C8").Text, "yyyy-mm-dd")

.FromTime = "00:00:00"

.ToTime = "00:00:00"

.Reason = "003"

.Result = "005"

.ResultExplanation = "005"

.State = "1"

.PartnRole = "AG"

.PartnId = "0000002000"

.ContactRole = "AP"

.Contact = "0000000005"

End With

ReDim ar_Businesspartner(2)

With ar_Businesspartner(0)

.DocNumber = "1"

.ItmNumber = "1"

.CountParvw = "1"

.PartnRole = "AG"

.PartnRoleOld = "AG"

.PartnId = "0000002000"

.PartnIdOld = "0000002000"

End With

With ar_Businesspartner(1)

.DocNumber = "1"

.ItmNumber = "1"

.CountParvw = "1"

.PartnRole = "AP"

.PartnRoleOld = "AP"

.PartnId = "0000000005"

.PartnIdOld = "0000000005"

End With

With ar_Businesspartner(2)

.DocNumber = "1"

.ItmNumber = "1"

.CountParvw = "1"

.PartnRole = "ZM"

.PartnRoleOld = "ZM"

Dim id As String

id = Range("C7").Value

.PartnId = id

.PartnIdOld = id

End With

Call contractWS.wsm_ZBpcontactCreatefromdata( _

ar_Businesspartner, _

ar_Generaldata, _

ar_Return, _

obj_Sender, _

str_Testrun _

)

Message was edited by: Marcus Schiffer

Message was edited by: Marcus Schiffer

[gregorw]

Hello Marcus,

can you provide us the Function Module Interface from which the Web Service was created?

Regards

Gregor

[marcus_schiffer]

Hi Gregor,

here is the FB interface to create a contact report in SAP.

I have debugged the WS call in ABAP and found that finally the transformation during the decoding of the soap body fails:

call transformation (template)

source xml l_xr

result (st_to_abap).

May the problem be related to the data representation in VB ?

With the last SAP release (6.20) the stuff worked after proper definition of the variables. Here it seems, the WSDL file from the WSADMIN transaction is different compared to the (6.20) version. E.g. the bapireturn2 structure is defined as variant.

Thanks for any further hint on the problem.

-

---

Function Interface -

---

FUNCTION Z_BPCONTACT_CREATEFROMDATA.

*"----

---

""Lokale Schnittstelle:

*" IMPORTING

*" VALUE(SENDER) TYPE BAPI_SENDER OPTIONAL

*" VALUE(TESTRUN) TYPE BAPI_VBKA_MISC-TESTRUN DEFAULT SPACE

*" TABLES

*" GENERALDATA STRUCTURE BAPI_BUS1037_VBKAKOM_CR

*" BUSINESSPARTNER STRUCTURE BAPI_VBKA_VBPA2KOM OPTIONAL

*" RETURN STRUCTURE BAPIRET2 OPTIONAL

[Former Member]

hi,

I'd have a look at capitalization. In most cases, when I have that deserialization problem and I have checked anything else to be correct, it's a capitalization thingy.

anton

[gregorw]

Hello Marcus,

the Web Service Enviroment has changed from Web AS 6.20 to Web AS 6.40 dramatically.

What I've found till now is that you could have the same problem as I had with optional Table Parameters. If they are only used to return Data you should uncheck "Exposed" in the virtual interface. I've found a problem to fill data into this tables if there where not filled from the Web Service call.

What I can't find in the VB Source you've provided is the Link to the Web Service WSDL. Can you send me a example Excel File?

Regards

Gregor

[Former Member]

hi all,

imho for the sake of clarity you should avodi tables parameters in WS function modules anyway. there is no equivalent thing in SOAP and it is very unusual in the WS world to send and echo unnecessary data.

just my 2 cents

[marcus_schiffer]

Hi,

thanks for the hints which helped to solve the issue.

Maybe this is all common knowledge, but I will nevertheless give a list of the steps I applied to get the Web Service working.

1. The "exposed" flag in the service description was unchecked and checked again in SAP. This led to a WSDL where all variables are in capital letters.

2. The BAPIRET2 structure contains a field named TYPE. This is a restricted word in VB for EXCEL. So the corresponding structure definition was never build from the Web Service Plug In in Excel. The structure was copied and TYPE was replaced with a ZTYPE. (The mapping feature in the interface description in ABAP should do this, but even after renaming the TYPE there to ZTYPE, the WSDL still contained TYPE)

3. Complex types are Variant in EXCEL and can be filled by variables of respective complex type

4. In the WSDL some fields were of type DATE and in the structure definition in EXCEL VB these were of corresponding type DATE. After replacing DATE by STRING for all these variables in VB EXCEL finally the Web service went through without error.

[gregorw]

Hello Marcus,

thank you for the detailed description of the Solution. I think that's worth a Article. You can get the Templates at https://www.sdn.sap.com/irj/sdn/submitcontent. When you publish it till November 30th you will get Double Points because SOA is <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/developerareas/esa">Topic of the Month November</a>.

Regards

Gregor

[Former Member]

Hello Marcus,

I was wondering if you could provide a simple example of how you used the VBA to connect to the Web Services. Also, where can I find this WS plugin you mentioned? I have been having a lot of trouble finding information or help on how to do this..I would really appreciate it.

email: gary_martins@raytheon.com

[Former Member]

Hi Marcus,

I'm working on running the webservices through Excel but webservice is not getting called it does not even show any error . In the forum you have mentioned some steps and you have solved it , can you be more elaborate on step 2, since I'm having the problem with the TYPE variable too . The WSDL file shows the input and output parametrs of the RFC function module (exposed as Webservice) uses TYPE parameter.

I have seen your (SAP Web Services and Microsoft Office) WEBLOG and you have mentioned on page 3 the following :

Before we proceed, it is necessary to mention that in Visual Basic, the word u201CTYPEu201D is restricted. So when generating a WSDL with restricted words in the type definitions, the Web Services Toolkit will not work properly. Unfortunately, the typical BAPI interface includes the BAPIRET2 structure with a field called TYPE. One way to overcome the restriction is to use the type mapping in the web service virtual interface as described in the previously mentioned tutorial. We choose as an alternative to use a copied version of the BAPIRET2 structure with a renamed TYPE field.

Can you please let me know which example you are refering too in this weblog ? please answer at your earliest.

Cheers,

Reddy.