Asp Forum - Excel VBA, XMTHTTP & Cookies - microsoft.public.excel.programming

Mark Simon

12/20/2006 7:25:00 AM

(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/for..., 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...

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

2 Answers

Umut Alev [MSFT]

12/20/2006 11:00:00 AM

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" <mark@comparity.not.example.net> wrote in message
news:4588e533$0$16556$afc38c87@news.optusnet.com.au...
> (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/for..., 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...
>
> 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

12/20/2006 11:22:00 AM

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" <mark@comparity.not.example.net> wrote in message
> news:4588e533$0$16556$afc38c87@news.optusnet.com.au...
>> (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/for..., 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...
>>
>> 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
>

microsoft.public.excel.programming

Excel VBA, XMTHTTP & Cookies

Mark Simon

Umut Alev [MSFT]

Mark Simon

x Login to ForumsZone