What's causing this SoapMapper error in VBA Soap Service call?

1
votes

I'm trying to consume a Java based SOAP web service from VBA code in an Excel 2003 workbook. There are two methods available. One retrieves data, the other uploads data. The service for retrieving data works fine. However, when we try to upload data, we're running into a strange error that I just cannot find good information about on Google. The error message is:

Run-time error '-2147221504 (800040000)';
SoapMapper: Putting data into SoapMapper element failed

If we try to consume the same web service from a .NET application, it looks like the generated classes and methods are slightly different. For example, the fetch data service call takes a DataContainer object which has some identifying properties to determine what data to get. Then it returns the same type of object with the data filled in.

In the VBA classes generated by the Web Reference Toolkit, both fetch and save take the same type of object. But in .NET, the save takes a SaveDataContainerDetails object.

So a couple questions really:

  • Has anyone seen the SoapMapper error before in their VBA work?

  • Has anyone seen .NET and VBA generate different method signatures from the same WSDL? What could cause that and how could I work around it?

  • Is there a better way to call SOAP services from VBA rather than using the Web Reference Toolkit and the SOAP Toolkit?

2
soapexcelsoap-clientvba

2 Answers

1
votes

I've figured out the issue here. The VBA Soap Toolkit and Web References Toolkit just can't handle array's of complex types. The error message above was somewhat misleading because "element" was actually the name of the XML element in the Soap envelope. So it was failing to put data into an element called "element". That was masking the issue a bit.

So I've determined it all boiled down to arrays of complex types. I've also determined that it's MUCH easier to just craft the soap envelopes my self in the VBA. The XML required is generally pretty well structured and not too difficult to create and or parse. So, if you're having trouble with the Soap Toolkit or the Web References Toolkit in VBA... just don't use it! :)

0
votes

The best way to consume web services from VBA, VBSCRIPT, Classic ASP, etc. is to create your client using .NET. Then turn your client into a COM object. These are easily consumed by these older technologies.