Appel web service par VBA

**Reseauteur** · 05/09/2017, 14h56

Bonjour,

Dans un contexte industriel, j'ai:

Une base informix depuis laquelle j'arrive à extraire des données via un complément excel
Une base Firebird à laquelle je n'ai pas accès mais qui dispose de web services (jamais entendu parler auparavant...)

Je souhaiterais extraire des données de cette dernière et faire du reporting en utilisant les deux ensembles de données mais je ne trouve que très peu de documentation, ou alors trop complexe pour moi sur la partie web service

Sous Excel j'ai activé les librairies suivantes : MS HTML object library, MS Soap type library x3.0, MS XML v6.0

Plusieurs web services sont disponibles, et ressemblent à ceci :

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
<wsdl:definitions name="EmployeeAccessDataService" targetNamespace="http://echange.service.open.bodet.com"><wsdl:types><xsd:schema attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://lang.java"><xsd:complexType name="Exception"><xsd:complexContent><xsd:extension base="tns:Throwable"><xsd:sequence/></xsd:extension></xsd:complexContent></xsd:complexType><xsd:complexType name="Throwable"><xsd:sequence/></xsd:complexType></xsd:schema><xsd:schema attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://echange.service.open.bodet.com"><xsd:complexType name="AskedPopulation"><xsd:complexContent><xsd:extension base="tns:EmployeeInformation"><xsd:sequence><xsd:element minOccurs="0" name="populationStartDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="populationEndDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="groupFilter" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="populationFilter" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="populationMode" nillable="true" type="xsd:int"/></xsd:sequence></xsd:extension></xsd:complexContent></xsd:complexType><xsd:complexType abstract="true" name="Object"><xsd:sequence><xsd:element minOccurs="0" name="technicalString" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="errorMessage" nillable="true" type="xsd:string"/></xsd:sequence></xsd:complexType><xsd:complexType name="EmployeeInformation"><xsd:complexContent><xsd:extension base="tns:Object"><xsd:sequence><xsd:element minOccurs="0" name="archivedEmployee" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="employeeKey" nillable="true" type="xsd:int"/><xsd:element minOccurs="0" name="employeeBadgeCode" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="employeeIdentificationCode" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="employeeIdentificationNumber" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="employeeSurname" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="employeeFirstName" nillable="true" type="xsd:string"/></xsd:sequence></xsd:extension></xsd:complexContent></xsd:complexType><xsd:complexType name="ArrayOfEmployeeAccessData"><xsd:sequence><xsd:element maxOccurs="unbounded" minOccurs="0" name="EmployeeAccessData" nillable="true" type="tns:EmployeeAccessData"/></xsd:sequence></xsd:complexType><xsd:complexType abstract="true" name="EmployeeInformationAndCriterion"><xsd:complexContent><xsd:extension base="tns:EmployeeInformation"><xsd:sequence><xsd:element minOccurs="0" name="defaultEmployeeBadge" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="defaultEmployeeIdentificationCode" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="currentAccessAuthorizationStartDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="takenIntoAccountPeriodStartDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="currentAccessAuthorizationEndDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="takenIntoAccountPeriodEndDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="generateBadge" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="currentAccessAuthorizationStartTime" nillable="true" type="xsd:time"/><xsd:element minOccurs="0" name="currentAccessAuthorizationEndTime" nillable="true" type="xsd:time"/><xsd:element minOccurs="0" name="defaultEmployeeIdentificationNumber" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="defaultEmployeeSurname" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="defaultEmployeeFirstName" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="searchUsingBadge" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="searchUsingIdentificationNumber" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="searchUsingSurname" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="searchUsingFirstname" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="isAccessModuleEmployee" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="isTandAModuleEmployee" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="useDefaultModelEmployee" nillable="true" type="xsd:boolean"/></xsd:sequence></xsd:extension></xsd:complexContent></xsd:complexType><xsd:complexType name="ArrayOfAskedPopulation"><xsd:sequence><xsd:element maxOccurs="unbounded" minOccurs="0" name="AskedPopulation" nillable="true" type="tns:AskedPopulation"/></xsd:sequence></xsd:complexType><xsd:complexType name="EmployeeAccessData"><xsd:complexContent><xsd:extension base="tns:EmployeeInformationAndCriterion"><xsd:sequence><xsd:element minOccurs="0" name="currentSectionAbbreviation" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="visitorUsedBadge" nillable="true" type="xsd:int"/><xsd:element minOccurs="0" name="personalCode" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="concernedByAntiPassbacks" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="currentPublicHolidayCalendarApplicationDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="currentAccessProfileAssigningDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="currentSectionAssigningDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="currentAccessProfileAssigningEndDate" nillable="true" type="xsd:date"/><xsd:element minOccurs="0" name="centralAlarmSystemManualActivationAllowance" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="specificSchedulesAllowance" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="specificSchedulesEverywhereAllowance" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="currentPublicHolidayCalendarDescription" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="currentSectionFullDescription" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="currentAccessProfileDescription" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="visitorSectionDescription" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="siteDescriptionOfCurrentAccessProfile" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="receiveVisitor" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="superPass" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="superPassEverywhere" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="extendedTimeout" nillable="true" type="xsd:boolean"/><xsd:element minOccurs="0" name="visitorOfOtherSiteAllowance" nillable="true" type="xsd:boolean"/></xsd:sequence></xsd:extension></xsd:complexContent></xsd:complexType><xsd:element name="exportEmployeeAccessDataList" type="tns:exportEmployeeAccessDataList"/><xsd:complexType name="exportEmployeeAccessDataList"><xsd:sequence><xsd:element minOccurs="0" name="populationFilter" type="tns:ArrayOfAskedPopulation"/></xsd:sequence></xsd:complexType><xsd:element name="exportEmployeeAccessDataListResponse" type="tns:exportEmployeeAccessDataListResponse"/><xsd:complexType name="exportEmployeeAccessDataListResponse"><xsd:sequence><xsd:element minOccurs="0" name="exportedEmployeeAccessData" type="tns:ArrayOfEmployeeAccessData"/></xsd:sequence></xsd:complexType><xsd:element name="importEmployeeAccessData" type="tns:importEmployeeAccessData"/><xsd:complexType name="importEmployeeAccessData"><xsd:sequence><xsd:element minOccurs="0" name="employeeAccessToImport" type="tns:ArrayOfEmployeeAccessData"/></xsd:sequence></xsd:complexType><xsd:element name="importEmployeeAccessDataResponse" type="tns:importEmployeeAccessDataResponse"/><xsd:complexType name="importEmployeeAccessDataResponse"><xsd:sequence><xsd:element minOccurs="0" name="employeeAccessDataInError" type="tns:ArrayOfEmployeeAccessData"/></xsd:sequence></xsd:complexType><xsd:element name="exportEmployeeAccessData" type="tns:exportEmployeeAccessData"/><xsd:complexType name="exportEmployeeAccessData"><xsd:sequence><xsd:element minOccurs="0" name="populationFilter" nillable="true" type="xsd:string"/><xsd:element minOccurs="0" name="groupFilter" nillable="true" type="xsd:string"/></xsd:sequence></xsd:complexType><xsd:element name="exportEmployeeAccessDataResponse" type="tns:exportEmployeeAccessDataResponse"/><xsd:complexType name="exportEmployeeAccessDataResponse"><xsd:sequence><xsd:element minOccurs="0" name="exportedEmployeeAccessData" type="tns:ArrayOfEmployeeAccessData"/></xsd:sequence></xsd:complexType><xsd:element name="Exception" nillable="true" type="ns0:Exception"/></xsd:schema></wsdl:types><wsdl:message name="exportEmployeeAccessDataListResponse"><wsdl:part element="tns:exportEmployeeAccessDataListResponse" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="exportEmployeeAccessDataList"><wsdl:part element="tns:exportEmployeeAccessDataList" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="importEmployeeAccessData"><wsdl:part element="tns:importEmployeeAccessData" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="importEmployeeAccessDataResponse"><wsdl:part element="tns:importEmployeeAccessDataResponse" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="exportEmployeeAccessData"><wsdl:part element="tns:exportEmployeeAccessData" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="exportEmployeeAccessDataResponse"><wsdl:part element="tns:exportEmployeeAccessDataResponse" name="parameters">
    </wsdl:part></wsdl:message><wsdl:message name="Exception"><wsdl:part element="tns:Exception" name="Exception">
    </wsdl:part></wsdl:message><wsdl:portType name="EmployeeAccessDataServicePortType"><wsdl:operation name="exportEmployeeAccessDataList"><wsdl:input message="tns:exportEmployeeAccessDataList" name="exportEmployeeAccessDataList">
    </wsdl:input><wsdl:output message="tns:exportEmployeeAccessDataListResponse" name="exportEmployeeAccessDataListResponse">
    </wsdl:output><wsdl:fault message="tns:Exception" name="Exception">
    </wsdl:fault></wsdl:operation><wsdl:operation name="importEmployeeAccessData"><wsdl:input message="tns:importEmployeeAccessData" name="importEmployeeAccessData">
    </wsdl:input><wsdl:output message="tns:importEmployeeAccessDataResponse" name="importEmployeeAccessDataResponse">
    </wsdl:output><wsdl:fault message="tns:Exception" name="Exception">
    </wsdl:fault></wsdl:operation><wsdl:operation name="exportEmployeeAccessData"><wsdl:input message="tns:exportEmployeeAccessData" name="exportEmployeeAccessData">
    </wsdl:input><wsdl:output message="tns:exportEmployeeAccessDataResponse" name="exportEmployeeAccessDataResponse">
    </wsdl:output><wsdl:fault message="tns:Exception" name="Exception">
    </wsdl:fault></wsdl:operation></wsdl:portType><wsdl:binding name="EmployeeAccessDataServiceSoapBinding" type="tns:EmployeeAccessDataServicePortType"><soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/><wsdl:operation name="exportEmployeeAccessDataList"><soap:operation soapAction="urn:exportEmployeeAccessDataList" style="document"/><wsdl:input name="exportEmployeeAccessDataList"><soap:body use="literal"/></wsdl:input><wsdl:output name="exportEmployeeAccessDataListResponse"><soap:body use="literal"/></wsdl:output><wsdl:fault name="Exception"><soap:fault name="Exception" use="literal"/></wsdl:fault></wsdl:operation><wsdl:operation name="importEmployeeAccessData"><soap:operation soapAction="urn:importEmployeeAccessData" style="document"/><wsdl:input name="importEmployeeAccessData"><soap:body use="literal"/></wsdl:input><wsdl:output name="importEmployeeAccessDataResponse"><soap:body use="literal"/></wsdl:output><wsdl:fault name="Exception"><soap:fault name="Exception" use="literal"/></wsdl:fault></wsdl:operation><wsdl:operation name="exportEmployeeAccessData"><soap:operation soapAction="urn:exportEmployeeAccessData" style="document"/><wsdl:input name="exportEmployeeAccessData"><soap:body use="literal"/></wsdl:input><wsdl:output name="exportEmployeeAccessDataResponse"><soap:body use="literal"/></wsdl:output><wsdl:fault name="Exception"><soap:fault name="Exception" use="literal"/></wsdl:fault></wsdl:operation></wsdl:binding><wsdl:service name="EmployeeAccessDataService"></wsdl:service></wsdl:definitions>

