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

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"":""me@me.com"",""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

The libkod.info domain name recently expired, making the first link unreachable. — CWilson, Jun 29 '16 at 18:05

score 15 · Accepted Answer · answered Oct 23 '13 at 22:51

15

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.

answered Oct 23 '13 at 22:51

Monty Wild

3,981
1
21
36

Ok I added those references and added a screenshot, but it still gives that error. I found that `Set objHTTP = New MSXML2.XMLHTTP60` will work, but it still fails at the `Print objHTTP.Status` with 'Method not valid without suitable object.' – Chloe Oct 23 '13 at 23:05

score 14 · Answer 2 · answered Oct 23 '13 at 23:36

14

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

answered Oct 23 '13 at 23:36

Chloe

25,162
40
190
357

Where can I read about all those different objects. XMLHTTP/XMLHTTP60/WinHttpRequest/WinHttpRequest.5.1/ServerXMLHTTP... ? – przemo_li Mar 07 '14 at 08:44
2

Here's the documentation: http://msdn.microsoft.com/en-us/library/windows/desktop/aa384106(v=vs.85).aspx – Chloe Mar 07 '14 at 23:05
I get connection with server could not be set. :-( – QHarr Jun 21 '18 at 07:18

score 12 · Answer 3 · edited Jul 05 '16 at 20:21

12

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.

edited Jul 05 '16 at 20:21

Marcos Dimitrio

6,651
5
38
62

answered Mar 10 '14 at 12:30

przemo_li

3,932
4
35
60

score 0 · Answer 4 · answered Sep 01 '18 at 02:42

0

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.

answered Sep 01 '18 at 02:42

Justin Dearing

14,270
22
88
161

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

Tools > References

Code

Reference

4 Answers4

Linked