Excel VBA, XMTHTTP & Cookies - XML SOAP

This is a discussion on Excel VBA, XMTHTTP & Cookies - XML SOAP ; (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 ...

+ Reply to Thread
Results 1 to 7 of 7

Excel VBA, XMTHTTP & Cookies

  1. Default 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. Default Re: 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" <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



  3. Default Re: 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" <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

    >


  4. Default Re: 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" <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

    >>




  5. Default Re: 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" <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

    >
    >


  6. Default Re: 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" <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

    >>



  7. Default Re: 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 to Thread

Similar Threads

  1. Cookies?
    By Application Development in forum DOTNET
    Replies: 0
    Last Post: 11-23-2007, 03:35 AM
  2. Cookies
    By Application Development in forum DOTNET
    Replies: 1
    Last Post: 10-30-2007, 06:24 AM
  3. Problem with Excel reports ::::Excel 2003 Migration To Excel 2007
    By Application Development in forum DOTNET
    Replies: 15
    Last Post: 10-24-2007, 08:34 AM
  4. ASP Cookies
    By Application Development in forum Inetserver
    Replies: 2
    Last Post: 04-17-2007, 10:50 AM
  5. Replies: 2
    Last Post: 09-09-2006, 03:24 PM