CODEWITHSUNDEEP
vbarestexcel
Chloe
Chloe

Reputation: 26264

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

I tried to follow this example: http://libkod.info/officexml-CHP-9-SECT-5.shtml - Archive.org - Donate

but it gave this error

Imgur

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:

Imgur

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 > References

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

Reference

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

Upvotes: 19

Views: 106538

Answers (4)

Justin Dearing
Justin Dearing

Reputation: 14928

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.

Upvotes: 0

przemo_li
przemo_li

Reputation: 4053

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.

Upvotes: 12

Chloe
Chloe

Reputation: 26264

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"":""[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

Upvotes: 14

Monty Wild
Monty Wild

Reputation: 3991

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.

Upvotes: 15

Related Questions