MSXML2.XMLHTTP send method works with early binding, fails with late binding

Question

The code below works. But if I comment out the line Dim objRequest As MSXML2.XMLHTTP and uncomment the line Dim objRequest As Object it fails with the error message :

The parameter is incorrect

Why, and what (if anything) can I do about it?

Public Function GetSessionId(strApiId, strUserName, strPassword) As String

    Dim strPostData As String

    Dim objRequest As MSXML2.XMLHTTP
    'Dim objRequest As Object '

    strPostData = "api_id=" & strApiId & "&user=" & strUserName & "&password=" & strPassword

    Set objRequest = New MSXML2.XMLHTTP
    With objRequest
        .Open "POST", "https://api.clickatell.com/http/auth", False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send strPostData
        GetSessionId = .responseText
    End With

End Function

Corey, yes, I know I would have to do that in order for my code to work without a reference to the MSXML type library. That's not the issue here. The code fails when using Dim objRequest As Object regardless of whether I use

Set objRequest = NEW MSXML2.XMLHTTP with the reference, or

Set objRequest = CreateObject("MSXML2.XMLHTTP") without the reference.

Tomalak · Accepted Answer · 2017-05-22T13:24:57.553

19

For some reason, this works:

Dim strPostData As String
Dim objRequest As Object

strPostData = "api_id=" & strApiId & "&user=" & strUserName & "&password=" & strPassword

Set objRequest = New MSXML2.XMLHTTP
With objRequest
  .Open "POST", "https://api.clickatell.com/http/auth", False
  .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  .send (strPostData)
   GetSessionId = .responseText
End With

Instead of building the URL-encoded strPostData via string concatenation, it's strongly advisable to use a URL encoding function:

strPostData = "api_id=" & URLEncode(strApiId) & _
              "&user=" & URLEncode(strUserName) & _
              "&password=" & URLEncode(strPassword)

A couple of choices for a URLEncode() function in VBA are in this thread: How can I URL encode a string in Excel VBA?

edited May 22 '17 at 13:24

answered Nov 21 '08 at 14:24

Tomalak

332,285
67
532
628

3

Very strange that the solution was to add parathensis to `.send (strPostData)`. But I just confirmed that it is really the case. – b.roth Aug 09 '10 at 17:47
@Bruno: Thanks for the confirmation. I'm convinced that there is a completely logical explanation for this, somewhere in the guts of VBA or VBA-to-COM interaction in general or the MSXML2.XMLHTTP library in particular, but I have no idea. I'm sure @Eric Lippert could explain. ;) – Tomalak Aug 09 '10 at 18:28
1

Adding parentheses around `strPostData` forces it to be passed By Value. I assumed you would only do this if you needed to "protect" your local variable from potentially being changed by the procedure being called. I'm not sure why the method itself behaves differently as a result. For more info see here: http://stackoverflow.com/questions/1070863/hidden-features-of-vba#1070942 – mwolfe02 Nov 01 '10 at 20:56
@mwolfe02: Great, thanks for the explanation, this makes some sense from the VB end. My guess would be: This either invokes a different overloaded `send()` method with the XMLHTTP library, or it may have something to do with internal string buffering differences of ByVal and ByRef params. – Tomalak Nov 02 '10 at 15:16
@Henrik If you are also using this to POST data, like in the code sample above, check the answer update I just made. – Tomalak May 22 '17 at 13:26
I use it for any method, this time it was GET that gave the XMLHTTP Send error. I didn't notice what you changed just now, actually. And it was hard to see the difference to Brendan's code when I found it. I diffed them with my eyes :) I think the answer could be clearer if you said "For some reason, just putting parentheses around the parameter works" or similar. Cheers! – Henrik Erlandsson May 23 '17 at 06:41
@Henrik You can always view all revisions of a post on StackOverflow when you click the "edited {date}" link below it. In this case I added the hint about URL-encoding data in VBA. – Tomalak May 23 '17 at 11:07

score 3 · Answer 2 · answered Nov 21 '08 at 13:54

3

If you use the Dim objRequest As Object then you would need to code:
Set objRequest = CreateObject("MSXML2.XMLHTTP")

answered Nov 21 '08 at 13:54

Corey Trager

22,649
18
83
121

You tell by issuing "New MSXML2.XMLHTTP". For me both ways to do it work. – Tomalak Nov 21 '08 at 14:00

score 0 · Answer 3 · answered Feb 22 '10 at 16:26

I realise this is nearly identical to the code from Tomalek above (all credit due to you!), but this question helped me towards a full solution to a problem I had (Excel submitting to PHP server, then dealing with response)...so in case this is of any help to anyone else:

Sub Button1_Click2()

Dim objXMLSendDoc As Object
Set objXMLSendDoc = New MSXML2.DOMDocument
objXMLSendDoc.async = False
Dim myxml As String
myxml = "<?xml version='1.0'?><Request>Do Something</Request>"
If Not objXMLSendDoc.LoadXML(myxml) Then
    Err.Raise objXMLSendDoc.parseError.ErrorCode, , objXMLSendDoc.parseError.reason
End If

Dim objRequest As MSXML2.XMLHTTP
Set objRequest = New MSXML2.XMLHTTP
With objRequest
    .Open "POST", "http://localhost/SISADraftCalcs/Test2.php", False
    .setRequestHeader "Content-Type", "application/xml;charset=UTF-16"
    .setRequestHeader "Cache-Control", "no-cache"
    .send objXMLSendDoc
End With

Dim objXMLDoc As MSXML2.DOMDocument
Set objXMLDoc = objRequest.responseXML
If objXMLDoc.XML = "" Then
    objXMLDoc.LoadXML objRequest.responseText
    If objXMLDoc.parseError.ErrorCode <> 0 Then
        MsgBox objXMLDoc.parseError.reason
    End If
End If

Dim rootNode As IXMLDOMElement
Set rootNode = objXMLDoc.DocumentElement

MsgBox rootNode.SelectNodes("text").Item(0).text

End Sub

MSXML2.XMLHTTP send method works with early binding, fails with late binding

3 Answers3

Linked