Excel VBA, XMTHTTP & Cookies

[Mark Simon]

(Sorry about cross-posting; I'm not sure which group is the best one for
this question.)

I need to get data into Excel from a web site which requires some sort
of authentication. As far as I can tell, the authentication is in the
form of a cookie or two.

I have successfully extracted data from websites using VBA and the
XMLHTTP object, and even from this site in the case where authentication
is not required.

However, I cannot seem to send cookies from VBA. I have a sample
procedure which tests my coding against a test site which displays form
data and cookies:

Sub test()
Dim x As New XMLHTTP
Dim t As String
x.Open "get", "http://www.comparity.net/perl/form.pl", False
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.send
t = x.responseText
WriteTextFile "c:\test.html", t
Debug.Print x.Status
Debug.Print t
End Sub

The repeated setRequestHeader code is to handle a bug, as listed in the
kb article:

http://support.microsoft.com/kb/290899

though I'm not 100% sure that it applies in this case.

The WriteTextFile procedure is used so I can read the response, and is
listed below:

Sub WriteTextFile(FileName, Text)
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName, ForWriting, -2)
f.Write Text
f.Close
End Sub

So, does anybody have any idea why my cookies are not being sent, or how
I can send them some other way?

Thanks,

Mark

 

[Umut Alev [MSFT]]

Mark,
XmlHttp under the covers use Urlmon and it strips the cookies for security
reasons.You need to pass this information some other way, either through url
or send body.

Regards
- Umut Alev [MSFT]

 

[Mark Simon]

Hi Umut

Thanks for the reply.

Do you think I can fake the cookies by building my own headers? Or might
there be an alternative to xmlhttp?

Thanks,

Mark

Mark,
XmlHttp under the covers use Urlmon and it strips the cookies for
security reasons.You need to pass this information some other way,
either through url or send body.

Regards
- Umut Alev [MSFT]

(Sorry about cross-posting; I'm not sure which group is the best one
for this question.)

I need to get data into Excel from a web site which requires some sort
of authentication. As far as I can tell, the authentication is in the
form of a cookie or two.

I have successfully extracted data from websites using VBA and the
XMLHTTP object, and even from this site in the case where
authentication is not required.

However, I cannot seem to send cookies from VBA. I have a sample
procedure which tests my coding against a test site which displays
form data and cookies:

Sub test()
Dim x As New XMLHTTP
Dim t As String
x.Open "get", "http://www.comparity.net/perl/form.pl", False
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.send
t = x.responseText
WriteTextFile "c:\test.html", t
Debug.Print x.Status
Debug.Print t
End Sub

The repeated setRequestHeader code is to handle a bug, as listed in
the kb article:

http://support.microsoft.com/kb/290899

though I'm not 100% sure that it applies in this case.

The WriteTextFile procedure is used so I can read the response, and is
listed below:

Sub WriteTextFile(FileName, Text)
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName, ForWriting, -2)
f.Write Text
f.Close
End Sub

So, does anybody have any idea why my cookies are not being sent, or
how I can send them some other way?

Thanks,

Mark

 

[Alex Krawarik [MSFT]]

Mark, it doesnt look like you are using XMLHTTP for any xml-specific
functionality (you are using responseText, not responseXml.xml)? If you're
just sending general data, can you check out the WinHTTP.WinHTTPRequest.5.1
object and see if that meets your needs?

Alex

Hi Umut

Thanks for the reply.

Do you think I can fake the cookies by building my own headers? Or might
there be an alternative to xmlhttp?

Thanks,

Mark

Mark,
XmlHttp under the covers use Urlmon and it strips the cookies for
security reasons.You need to pass this information some other way, either
through url or send body.

Regards
- Umut Alev [MSFT]

(Sorry about cross-posting; I'm not sure which group is the best one for
this question.)

I need to get data into Excel from a web site which requires some sort
of authentication. As far as I can tell, the authentication is in the
form of a cookie or two.

I have successfully extracted data from websites using VBA and the
XMLHTTP object, and even from this site in the case where authentication
is not required.

However, I cannot seem to send cookies from VBA. I have a sample
procedure which tests my coding against a test site which displays form
data and cookies:

Sub test()
Dim x As New XMLHTTP
Dim t As String
x.Open "get", "http://www.comparity.net/perl/form.pl", False
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.send
t = x.responseText
WriteTextFile "c:\test.html", t
Debug.Print x.Status
Debug.Print t
End Sub

The repeated setRequestHeader code is to handle a bug, as listed in the
kb article:

http://support.microsoft.com/kb/290899

though I'm not 100% sure that it applies in this case.

The WriteTextFile procedure is used so I can read the response, and is
listed below:

Sub WriteTextFile(FileName, Text)
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName, ForWriting, -2)
f.Write Text
f.Close
End Sub

So, does anybody have any idea why my cookies are not being sent, or how
I can send them some other way?

Thanks,

Mark

 

[Mark Simon]

Thanks Alex

I had a look at WinHTTP.WinHTTPRequest.5.1, and find that it does indeed
send my cookies. However, I can't get that to send my post data!

Here is a revised test procedure which successfully sends the cookies
but not the data:

