Excel VBA, XMTHTTP & Cookies
 

(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

Excel VBA, XMTHTTP & Cookies
 

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" wrote in message
u...
(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

Excel VBA, XMTHTTP & Cookies
 

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]

"Mark Simon" wrote in message
u...
(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

Excel VBA, XMTHTTP & Cookies
 

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


"Mark Simon" wrote in message
u...
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]

"Mark Simon" wrote in message
u...
(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

Excel VBA, XMTHTTP & Cookies
 

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


Alex Krawarik [MSFT] wrote:
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


"Mark Simon" wrote in message
u...
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]

"Mark Simon" wrote in message
u...
(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

Excel VBA, XMTHTTP & Cookies
 

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

"Mark Simon" wrote in message
u...
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


Alex Krawarik [MSFT] wrote:
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


"Mark Simon" wrote in message
u...
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]

"Mark Simon" wrote in message
u...
(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

Excel VBA, XMTHTTP & Cookies
 

Hi Joe

That was it. Thanks for the suggestion.

Thanks also Umut & Alex. It all came together.

Mark

Joe Fawcett wrote:
Mark

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