HTTPS Post using Excel VBA with XML body and request headers issue
1
I am trying to use Excel-VBA to perform an https POST of an XML body as follows:
Public Sub test()
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim myDom As Object
Set myDom = CreateObject("MSXML2.DOMDocument.6.0")
URL = "https://uatgateway.topl.com.au/quote"
myDom.async = False
myXMLstr = URL & "<?xml version=&Chr(34)&1.0&Chr(34)& encoding=&Chr(34)&UTF-
8&Chr(34)& standalone=&Chr(34)&yes&Chr(34)&?><IMPULSE><REQUEST><QUOTE>
<MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID>
<MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION>
<QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO>
<MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE>
</REQUEST></IMPULSE>"
myDom.Load (myXMLstr)
xmlhttp.Open "post", URL, False
xmlhttp.setRequestHeader "Content-Type", "application/xml"
xmlhttp.setRequestHeader "Content-Length", "356"
xmlhttp.setRequestHeader "Accept", "text/xml"
xmlhttp.setRequestHeader "Host", "uatgateway.topl.com.au"
xmlhttp.setRequestHeader "Cache-Control", "no-cache"
xmlhttp.send myDom.XML
MsgBox xmlhttp.responseText
End Sub
My VBA code compiles ok, but on execution stops at the xmlhttp.send line almost immediately with an error "the server returned an invalid or unrecognised response". The same POST on Postman works. I am not sure if I an referencing the myDom.XML correctly?
Any suggestions welcome?
excel xml vba http
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
add a comment |
1
I am trying to use Excel-VBA to perform an https POST of an XML body as follows:
Public Sub test()
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim myDom As Object
Set myDom = CreateObject("MSXML2.DOMDocument.6.0")
URL = "https://uatgateway.topl.com.au/quote"
myDom.async = False
myXMLstr = URL & "<?xml version=&Chr(34)&1.0&Chr(34)& encoding=&Chr(34)&UTF-
8&Chr(34)& standalone=&Chr(34)&yes&Chr(34)&?><IMPULSE><REQUEST><QUOTE>
<MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID>
<MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION>
<QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO>
<MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE>
</REQUEST></IMPULSE>"
myDom.Load (myXMLstr)
xmlhttp.Open "post", URL, False
xmlhttp.setRequestHeader "Content-Type", "application/xml"
xmlhttp.setRequestHeader "Content-Length", "356"
xmlhttp.setRequestHeader "Accept", "text/xml"
xmlhttp.setRequestHeader "Host", "uatgateway.topl.com.au"
xmlhttp.setRequestHeader "Cache-Control", "no-cache"
xmlhttp.send myDom.XML
MsgBox xmlhttp.responseText
End Sub
My VBA code compiles ok, but on execution stops at the xmlhttp.send line almost immediately with an error "the server returned an invalid or unrecognised response". The same POST on Postman works. I am not sure if I an referencing the myDom.XML correctly?
Any suggestions welcome?
excel xml vba http
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
Think you need to look at yourmyXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).
– chillin
Nov 23 '18 at 10:50
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
You might need to change yourmyXMLstrassignment tomyXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>"-- something like that, though it's possible the URL should not be in it
– chillin
Nov 23 '18 at 11:01
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02
add a comment |
1
1
1
I am trying to use Excel-VBA to perform an https POST of an XML body as follows:
Public Sub test()
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim myDom As Object
Set myDom = CreateObject("MSXML2.DOMDocument.6.0")
URL = "https://uatgateway.topl.com.au/quote"
myDom.async = False
myXMLstr = URL & "<?xml version=&Chr(34)&1.0&Chr(34)& encoding=&Chr(34)&UTF-
8&Chr(34)& standalone=&Chr(34)&yes&Chr(34)&?><IMPULSE><REQUEST><QUOTE>
<MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID>
<MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION>
<QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO>
<MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE>
</REQUEST></IMPULSE>"
myDom.Load (myXMLstr)
xmlhttp.Open "post", URL, False
xmlhttp.setRequestHeader "Content-Type", "application/xml"
xmlhttp.setRequestHeader "Content-Length", "356"
xmlhttp.setRequestHeader "Accept", "text/xml"
xmlhttp.setRequestHeader "Host", "uatgateway.topl.com.au"
xmlhttp.setRequestHeader "Cache-Control", "no-cache"
xmlhttp.send myDom.XML
MsgBox xmlhttp.responseText
End Sub
My VBA code compiles ok, but on execution stops at the xmlhttp.send line almost immediately with an error "the server returned an invalid or unrecognised response". The same POST on Postman works. I am not sure if I an referencing the myDom.XML correctly?
Any suggestions welcome?
excel xml vba http
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
I am trying to use Excel-VBA to perform an https POST of an XML body as follows:
Public Sub test()
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Dim myDom As Object
Set myDom = CreateObject("MSXML2.DOMDocument.6.0")
URL = "https://uatgateway.topl.com.au/quote"
myDom.async = False
myXMLstr = URL & "<?xml version=&Chr(34)&1.0&Chr(34)& encoding=&Chr(34)&UTF-
8&Chr(34)& standalone=&Chr(34)&yes&Chr(34)&?><IMPULSE><REQUEST><QUOTE>
<MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID>
<MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION>
<QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO>
<MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE>
</REQUEST></IMPULSE>"
myDom.Load (myXMLstr)
xmlhttp.Open "post", URL, False
xmlhttp.setRequestHeader "Content-Type", "application/xml"
xmlhttp.setRequestHeader "Content-Length", "356"
xmlhttp.setRequestHeader "Accept", "text/xml"
xmlhttp.setRequestHeader "Host", "uatgateway.topl.com.au"
xmlhttp.setRequestHeader "Cache-Control", "no-cache"
xmlhttp.send myDom.XML
MsgBox xmlhttp.responseText
End Sub
My VBA code compiles ok, but on execution stops at the xmlhttp.send line almost immediately with an error "the server returned an invalid or unrecognised response". The same POST on Postman works. I am not sure if I an referencing the myDom.XML correctly?
Any suggestions welcome?
excel xml vba http
excel xml vba http
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
edited Nov 23 '18 at 10:05
K.Dᴀᴠɪs
7,229112439
7,229112439
asked Nov 23 '18 at 9:50
James LingJames Ling
61
asked Nov 23 '18 at 9:50
James LingJames Ling
61
asked Nov 23 '18 at 9:50
James LingJames Ling
61
61
Think you need to look at yourmyXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).
– chillin
Nov 23 '18 at 10:50
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
You might need to change yourmyXMLstrassignment tomyXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>"-- something like that, though it's possible the URL should not be in it
– chillin
Nov 23 '18 at 11:01
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02
add a comment |
Think you need to look at yourmyXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).
– chillin
Nov 23 '18 at 10:50
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
You might need to change yourmyXMLstrassignment tomyXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>"-- something like that, though it's possible the URL should not be in it
– chillin
Nov 23 '18 at 11:01
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02
Think you need to look at your
myXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).– chillin
Nov 23 '18 at 10:50
Think you need to look at your
myXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).– chillin
Nov 23 '18 at 10:50
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
You might need to change your
myXMLstr assignment to myXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>" -- something like that, though it's possible the URL should not be in it– chillin
Nov 23 '18 at 11:01
You might need to change your
myXMLstr assignment to myXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>" -- something like that, though it's possible the URL should not be in it– chillin
Nov 23 '18 at 11:01
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53444235%2fhttps-post-using-excel-vba-with-xml-body-and-request-headers-issue%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53444235%2fhttps-post-using-excel-vba-with-xml-body-and-request-headers-issue%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Think you need to look at your
myXMLstr, your VBA code/functions are being interpreted as literal strings as everything is between double quotes (from what I can see).– chillin
Nov 23 '18 at 10:50
Also, you seem to be prepending the URL to the beginning of your XML document, which itself does not presently seem to be valid (see previous comment). Could you post the successful request in Postman, so that it's clear whether the body should begin with a URL? Thanks
– chillin
Nov 23 '18 at 10:54
You might need to change your
myXMLstrassignment tomyXMLstr = URL & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "encoding=" & Chr(34) & "UTF-8" & Chr(34) & "standalone=" & Chr(34) & "yes" & Chr(34) & "?><IMPULSE><REQUEST><QUOTE> <MERCHANT><ACQUIRERID>Bank A</ACQUIRERID><NETWORKID>eComm</NETWORKID> <MERCHANTID>987654321</MERCHANTID></MERCHANT><MCH_TRANSACTION> <QUOTEREF>3456789012</QUOTEREF><MCH_ISO>036</MCH_ISO> <MCH_AMT>1000.00</MCH_AMT><BIN>400555000</BIN></MCH_TRANSACTION></QUOTE></REQUEST></IMPULSE>"-- something like that, though it's possible the URL should not be in it– chillin
Nov 23 '18 at 11:01
Hi chillin ...thanks, you're right about the myXMLstr assignment and removing the URL prependment. But also, I needed to use the LoadXML method of the DOMdocument model instead of the Load method. That's because I am loading the XML as a string and not as a resource pointing to the XML string. Fixing all these means it is now working ...so many thanks for the tips!
– James Ling
Nov 23 '18 at 22:02