Sub test()
Dim w As New WinHttp.WinHttpRequest
Dim t As String, qs As String
qs = "this=that&more=less"
w.Open "post", "http://www.comparity.net/perl/form.pl?a=b", False
w.setRequestHeader "Cookie", "one=foo"
w.setRequestHeader "Cookie", "two=bar"
w.send qs
t = w.responseText
WriteTextFile "c:\test.html", t
Debug.Print w.Status
Debug.Print t
End Sub

This is frustrating. Any suggestions?

Thanks,

Mark

Mark, it doesnt look like you are using XMLHTTP for any xml-specific
functionality (you are using responseText, not responseXml.xml)? If you're
just sending general data, can you check out the WinHTTP.WinHTTPRequest.5.1
object and see if that meets your needs?

Alex

Hi Umut

Thanks for the reply.

Do you think I can fake the cookies by building my own headers? Or might
there be an alternative to xmlhttp?

Thanks,

Mark

Mark,
XmlHttp under the covers use Urlmon and it strips the cookies for
security reasons.You need to pass this information some other way, either
through url or send body.

Regards
- Umut Alev [MSFT]

(Sorry about cross-posting; I'm not sure which group is the best one for
this question.)

I need to get data into Excel from a web site which requires some sort
of authentication. As far as I can tell, the authentication is in the
form of a cookie or two.

I have successfully extracted data from websites using VBA and the
XMLHTTP object, and even from this site in the case where authentication
is not required.

However, I cannot seem to send cookies from VBA. I have a sample
procedure which tests my coding against a test site which displays form
data and cookies:

Sub test()
Dim x As New XMLHTTP
Dim t As String
x.Open "get", "http://www.comparity.net/perl/form.pl", False
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.send
t = x.responseText
WriteTextFile "c:\test.html", t
Debug.Print x.Status
Debug.Print t
End Sub

The repeated setRequestHeader code is to handle a bug, as listed in the
kb article:

http://support.microsoft.com/kb/290899

though I'm not 100% sure that it applies in this case.

The WriteTextFile procedure is used so I can read the response, and is
listed below:

Sub WriteTextFile(FileName, Text)
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName, ForWriting, -2)
f.Write Text
f.Close
End Sub

So, does anybody have any idea why my cookies are not being sent, or how
I can send them some other way?

Thanks,

Mark

 

[Joe Fawcett]

Mark

I haven't used the class often but some servers will only accept uppercase
requests, POST instead of post.

--

Joe Fawcett (MVP)

http://joe.fawcett.name

Thanks Alex

I had a look at WinHTTP.WinHTTPRequest.5.1, and find that it does indeed
send my cookies. However, I can't get that to send my post data!

Here is a revised test procedure which successfully sends the cookies but
not the data:

Sub test()
Dim w As New WinHttp.WinHttpRequest
Dim t As String, qs As String
qs = "this=that&more=less"
w.Open "post", "http://www.comparity.net/perl/form.pl?a=b", False
w.setRequestHeader "Cookie", "one=foo"
w.setRequestHeader "Cookie", "two=bar"
w.send qs
t = w.responseText
WriteTextFile "c:\test.html", t
Debug.Print w.Status
Debug.Print t
End Sub

This is frustrating. Any suggestions?

Thanks,

Mark

Mark, it doesnt look like you are using XMLHTTP for any xml-specific
functionality (you are using responseText, not responseXml.xml)? If
you're just sending general data, can you check out the
WinHTTP.WinHTTPRequest.5.1 object and see if that meets your needs?

Alex

Hi Umut

Thanks for the reply.

Do you think I can fake the cookies by building my own headers? Or might
there be an alternative to xmlhttp?

Thanks,

Mark

Umut Alev [MSFT] wrote:
Mark,
XmlHttp under the covers use Urlmon and it strips the cookies for
security reasons.You need to pass this information some other way,
either through url or send body.

Regards
- Umut Alev [MSFT]

(Sorry about cross-posting; I'm not sure which group is the best one
for this question.)

I need to get data into Excel from a web site which requires some sort
of authentication. As far as I can tell, the authentication is in the
form of a cookie or two.

I have successfully extracted data from websites using VBA and the
XMLHTTP object, and even from this site in the case where
authentication is not required.

However, I cannot seem to send cookies from VBA. I have a sample
procedure which tests my coding against a test site which displays
form data and cookies:

Sub test()
Dim x As New XMLHTTP
Dim t As String
x.Open "get", "http://www.comparity.net/perl/form.pl", False
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.setRequestHeader "Cookie", "one=foo;two=bar;"
x.send
t = x.responseText
WriteTextFile "c:\test.html", t
Debug.Print x.Status
Debug.Print t
End Sub

The repeated setRequestHeader code is to handle a bug, as listed in
the kb article:

http://support.microsoft.com/kb/290899

though I'm not 100% sure that it applies in this case.

The WriteTextFile procedure is used so I can read the response, and is
listed below:

Sub WriteTextFile(FileName, Text)
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(FileName, ForWriting, -2)
f.Write Text
f.Close
End Sub

So, does anybody have any idea why my cookies are not being sent, or
how I can send them some other way?

Thanks,

Mark

 

[Mark Simon]

Hi Joe

That was it. Thanks for the suggestion.

Thanks also Umut & Alex. It all came together.

Mark