none
如何在 Excel VBA 中傳送 complexType 的資料給不同平台的 Web service RRS feed

  • 一般討論

  • 因為使用者想用 Excel 將輸入好的大量資料用 VBA 傳送給用 PHP 建置的 Web Service 來進行大量新增或更新資料
    其 WSDL 內容如下:

      <?xml version="1.0" encoding="ISO-8859-1" ?> 
    
    - <definitions xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://localhost/Item" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns="http://schemas.xmlsoap.org/wsdl/" targetNamespace="http://localhost/Item">
    
    - <types>
    
    - <xsd:schema targetNamespace="http://localhost/Item">
    
      <xsd:import namespace="http://schemas.xmlsoap.org/soap/encoding/" /> 
    
      <xsd:import namespace="http://schemas.xmlsoap.org/wsdl/" /> 
    
    - <xsd:complexType name="ItemData">
    
    - <xsd:all>
    
      <xsd:element name="itemId" type="xsd:string" /> 
    
      <xsd:element name="itemName" type="xsd:string" /> 
    
      <xsd:element name="itemPrice" type="xsd:int" /> 
    
      </xsd:all>
    
      </xsd:complexType>
    
      </xsd:schema>
    
      </types>
    
    - <message name="getItemRequest">
    
      <part name="item_id" type="xsd:string" /> 
    
      </message>
    
    - <message name="getItemResponse">
    
      <part name="return" type="tns:ItemData" /> 
    
      </message>
    
    - <message name="addItemRequest">
    
      <part name="item" type="tns:ItemData" /> 
    
      </message>
    
    - <message name="addItemResponse">
    
      <part name="return" type="xsd:string" /> 
    
      </message>
    
    - <portType name="ItemPortType">
    
    - <operation name="getItem">
    
      <input message="tns:getItemRequest" /> 
    
      <output message="tns:getItemResponse" /> 
    
      </operation>
    
    - <operation name="addItem">
    
      <documentation>add new item</documentation> 
    
      <input message="tns:addItemRequest" /> 
    
      <output message="tns:addItemResponse" /> 
    
      </operation>
    
      </portType>
    
    - <binding name="ItemBinding" type="tns:ItemPortType">
    
      <soap:binding style="rpc" transport="http://schemas.xmlsoap.org/soap/http" /> 
    
    - <operation name="getItem">
    
      <soap:operation soapAction="http://localhost/bs_quick_populate.php/getItem" style="rpc" /> 
    
    - <input>
    
      <soap:body use="encoded" namespace="http://localhost/Item" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
    
      </input>
    
    - <output>
    
      <soap:body use="encoded" namespace="http://localhost/Item" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
    
      </output>
    
      </operation>
    
    - <operation name="addItem">
    
      <soap:operation soapAction="http://localhost/bs_quick_populate.php/addItem" style="rpc" /> 
    
    - <input>
    
      <soap:body use="encoded" namespace="http://localhost/Item" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
    
      </input>
    
    - <output>
    
      <soap:body use="encoded" namespace="http://localhost/Item" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
    
      </output>
    
      </operation>
    
      </binding>
    
    - <service name="Item">
    
    - <port name="ItemPort" binding="tns:ItemBinding">
    
      <soap:address location="http://localhost/bs_quick_populate.php" /> 
    
      </port>
    
      </service>
    
      </definitions>
    
    


    目前測試程式如下:

    ' 物件類別模組 TItemDate
    
    Option Explicit
    
    Public itemId As String
    
    Public itemName As String
    
    Public itemPrice As Integer
    
    
    
    

    ' 模組 ModuleTest
    
    Option Explicit
    
    
    
    Sub addItem()
    
        Dim objItem As New TItemData
    
        
    
        Dim objSOAPClient As MSSOAPLib.SoapClient
    
        Dim wsReturn As String
    
        
    
        On Error GoTo Err
    
        Set objSOAPClient = New MSSOAPLib.SoapClient
    
        objSOAPClient.mssoapinit bstrWSDLFile:="http://localhost/bs_quick_populate.php?wsdl"<br/>
    
    <br/><br/>    objItem.itemId = "1"
    
        objItem.itemName = "Test"
    
        objItem.itemPrice = 100
    
        
    
        wsReturn = objSOAPClient.addItem(objItem)
    
        Sheet1.Cells(3, 4) = wsReturn
    
        
    
    TheEnd:
    
        Set objItem = Nothing
    
        Set objSOAPClient = Nothing
    
        Exit Sub    
    
    Err:
    
        With objSOAPClient
    
            MsgBox Prompt:="An error occured:" & vbCrLf & vbCrLf & _
    
                "Fault code: " & .faultcode & vbCrLf & _
    
                "Fault string: " & .faultstring & vbCrLf & _
    
                "Fault detail: " & .detail
    
        End With
    
        
    
        Resume TheEnd
    
    End Sub
    
    
    
    

    執行 addItem() 都是出現下列錯誤訊息

    Fault code: Client
    Fault string: Saveing SoapMapper ItemData failed
    Fault detail: Saveing SoapMapper ItemData failed HRESULT=0x80004002

    請問該如何處理呢?
    2009年5月5日 上午 07:15