Mixing
Using MSXML in a VBA script to pull website data
0
I have the following code from http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/#comment-60553
Sub GetTable()
Dim xml As Object ' MSXML2.XMLHTTP60
Dim htmlDoc As Object ' MSHTML.HTMLDocument
Dim htmlBody As Object ' MSHTML.HTMLBody
Dim ieTable As Object
Dim clip As DataObject
Set xml = GetMSXML
With xml
.Open "POST", "https://web.site", False
.send "username=myname&password=mypassword"""
End With
With xml
.Open "POST", "https://web.site/anotherpage", False
End With
Set htmlDoc = CreateHTMLDoc
Set htmlBody = htmlDoc.Body
htmlBody.innerHTML = xml.responseText
Set ieTable = htmlBody.all.Item("report")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Range("A1").Select
ActiveSheet.PasteSpecial "Unicode Text"""
End If
End Sub
Function CreateHTMLDoc() As Object ' MSHTML.HTMLDocument
Set CreateHTMLDoc = CreateObject("htmlfile")
End Function
Function GetMSXML() As Object ' MSXML2.XMLHTTP
On Error Resume Next
Set GetMSXML = CreateObject("MSXML2.XMLHTTP")
End Function
Using this code I am attempting to access the site web.site and pass it a username and password to login, before proceeding to another page on the site, before copying the content of a table (results) into sheet1 of the excel workbook.
I have tried to debug this using f8 but without the visual browser that I would get if I were to follow this page http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/
then its a bit difficult to see exactly what is happening and where it is failing.
excel vba excel-vba msxml
asked Nov 20 '18 at 9:00
AaranAaran
33
add a comment |
0
I have the following code from http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/#comment-60553
Sub GetTable()
Dim xml As Object ' MSXML2.XMLHTTP60
Dim htmlDoc As Object ' MSHTML.HTMLDocument
Dim htmlBody As Object ' MSHTML.HTMLBody
Dim ieTable As Object
Dim clip As DataObject
Set xml = GetMSXML
With xml
.Open "POST", "https://web.site", False
.send "username=myname&password=mypassword"""
End With
With xml
.Open "POST", "https://web.site/anotherpage", False
End With
Set htmlDoc = CreateHTMLDoc
Set htmlBody = htmlDoc.Body
htmlBody.innerHTML = xml.responseText
Set ieTable = htmlBody.all.Item("report")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Range("A1").Select
ActiveSheet.PasteSpecial "Unicode Text"""
End If
End Sub
Function CreateHTMLDoc() As Object ' MSHTML.HTMLDocument
Set CreateHTMLDoc = CreateObject("htmlfile")
End Function
Function GetMSXML() As Object ' MSXML2.XMLHTTP
On Error Resume Next
Set GetMSXML = CreateObject("MSXML2.XMLHTTP")
End Function
Using this code I am attempting to access the site web.site and pass it a username and password to login, before proceeding to another page on the site, before copying the content of a table (results) into sheet1 of the excel workbook.
I have tried to debug this using f8 but without the visual browser that I would get if I were to follow this page http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/
then its a bit difficult to see exactly what is happening and where it is failing.
excel vba excel-vba msxml
asked Nov 20 '18 at 9:00
AaranAaran
33
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressingF12) and monitor requests that appear under theNetworktab as you manually log in -- to see the requests that need emulating.
– chillin
Nov 20 '18 at 14:22
add a comment |
0
0
0
1
I have the following code from http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/#comment-60553
Sub GetTable()
Dim xml As Object ' MSXML2.XMLHTTP60
Dim htmlDoc As Object ' MSHTML.HTMLDocument
Dim htmlBody As Object ' MSHTML.HTMLBody
Dim ieTable As Object
Dim clip As DataObject
Set xml = GetMSXML
With xml
.Open "POST", "https://web.site", False
.send "username=myname&password=mypassword"""
End With
With xml
.Open "POST", "https://web.site/anotherpage", False
End With
Set htmlDoc = CreateHTMLDoc
Set htmlBody = htmlDoc.Body
htmlBody.innerHTML = xml.responseText
Set ieTable = htmlBody.all.Item("report")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Range("A1").Select
ActiveSheet.PasteSpecial "Unicode Text"""
End If
End Sub
Function CreateHTMLDoc() As Object ' MSHTML.HTMLDocument
Set CreateHTMLDoc = CreateObject("htmlfile")
End Function
Function GetMSXML() As Object ' MSXML2.XMLHTTP
On Error Resume Next
Set GetMSXML = CreateObject("MSXML2.XMLHTTP")
End Function
Using this code I am attempting to access the site web.site and pass it a username and password to login, before proceeding to another page on the site, before copying the content of a table (results) into sheet1 of the excel workbook.
I have tried to debug this using f8 but without the visual browser that I would get if I were to follow this page http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/
then its a bit difficult to see exactly what is happening and where it is failing.
excel vba excel-vba msxml
asked Nov 20 '18 at 9:00
AaranAaran
33
I have the following code from http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/#comment-60553
Sub GetTable()
Dim xml As Object ' MSXML2.XMLHTTP60
Dim htmlDoc As Object ' MSHTML.HTMLDocument
Dim htmlBody As Object ' MSHTML.HTMLBody
Dim ieTable As Object
Dim clip As DataObject
Set xml = GetMSXML
With xml
.Open "POST", "https://web.site", False
.send "username=myname&password=mypassword"""
End With
With xml
.Open "POST", "https://web.site/anotherpage", False
End With
Set htmlDoc = CreateHTMLDoc
Set htmlBody = htmlDoc.Body
htmlBody.innerHTML = xml.responseText
Set ieTable = htmlBody.all.Item("report")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Range("A1").Select
ActiveSheet.PasteSpecial "Unicode Text"""
End If
End Sub
Function CreateHTMLDoc() As Object ' MSHTML.HTMLDocument
Set CreateHTMLDoc = CreateObject("htmlfile")
End Function
Function GetMSXML() As Object ' MSXML2.XMLHTTP
On Error Resume Next
Set GetMSXML = CreateObject("MSXML2.XMLHTTP")
End Function
Using this code I am attempting to access the site web.site and pass it a username and password to login, before proceeding to another page on the site, before copying the content of a table (results) into sheet1 of the excel workbook.
I have tried to debug this using f8 but without the visual browser that I would get if I were to follow this page http://dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/
then its a bit difficult to see exactly what is happening and where it is failing.
excel vba excel-vba msxml
excel vba excel-vba msxml
asked Nov 20 '18 at 9:00
AaranAaran
33
asked Nov 20 '18 at 9:00
AaranAaran
33
asked Nov 20 '18 at 9:00
AaranAaran
33
asked Nov 20 '18 at 9:00
AaranAaran
33
asked Nov 20 '18 at 9:00
AaranAaran
33
33
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressingF12) and monitor requests that appear under theNetworktab as you manually log in -- to see the requests that need emulating.
– chillin
Nov 20 '18 at 14:22
add a comment |
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressingF12) and monitor requests that appear under theNetworktab as you manually log in -- to see the requests that need emulating.
– chillin
Nov 20 '18 at 14:22
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressing
F12) and monitor requests that appear under the Network tab as you manually log in -- to see the requests that need emulating.– chillin
Nov 20 '18 at 14:22
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressing
F12) and monitor requests that appear under the Network tab as you manually log in -- to see the requests that need emulating.– chillin
Nov 20 '18 at 14:22
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%2f53389444%2fusing-msxml-in-a-vba-script-to-pull-website-data%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%2f53389444%2fusing-msxml-in-a-vba-script-to-pull-website-data%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
You can write out the response for starters or use an API tester such as Postman to see what the server response is.
– QHarr
Nov 20 '18 at 9:21
Thanks, could you elaborate on writing out the responses?
– Aaran
Nov 20 '18 at 9:27
Debug.Print xml.Status, xml.responseText
– QHarr
Nov 20 '18 at 9:29
1. Ensure you're POSTing your credentials to the correct URL, 2. Services/websites that require log-in usually have some means of client/session management. Check the 'Set-Cookie' header in the server's response, ensure you include it in subsequent requests (especially once you've logged in) -- as that cookie is associated with your session. 3. It might be helpful to open an Internet browser, open the developer tools (in Chrome you can do this by pressing
F12) and monitor requests that appear under theNetworktab as you manually log in -- to see the requests that need emulating.– chillin
Nov 20 '18 at 14:22