Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.xml
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enable cookies, Icannot download Excel from My documen | Excel Discussion (Misc queries) | |||
Eat Cookies and Loss Weight. See How | Excel Worksheet Functions | |||
How do I disable cookies? | New Users to Excel | |||
Need help with cookies also | Excel Discussion (Misc queries) | |||
Web Query & Cookies | Excel Programming |