Getting method not valid without suitable object error when trying to make a HTTP request in VBA

Question

I tried to follow this example  http   libkod info officexml-CHP-9-SECT-5 shtml - Archive org - Donate but it gave this error  on this line  Dim objHTTP As New MSXML2 XMLHTTP  I tried to use this example  How can I send an HTTP POST request to a server from Excel using VBA  but it gave this error   on this line  Print objHTTP Status  So how do I make a POST REST call in VBA  How do I make a PUT multi-part form-data file upload REST call in VBA  Tools  gt  References  Code Sub SendEmail        Dim objHTTP As New MSXML2 XMLHTTP      Set objhttp   CreateObject  quot WinHttp WinHttpRequest 5 1 quot       Set objHTTP   CreateObject  quot MSXML2 ServerXMLHTTP quot       URL    quot http   localhost 8888 rest mail send quot      objHTTP Open  quot POST quot   URL  False     objHTTP send   quot   quot  quot key quot  quot  null  quot  quot from quot  quot   quot  quot me me com quot  quot   quot  quot to quot  quot  null  quot  quot cc quot  quot  null  quot  quot bcc quot  quot  null  quot  quot date quot  quot  null  quot  quot subject quot  quot   quot  quot My Subject quot  quot   quot  quot body quot  quot  null  quot  quot attachments quot  quot  null  quot       Print objHTTP Status     Print objHTTP ResponseText      End Sub  Reference WinHttpRequest object  http   msdn microsoft com en-us library windows desktop aa384106 v vs 85  aspx

User · Answer

For reading REST data  at least OData Consider Microsoft Power Query  You won t be able to write data  However  you can read data very well

User · Answer

Check out this one   https   github com VBA-tools VBA-Web  It s a high level library for dealing with REST  It s OOP  works with JSON  but also works with any other format

User · Answer

I had to use Debug print instead of Print  which works in the Immediate window   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     me me com     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

User · Answer

You probably haven t added a reference to Microsoft XML  any version  for Dim objHTTP As New MSXML2 XMLHTTP in the VBA window s Tools References    dialog   Also  it s a good idea to avoid using late binding  CreateObject      better to use early binding  Dim objHTTP As New MSXML2 XMLHTTP   as early binding allows you to use Intellisense to list the members and do all sorts of design-time validation

[vba] Getting "method not valid without suitable object" error when trying to make a HTTP request in VBA?

Examples related to vba

Examples related to rest

Examples related to excel