When you run a Microsoft Visual Basic for Applications (VBA) program that calls an XML Web service method, you may receive the following error message:
Run-time error '-2147221504 (80040000)':
Client:Unspecified client error.
An ArrayList object may contain elements of any data type. Therefore, when the Microsoft .NET Framework generates Web Services Description Language (WSDL) code that corresponds to an XML Web service method that returns an ArrayList object, the WSDL code does not specify the data type of the element that corresponds to the ArrayList object. When you try to call this XML Web service method, the Microsoft SOAP Toolkit cannot recognize the data type of the element that corresponds to the ArrayList object. Therefore, the SOAP Toolkit raises an error.
In the WSDL code that corresponds to the XML Web service method, specify the data type of the element that corresponds to the ArrayList object as anyType. To do this, follow these steps:
Locate the WSDL code that corresponds to your XML Web service, and then save this code as a WSDL contract file on your local computer.
In the WSDL contract file, locate the following WSDL code.
Create an XML Web service that contains an XML Web service method that returns an ArrayList object:
Start Microsoft Visual Studio .NET.
Use Microsoft Visual Basic .NET to create a Microsoft ASP.NET Web Service project. Name the project MyService. By default, the Service1.asmx file is created.
In the code view of the Service1.asmx file, locate the following code.
Add the following code before the code that you located in step c:
<WebMethod()> _Public Function GetArrayList() As ArrayList Dim MyArrayList As New ArrayList MyArrayList.Add("Blue") MyArrayList.Add("Green") MyArrayList.Add("Red") Return MyArrayListEnd Function
Build the solution to create the Service1 XML Web service.
Start Microsoft Excel.
On the Tools menu, point to Macro, and then click Visual Basic Editor. The VBA editor appears.
On the Tools menu, click Web Service References.
If you are using Office 2003, the Microsoft Office 2003 Web Services Toolkit dialog box appears.
If you are using Office XP or Office 2000, the Web Service References Tool 2.0 dialog box appears.
Click Web Service URL.
In the URL box, type the URL of the WSDL code that corresponds to the Service1 XML Web service. For example, if the URL of the Service1 XML Web service is http://MyServer/MyService/Service1.asmx, type http://MyServer/MyService/Service1.asmx?wsdl in the URL box.
Click Search. The Service1 XML Web service appears in the Search Results box.
In the Search Results box, click to select the Service1 check box, and then click Add. A class module that is named clsws_Service1 is added to your VBA project.
In Project Explorer, right-click VBAProject (Book1), point to Insert, and then click UserForm. The UserForm1 user form is added to the VBA project.
Add a command button to the UserForm1 user form. By default, the CommandButton1 command button is added to the UserForm1 user form.
Double-click CommandButton1 to open the code view of the UserForm1 user form.
Add the following code to the CommandButton1_Click event handler.
Dim objArrayList As VariantDim WebService As New clsws_Service1Dim Item As VariantobjArrayList = WebService.wsm_GetArrayListFor Each Item In objArrayListMsgBox ItemNext
On the Run menu, click Run Sub/UserForm. The UserForm1 user form appears.
The problem that is mentioned in the "Symptoms" section occurs.
For more information, visit the following Microsoft Developer Network (MSDN) Web sites: