Excel VBA, XMTHTTP & Cookies - XML SOAP

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/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

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/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, 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" <mark@comparity.not.example.net> wrote in message
news:45891cbf$0$19406$afc38c87@news.optusnet.com.au...
> 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/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
>>

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" <mark@comparity.not.example.net> wrote in message
> news:45891cbf$0$19406$afc38c87@news.optusnet.com.au...
>> 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/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

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" <mark@comparity.not.example.net> wrote in message
news:458a4888$0$29329$afc38c87@news.optusnet.com.au...
> 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" <mark@comparity.not.example.net> wrote in message
>> news:45891cbf$0$19406$afc38c87@news.optusnet.com.au...
>>> 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/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
>>

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.
>