Pour l'instant j'ai essayé :

Code :

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

1
2
3
4
5
6
Dim req As New XMLHTTP
 
req.Open "GET", "http://192.168.1.106:8089/open/services/EmployeeAccessDataService?wsdl"
req.send
 
MsgBox req.responseText

Ce qui me renvoie :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
<?xml version='1.0' encoding='UTF-8'?><wsdl:definitions xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://echange.service.open.bodet.com" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:ns1="http://schemas.xmlsoap.org/soap/http" name="EmployeeAccessDataService" targetNamespace="http://echange.service.open.bodet.com">
  <wsdl:types>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://lang.java" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://lang.java">
  <xsd:complexType name="Exception">
    <xsd:complexContent>
      <xsd:extension base="tns:Throwable">
        <xsd:sequence/>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType name="Throwable">
    <xsd:sequence/>
  </xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://echange.service.open.bodet.com" xmlns:ns0="http://lang.java" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://echange.service.open.bodet.com">
  <xsd:complexType name="AskedPopulation">
    <xsd:complexContent>
      <xsd:extension base="tns:EmployeeInformation">
        <xsd:sequence>
          <xsd:element minOccurs="0" name="populationStartDate" nillable="true" type="xsd:date"/>
          <xsd:element minOccurs="0" name="populationEndDate" nillable="true" type="xsd:date"/>
          <xsd:element minOccurs="0" name="groupFilter" nillable="true" type="xsd:string"/>
          <xsd:element minOccurs="0" name="populationFilter" nillable="true" type="xsd:string"/>
          <xsd:element minOccurs="0" name="populationMode" nillable="true" type="xsd:int"/>
        </xsd:sequence>
      </xsd:extension>
    </xsd:complexContent>
  </xsd:complexType>
  <xsd:complexType abstract="true" name="Object">
    <xsd:sequence>
      <xsd:element minOccurs="0" name="technicalString" nillable="true" type="xsd:string"/>
      <xsd:element minOccurs="0" name="errorMessage" nillable="true" type="xsd:string"/>
    </xsd:sequence>
  </xsd:complexType>

Il y a donc une communication mais je ne sais pas comment récupérer la moindre donnée, est-ce que quelqu'un pourrait m'orienter un peu svp sur ce cas concret ?

Je dispose également de fichiers html qui semblent expliquer le fonctionnement de chaque service. Pour celui cité plus haut, j'ai par exemple un fichier exportEmployeeAccessDataList.html et un fichier importEmployeeAccessData.html qui contiennent deux tableaux "paramètres entrées" et "paramètres sorties" avec à chaque fois un code + description + type de classe
Exemple :
exportEmployeeJobDataList
export filter
AskedPopulation
archivedEmployee (boolean)
employeeBadgeCode (string)
employeeFirstName (string)
...

**PipoWIL** · 28/09/2017, 12h41

Oui c'est possible de consommer un wervice web avec du VBA.
Pour un web service SOAP, tu dois envoyer une enveloppe XML et le web service te répondra avec une réponse au format XML.
Tu peux imaginer qqch du genre de ceci :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Function Web_Svc()
 
Dim strResponseText As String
Dim strURL As String
Dim strEnv As String
Dim xmlhtp As MSXML2.XMLHTTP60
Dim lgFileNum As Long
Dim xmlDoc As DOMDocument60
 
 
' we create our SOAP envelope for submission to the Web Service
strEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
'je reprends tous les elements du début(definition) du wsdl :
strEnv = strEnv & "<soapenv:Envelope xmlns=""http://ww..."
strEnv = strEnv & "xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" "
strEnv = strEnv & "xmlns:wsdlsoap=""http://schemas.xmlsoap.org/wsdl/soap/"" "
strEnv = strEnv & "xmlns:wsdl=""http://schemas.xmlsoap.org/wsdl/""  "
strEnv = strEnv & "xmlns:....."
 
strEnv = strEnv & "  <soapenv:Header/>"
strEnv = strEnv & "  <soapenv:Body>"
 
strEnv = strEnv & "       <MyData1>123456</MyData1>"
strEnv = strEnv & "       <MyData2>blah blah</MyData2>"
 
strEnv = strEnv & "  </soapenv:Body>"
strEnv = strEnv & "</soapenv:Envelope>"
 
strURL = "https://www...."
 
 
Set xmlhtp = New MSXML2.ServerXMLHTTP60
 
xmlhtp.Open "POST", strURL, False         'POST, OPEN, GET : Always use uppercase as some servers ignore lowercase HTTP verbs
 
xmlhtp.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
 
xmlhtp.send strEnv
strResponseText = xmlhtp.responseText
 
'la réponse sera du type XML
Debug.Print strResponseText
 
End Function

**Reseauteur** · 28/09/2017, 15h42

Bonjour,

Merci pour la réponse, j'avais regardé pas mal d'exemples de la sorte mais laissé tomber le VBA car je me suis rendu compte qu'il fallait s'authentifier et je n'ai pas compris le fonctionnement... S'il faut s'authentifier auprès d'un webservice particulier ou du webservice qu'on utilise en rajoutant un bout de code (user/password) s'il faut inclure dans l'enveloppe, l'entête ou pas....
Un conseil peut être ?

J'ai réussi à faire ce que je voulais avec SoapUI + script mais le VBA me faciliterait le travail !

Cordialement

**PipoWIL** · 28/09/2017, 16h48

Bonjour,
Je ne connais pas SoapUI. C'est quoi ?

J'ai vu des exemples de webservice SOAP qui utilisaient exactement la même méthode que celle que je te donne et dans l'enveloppe, il y avait un champ UserID et un champ Password. C'est à dire que le userid et le password sont envoyé en clair parmi les autres données. Je ne suis pas sûr que ce soit la meilleure sécurité.

Si tu trouves un moyen d'utiliser le web service avec securité (certificat / userID), fais-nous signe. Pour le moment, je sèche sur un problème similaire.

**Reseauteur** · 28/09/2017, 18h33

j'essayerai en vba l'authentification mais ça me semble complexe. Pour la sécurité je n'ai pas la main sur les webservices et je ne suis pas assez qualifié je pense...
Soapui cest un outil qui permet de tester des webservices, login mdp sont saisis dans les paramètres donc facile sur ce point, il suffit d'entrer l'url http://ip:port/service?wsdl et il s'occupe de chercher les "fonctions" du webservice, il prépare l'enveloppe et affiche le résultat, il y a les "testcase" ou dans mon cas jai deux appels de webservices qui enchaînent sur un script en groovy (genre de Java) qui me permet de récupérer les données dans un fichier texte, bref ça fait le taff ..!

Appel web service par VBA

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager