Appel d'un wsdl depuis VBA Excel

**Fred85000** · 10/10/2017, 15h15

Bonjour,

J'ai fureté un peu partout et cherché un peu partout sur Google mais je ne trouve rien de bien concluant, d'où l'ouverture de cette discussion (ou demande d'aide pour être plus précis).

J'ai un PLM sous IIS en interne dans ma société. Ce progiciel expose des WS.

J'essaye d'appeler, depuis Excel, le WS permettant de se connecter au serveur (pour récupérer un identifiant de session).

Voici comment se traduit mon appel depuis SoapUI, appel qui fonctionne (en retournant un xml contenant mon id de session ) :

Code xml :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:mes="http://tempuri.org/message/">
   <soapenv:Header/>
   <soapenv:Body>
      <mes:Login soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <strSystemId>{39707381-6862-48C0-B0F5-582BFF2F197E}</strSystemId>
         <strResourceId xsi:type="xsd:string">{7700ED8C-CDC6-45A5-A8DA-247138ED66E8}</strResourceId>
         <strPassword xsi:type="xsd:string">toto</strPassword>
         <strProductId xsi:type="xsd:string">{09786DFC-EF02-46BF-AD2A-97C6EF6EA9BF}</strProductId>
         <allowSharedLogin xsi:type="xsd:int">0</allowSharedLogin>
      </mes:Login>
   </soapenv:Body>
</soapenv:Envelope>

L'url du WS est la suivante : http://serveur/advitium/WebServices/...ureServer.wsdl

Voici mon code VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
    su = "http://serveur/advitium/WebServices/LoginSecureServer.wsdl"
    Set oHt = New XMLHTTP
    sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
    sEnv = "<soapenv:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:mes=""http://tempuri.org/message/"">"
    sEnv = sEnv & "<soapenv:Header/>"
    sEnv = sEnv & "<soapenv:Body>"
    sEnv = sEnv & "<mes:Login soapenv:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/"">"
    sEnv = sEnv & "<strSystemId>{39707381-6862-48C0-B0F5-582BFF2F197E}</strSystemId>"
    sEnv = sEnv & "<strResourceId>{7700ED8C-CDC6-45A5-A8DA-247138ED66E8}</strResourceId>"
    sEnv = sEnv & "<strPassword>toto</strPassword>"
    sEnv = sEnv & "<strProductId>{09786DFC-EF02-46BF-AD2A-97C6EF6EA9BF}</strProductId>"
    sEnv = sEnv & "<allowSharedLogin>0</allowSharedLogin>"
    sEnv = sEnv & "</mes:Login>"
    sEnv = sEnv & "</soapenv:Body></soapenv:Envelope>"
 
    oHt.Open "POST", su, False
    oHt.setRequestHeader "Content-Type", "text/xml"
    oHt.setRequestHeader "SOAPAction", "Login"
    oHt.send sEnv
    xmlDoc.LoadXML (oHt.responseText)

A l'exécution, j'ai en retour une erreur qui est :
WSDLReader: The operation requested in the Soap message with soapAction Login isn't defined in the WSDL file. This may be because it is in the wrong namespace or has incorrect case.

J'ai tenté de désactiver l'action, mais le message est identique...

Voici une partie du wsdl :

Code xml :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
 
  <?xml version="1.0" encoding="UTF-8" ?> 
- <!--  Generated 08/25/06 by Microsoft SOAP Toolkit WSDL File Generator, Version 3.00.1325.0 
  --> 
- <definitions name="LoginSecureServer" targetNamespace="http://tempuri.org/wsdl/" xmlns:wsdlns="http://tempuri.org/wsdl/" xmlns:typens="http://tempuri.org/type/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:stk="http://schemas.microsoft.com/soap-toolkit/wsdl-extension" xmlns:dime="http://schemas.xmlsoap.org/ws/2002/04/dime/wsdl/" xmlns:ref="http://schemas.xmlsoap.org/ws/2002/04/reference/" xmlns:content="http://schemas.xmlsoap.org/ws/2002/04/content-type/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns="http://schemas.xmlsoap.org/wsdl/">
+ <types>
- <schema targetNamespace="http://tempuri.org/type/" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" elementFormDefault="qualified">
  <import namespace="http://schemas.xmlsoap.org/soap/encoding/" /> 
  <import namespace="http://schemas.xmlsoap.org/wsdl/" /> 
  <import namespace="http://schemas.xmlsoap.org/ws/2002/04/reference/" /> 
  <import namespace="http://schemas.xmlsoap.org/ws/2002/04/content-type/" /> 
  </schema>
  </types>
+ <message name="LoginSecureServer.Login">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strPassword" type="xsd:string" /> 
  <part name="strProductId" type="xsd:string" /> 
  <part name="allowSharedLogin" type="xsd:int" /> 
  </message>
+ <message name="LoginSecureServer.LoginResponse">
  <part name="Result" type="xsd:string" /> 
  </message>
+ <message name="LoginSecureServer.Logout">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  </message>
  <message name="LoginSecureServer.LogoutResponse" /> 
+ <message name="LoginSecureServer.IsLoggedIn">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  </message>
+ <message name="LoginSecureServer.IsLoggedInResponse">
  <part name="Result" type="xsd:int" /> 
  </message>
+ <message name="LoginSecureServer.GetLoginData">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  </message>
+ <message name="LoginSecureServer.GetLoginDataResponse">
  <part name="Result" type="xsd:string" /> 
  </message>
+ <message name="LoginSecureServer.LogoutAllMySessions">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  </message>
  <message name="LoginSecureServer.LogoutAllMySessionsResponse" /> 
+ <message name="LoginSecureServer.LogoutAllLostSessions">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  </message>
  <message name="LoginSecureServer.LogoutAllLostSessionsResponse" /> 
+ <message name="LoginSecureServer.IsLoggedInToProduct">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  <part name="strProductId" type="xsd:string" /> 
  </message>
+ <message name="LoginSecureServer.IsLoggedInToProductResponse">
  <part name="Result" type="xsd:int" /> 
  </message>
+ <message name="LoginSecureServer.LogoutAllMySessionsForProduct">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strSessionId" type="xsd:string" /> 
  <part name="strProductId" type="xsd:string" /> 
  </message>
  <message name="LoginSecureServer.LogoutAllMySessionsForProductResponse" /> 
+ <message name="LoginSecureServer.AutoLogin">
  <part name="strSystemId" type="xsd:string" /> 
  <part name="strResourceId" type="xsd:string" /> 
  <part name="strOriginatedSessionId" type="xsd:string" /> 
  <part name="strProductId" type="xsd:string" /> 
  <part name="allowSharedLogin" type="xsd:int" /> 
  </message>
+ <message name="LoginSecureServer.AutoLoginResponse">
  <part name="Result" type="xsd:string" /> 
  </message>
+ <portType name="LoginSecureServerSoapPort">
- <operation name="Login" parameterOrder="strSystemId strResourceId strPassword strProductId allowSharedLogin">
  <input message="wsdlns:LoginSecureServer.Login" /> 
  <output message="wsdlns:LoginSecureServer.LoginResponse" /> 
  </operation>
- <operation name="Logout" parameterOrder="strSystemId strResourceId strSessionId">
  <input message="wsdlns:LoginSecureServer.Logout" /> 
  <output message="wsdlns:LoginSecureServer.LogoutResponse" /> 
  </operation>
- <operation name="IsLoggedIn" parameterOrder="strSystemId strResourceId strSessionId">
  <input message="wsdlns:LoginSecureServer.IsLoggedIn" /> 
  <output message="wsdlns:LoginSecureServer.IsLoggedInResponse" /> 
  </operation>
- <operation name="GetLoginData" parameterOrder="strSystemId strResourceId strSessionId">
  <input message="wsdlns:LoginSecureServer.GetLoginData" /> 
  <output message="wsdlns:LoginSecureServer.GetLoginDataResponse" /> 
  </operation>
- <operation name="LogoutAllMySessions" parameterOrder="strSystemId strResourceId strSessionId">
  <input message="wsdlns:LoginSecureServer.LogoutAllMySessions" /> 
  <output message="wsdlns:LoginSecureServer.LogoutAllMySessionsResponse" /> 
  </operation>
- <operation name="LogoutAllLostSessions" parameterOrder="strSystemId strResourceId strSessionId">
  <input message="wsdlns:LoginSecureServer.LogoutAllLostSessions" /> 
  <output message="wsdlns:LoginSecureServer.LogoutAllLostSessionsResponse" /> 
  </operation>
- <operation name="IsLoggedInToProduct" parameterOrder="strSystemId strResourceId strSessionId strProductId">
  <input message="wsdlns:LoginSecureServer.IsLoggedInToProduct" /> 
  <output message="wsdlns:LoginSecureServer.IsLoggedInToProductResponse" /> 
  </operation>
- <operation name="LogoutAllMySessionsForProduct" parameterOrder="strSystemId strResourceId strSessionId strProductId">
  <input message="wsdlns:LoginSecureServer.LogoutAllMySessionsForProduct" /> 
  <output message="wsdlns:LoginSecureServer.LogoutAllMySessionsForProductResponse" /> 
  </operation>
- <operation name="AutoLogin" parameterOrder="strSystemId strResourceId strOriginatedSessionId strProductId allowSharedLogin">
  <input message="wsdlns:LoginSecureServer.AutoLogin" /> 
  <output message="wsdlns:LoginSecureServer.AutoLoginResponse" /> 
  </operation>
  </portType>
+ <binding name="LoginSecureServerSoapBinding" type="wsdlns:LoginSecureServerSoapPort">
  <stk:binding preferredEncoding="UTF-8" /> 
  <soap:binding style="rpc" transport="http://schemas.xmlsoap.org/soap/http" /> 
- <operation name="Login">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.Login" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strPassword strProductId allowSharedLogin" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="Result" /> 
  </output>
  </operation>
- <operation name="Logout">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.Logout" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </output>
  </operation>
- <operation name="IsLoggedIn">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.IsLoggedIn" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="Result" /> 
  </output>
  </operation>
- <operation name="GetLoginData">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.GetLoginData" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="Result" /> 
  </output>
  </operation>
- <operation name="LogoutAllMySessions">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.LogoutAllMySessions" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </output>
  </operation>
- <operation name="LogoutAllLostSessions">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.LogoutAllLostSessions" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </output>
  </operation>
- <operation name="IsLoggedInToProduct">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.IsLoggedInToProduct" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId strProductId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="Result" /> 
  </output>
  </operation>
- <operation name="LogoutAllMySessionsForProduct">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.LogoutAllMySessionsForProduct" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strSessionId strProductId" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </output>
  </operation>
- <operation name="AutoLogin">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.AutoLogin" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" /> 
  </output>
  </operation>
  </binding>
- <service name="LoginSecureServer">
- <port name="LoginSecureServerSoapPort" binding="wsdlns:LoginSecureServerSoapBinding">
  <soap:address location="http://localhost/Advitium/Services/LoginSecureServer.WSDL" /> 
  </port>
  </service>
  </definitions>

Comment cela peut fonctionner sans souci depui SoapUI et que depuis l'objet HTTP cela ne fonctionne pas...????

D'avance, je vous remercie pour votre temps et vos réponses.

**Fred85000** · 10/10/2017, 15h36

Bon alors, tout d'abord mes excuses...

Tout est dans le WSDL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
- <binding name="LoginSecureServerSoapBinding" type="wsdlns:LoginSecureServerSoapPort">
  <stk:binding preferredEncoding="UTF-8" /> 
  <soap:binding style="rpc" transport="http://schemas.xmlsoap.org/soap/http" /> 
- <operation name="Login">
  <soap:operation soapAction="http://tempuri.org/action/LoginSecureServer.Login" /> 
- <input>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="strSystemId strResourceId strPassword strProductId allowSharedLogin" /> 
  </input>
- <output>
  <soap:body use="encoded" namespace="http://tempuri.org/message/" encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" parts="Result" /> 
  </output>
  </operation>

En ajoutant donc dans le header en vba :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
oHt.setRequestHeader "SOAPAction", "http://tempuri.org/action/LoginSecureServer.Login"

cela fonctionne...

Retour

Code xml :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
<?xml version="1.0" standalone="no"?>
<SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><SOAP-ENV:Body><SOAPSDK1:LoginResponse xmlns:SOAPSDK1="http://tempuri.org/message/"><Result>{8C5BA9C9-C272-492C-9B21-A8EA8BE9CEAD}</Result></SOAPSDK1:LoginResponse></SOAP-ENV:Body></SOAP-ENV:Envelope>

Je suis donc une Quiche... et j'ai des lacunes en appel WS VBA...

Bonne journée ! Je marque en résolue. Si cela peut aider d'autres personnes...

Appel d'un wsdl depuis VBA Excel [XL-2010]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager