Astuces Excel
  1. Astuces Excel
  3. Comment faire REST appel avec VBA dans Excel?
Intereting Posts
Copier quelques lignes sur de nouvelles colonnes – Excel Excel | Les lignes de sum avec différentes longueurs Puis-je utiliser pywin32 WithEvents et ne pas préconiser le process? Dans VLOOKUP, si je reçois une erreur, je souhaite effectuer une autre search en utilisant une valeur de search différente VBA Keydown Problems Comment publier et définir correctement une feuille de calcul? Comment ignorer une seule méthode dans Selenium download l'image dans le contrôle image actvex à partir du type binary dans vba excel Exporter / importer vers Excel avec la zone de list déroulante VBScript ne fonctionne pas? Configuration de la page incorrecte Accès de requête depuis Excel: parameters variables calculer le coût des heures travaillées Utilisation du programme ac # pour envoyer un paramètre à un file excel, de sorte qu'il utilise le paramètre pour l'envoyer à une procédure stockée VBA- "Erreur 1004: Impossible d'get la propriété CurrentPage de la class PivotField" Remplacer un hyperlink par une image sur Excel

Comment faire REST appel avec VBA dans Excel?

J'ai essayé de suivre cet exemple: http://libkod.info/officexml-CHP-9-SECT-5.shtml – Archive.org – Dons

mais cela a donné cette erreur

Imgur

sur cette ligne:

Dim obJHTTP comme nouveau MSXML2.XMLHTTP

J'ai essayé d'utiliser cet exemple: Comment puis-je envoyer une request POST HTTP à un server à partir d'Excel à l'aide de VBA?

mais cela a donné cette erreur:

Imgur

sur cette ligne: 

Print objHTTP.Status

Alors, comment faire un appel POST REST en VBA? Comment puis-je faire un file PUT multi-partie / file de données download REST appel dans VBA?

devises> Références

Imgur

Code 

 Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub Sub SendEmail () Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub 'Dim objHTTP comme nouveau MSXML2.XMLHTTP Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub 'Set objhttp = CreateObject ("WinHttp.WinHttpRequest.5.1") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub Définir objHTTP = CreateObject ("MSXML2.ServerXMLHTTP") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub URL = "http: // localhost: 8888 / rest / mail / send" Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub objHTTP.Open "POST", URL, Faux Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub objHTTP.send ("{" "key" ": null," "de" ":" "[email protected]" "," "à" ": null" "cc" ": null", "bcc" ": null", "date" ": null" "subject" ":" "My Subject" "," body "": null "," attachments "": null} ") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub Imprimer ObjHTTP.Status Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub Imprimer ObJHTTP.ResponseText Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objhttp = CreateObject("WinHttp.WinHttpRequest.5.1") Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Print objHTTP.Status Print objHTTP.ResponseText End Sub

Référence

Objet WinHttpRequest: http://msdn.microsoft.com/en-us/library/windows/desktop/aa384106(v=vs.85).aspx

Vous n'avez probablement pas ajouté une reference à Microsoft XML (toute version) pour Dim objHTTP As New MSXML2.XMLHTTP dans la boîte de dialog devises / Références … de la window VBA.

En outre, il est judicieux d'éviter d'utiliser la binding tardive ( CreateObject …); Il est préférable d'utiliser la binding anticipée ( Dim objHTTP As New MSXML2.XMLHTTP ), car une binding anticipée vous permet d'utiliser Intellisense pour répertorier les membres et effectuer toutes sortes de validation du time de design.

J'ai dû utiliser Debug.print au lieu de Print , qui fonctionne dans la window immédiate. 

 Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub Sub SendEmail () Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub 'Dim objHTTP comme nouveau MSXML2.XMLHTTP Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub 'Set objHTTP = Nouveau MSXML2.XMLHTTP60 Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub 'Dim obJHTTP comme nouveau MSXML2.XMLHTTP60 Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub Dim obJHTTP comme nouveau WinHttp.WinHttpRequest Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub 'Définir objHTTP = CreateObject ("WinHttp.WinHttpRequest.5.1") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub 'Définir objHTTP = CreateObject ("MSXML2.ServerXMLHTTP") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub URL = "http: // localhost: 8888 / rest / mail / send" Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub objHTTP.Open "POST", URL, Faux Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub objHTTP.setRequestHeader "Content-Type", "application / json" Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub objHTTP.send ("{" "key" ": null," "de" ":" "[email protected]" "," "à" ": null" "cc" ": null", "bcc" ": null", "date" ": null" "subject" ":" "My Subject" "," body "": null "," attachments "": null} ") Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub Debug.Print objHTTP.Status Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub Debug.Print objHTTP.ResponseText Sub SendEmail() 'Dim objHTTP As New MSXML2.XMLHTTP 'Set objHTTP = New MSXML2.XMLHTTP60 'Dim objHTTP As New MSXML2.XMLHTTP60 Dim objHTTP As New WinHttp.WinHttpRequest 'Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 'Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL = "http://localhost:8888/rest/mail/send" objHTTP.Open "POST", URL, False objHTTP.setRequestHeader "Content-Type", "application/json" objHTTP.send ("{""key"":null,""from"":""[email protected]"",""to"":null,""cc"":null,""bcc"":null,""date"":null,""subject"":""My Subject"",""body"":null,""attachments"":null}") Debug.Print objHTTP.Status Debug.Print objHTTP.ResponseText End Sub

Regardez celui-ci:

https://github.com/VBA-tools/VBA-Web

C'est une bibliothèque de haut niveau pour traiter le REST. C'est OOP, travaille avec JSON, mais fonctionne également avec n'importe quel autre format.