1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy cookie from request headers

Discussion in 'VBA Macros' started by YasserKhalil, Feb 6, 2018.

  1. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    Try...
    Code (vb):

    Function GetCookie(strUrl)
        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", strUrl, False
            .SetRequestHeader "REFERER", strUrl
            .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
            .SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
            .SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
            .SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
            .Send
            strCookie = .getResponseHeader("Set-Cookie")
            strCookie = Split(strCookie, ";")(0)
            GetCookie = strCookie
        End With
    End Function

    Sub Demo()
    Debug.Print GetCookie("http://162.217.184.82/i2/default.aspx")
    End Sub
     
    Edit: If you want both part of Set-Cookie header, change strCookie part of code to something like below and do further text manipulation as needed.
    Code (vb):
            strCookie = .getAllResponseHeaders
            strCookie = Split(strCookie, vbCrLf)
            GetCookie = strCookie(5) & vbCrLf & strCookie(6)
    Last edited: Feb 6, 2018
    YasserKhalil likes this.
  4. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    That's great and fascinating ..
    But I didn't find any of the search parameters of this cookie UDF .. I expect that it is related to the search numbers as I explained in the snapshot
    Thank you very much
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    That's not part of cookie. That's part of "Form Data" that's sent to the site.

    upload_2018-2-6_14-45-41.png
  6. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    If you copy this data and put in notepad and used Ctrl + F (Find) and searched for any number you will find the number ( 31 for example) embedded in that data .. and the same with the other numbers

    Attached Files:

  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    I know. That's what I said in post #5. But that's not part of cookie string. That's Form data that's being sent via script and you can't really scrape that part.

    Just construct the string following the structure in code if you need to send form data to the server.
  8. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Thanks a lot. How can I construct the string of the form data .. Forgive me as I am novice at the issues of scraping
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
  10. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Will I put that data form string in your UDF or what .. I am confused about that part?
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    No. UDF is just to get cookie info as you requested initially. Has nothing to do with Form data or actual responseText returned (cookie is part of header/meta data and not in responseText).

    You'd use separate Winhttp/xmlhttp to send "POST" request with form data.

    See codes in links I gave you.
  12. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    I tried that code
    Code (vb):
      Function GetCookie(strUrl)
      Dim strCookie
      Dim ArgumentStr As String
     
        ArgumentStr = "ScriptManager1=SearchFormEx1%24UpdatePanel%7CSearchFormEx1%24btnSearch&ScriptManager1_HiddenField=%3B%3BAjaxControlToolkit%2C%20Version%3D" & _
    "3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3Aeffe2a26%3B%3BAjax" & _
    "ControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b" & _
    "13a33%3A475a4ef5%3A5546a2b%3A497ef277%3Aa43b07eb%3Ad2e10b12%3A37e2e5c9%3A5a682656%3A1d3ed089%3Af9029856%3Ad1a1d569%3B&__VIEWSTATE=&Navigator" & _
    "1%24SearchOptions1%24DocImagesCheck=on&SearchFormEx1%24PINTextBox0=31&SearchFormEx1%24PINTextBox1=01&SearchFormEx1%24PINTextBox2=209&SearchForm" & _
    "Ex1%24PINTextBox3=005&SearchFormEx1%24PINTextBox4=0000&SearchFormEx1%24ACSTextBox_Subdivision=&SearchFormEx1%24ACSTextBox_BlockNo=&SearchForm" & _
    "Ex1%24ACSTextBox_LotNo=&SearchFormEx1%24ACSTextBox_PartOfLotNo=&SearchFormEx1%24ACSTextBox_DeclarationCondoNo=&SearchFormEx1%24ACS" & _
    "TextBox_BuildingNo=&SearchFormEx1%24ACSTextBox_UnitNo=&SearchFormEx1%24ACSTextBox_AcresNo=&SearchFormEx1%24ACS" & _
    "TextBox_Quarter3=&SearchFormEx1%24ACSTextBox_Quarter2=&SearchFormEx1%24ACSTextBox_Quarter1=&SearchFormEx1%24ACSTextBox_Part1Code=&SearchForm" & _
    "Ex1%24ACSTextBox_Part2Code=&SearchFormEx1%24ACSTextBox_OneHalfCode=&ImageViewer1%24ScrollPos=&ImageViewer1%24ScrollPosChange=&ImageViewer" & _
    "1%24_imgContainerWidth=0&ImageViewer1%24_imgContainerHeight=0&ImageViewer1%24isImageViewerVisible=true&ImageViewer1%24hdnWidgetSize=&ImageViewer" & _
    "1%24DragResizeExtender_ClientState=&CertificateViewer1%24ScrollPos=&CertificateViewer1%24ScrollPosChange=&CertificateViewer1%24_imgContainer" & _
    "Width=0&CertificateViewer1%24_imgContainerHeight=0&CertificateViewer1%24isImageViewerVisible=true&CertificateViewer1%24hdnWidgetSize=&Certificate" & _
    "Viewer1%24DragResizeExtender_ClientState=&PTAXViewer1%24ScrollPos=&PTAXViewer1%24ScrollPosChange=&PTAXViewer1%24_imgContainerWidth=0&PTAXViewer" & _
    "1%24_imgContainerHeight=0&PTAXViewer1%24isImageViewerVisible=true&PTAXViewer1%24hdnWidgetSize=&PTAXViewer1%24DragResizeExtender_ClientState=&DocList" & _
    "1%24ctl09=&DocList1%24ctl11=&NameList1%24ScrollPos=&NameList1%24ScrollPosChange=&NameList1%24_SortExpression=&NameList1%24ctl03=&NameList" & _
    "1%24ctl05=&DocDetails1%24PageSize=&DocDetails1%24PageIndex=&DocDetails1%24SortExpression=&BasketCtrl1%24ctl01=&BasketCtrl1%24ctl03=&OrderList" & _
    "1%24ctl01=&OrderList1%24ctl03=&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__ASYNCPOST=true&SearchFormEx1%24btnSearch=Search"
       
        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", strUrl, False
            .setRequestHeader "REFERER", strUrl
            .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
            .setRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
            .setRequestHeader "Accept-Language", "en-us,en;q=0.5"
            .setRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
            .send ArgumentStr
            strCookie = .getResponseHeader("Set-Cookie")
            strCookie = Split(strCookie, ";")(0)
            GetCookie = strCookie
        End With
    End Function

    Sub Demo()
    Debug.Print GetCookie("http://162.217.184.82/i2/default.aspx")
    End Sub
    But that's part of the cookie. I need to copy the same as shown in the snapshot in the first post
    Something like that

    Code (vb):
    ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    ... so you combine two separate process.

    Use the code I gave you to get the cookie and clean it up for use in second code.

    Second code will be needed to send "POST" request using form data and cookie as part of request header.
  14. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Here's the final code but I didn't get any results. Please fix that code ..
    Code (vb):
    Sub Test()
        Dim HTTP As New ServerXMLHTTP60, HTML As New HTMLDocument
        Dim posts As Object, elems As Variant, elem As Object
        Dim F As Boolean
      Dim c As Long
      Dim r As Long
      Dim postdata As String
      Dim strCookie As String
          Dim ArgumentStr As String
     
        ArgumentStr = "ScriptManager1=SearchFormEx1%24UpdatePanel%7CSearchFormEx1%24btnSearch&ScriptManager1_HiddenField=%3B%3BAjaxControlToolkit%2C%20Version%3D" & _
    "3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3Aeffe2a26%3B%3BAjax" & _
    "ControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b" & _
    "13a33%3A475a4ef5%3A5546a2b%3A497ef277%3Aa43b07eb%3Ad2e10b12%3A37e2e5c9%3A5a682656%3A1d3ed089%3Af9029856%3Ad1a1d569%3B&__VIEWSTATE=&Navigator" & _
    "1%24SearchOptions1%24DocImagesCheck=on&SearchFormEx1%24PINTextBox0=31&SearchFormEx1%24PINTextBox1=01&SearchFormEx1%24PINTextBox2=209&SearchForm" & _
    "Ex1%24PINTextBox3=005&SearchFormEx1%24PINTextBox4=0000&SearchFormEx1%24ACSTextBox_Subdivision=&SearchFormEx1%24ACSTextBox_BlockNo=&SearchForm" & _
    "Ex1%24ACSTextBox_LotNo=&SearchFormEx1%24ACSTextBox_PartOfLotNo=&SearchFormEx1%24ACSTextBox_DeclarationCondoNo=&SearchFormEx1%24ACS" & _
    "TextBox_BuildingNo=&SearchFormEx1%24ACSTextBox_UnitNo=&SearchFormEx1%24ACSTextBox_AcresNo=&SearchFormEx1%24ACS" & _
    "TextBox_Quarter3=&SearchFormEx1%24ACSTextBox_Quarter2=&SearchFormEx1%24ACSTextBox_Quarter1=&SearchFormEx1%24ACSTextBox_Part1Code=&SearchForm" & _
    "Ex1%24ACSTextBox_Part2Code=&SearchFormEx1%24ACSTextBox_OneHalfCode=&ImageViewer1%24ScrollPos=&ImageViewer1%24ScrollPosChange=&ImageViewer" & _
    "1%24_imgContainerWidth=0&ImageViewer1%24_imgContainerHeight=0&ImageViewer1%24isImageViewerVisible=true&ImageViewer1%24hdnWidgetSize=&ImageViewer" & _
    "1%24DragResizeExtender_ClientState=&CertificateViewer1%24ScrollPos=&CertificateViewer1%24ScrollPosChange=&CertificateViewer1%24_imgContainer" & _
    "Width=0&CertificateViewer1%24_imgContainerHeight=0&CertificateViewer1%24isImageViewerVisible=true&CertificateViewer1%24hdnWidgetSize=&Certificate" & _
    "Viewer1%24DragResizeExtender_ClientState=&PTAXViewer1%24ScrollPos=&PTAXViewer1%24ScrollPosChange=&PTAXViewer1%24_imgContainerWidth=0&PTAXViewer" & _
    "1%24_imgContainerHeight=0&PTAXViewer1%24isImageViewerVisible=true&PTAXViewer1%24hdnWidgetSize=&PTAXViewer1%24DragResizeExtender_ClientState=&DocList" & _
    "1%24ctl09=&DocList1%24ctl11=&NameList1%24ScrollPos=&NameList1%24ScrollPosChange=&NameList1%24_SortExpression=&NameList1%24ctl03=&NameList" & _
    "1%24ctl05=&DocDetails1%24PageSize=&DocDetails1%24PageIndex=&DocDetails1%24SortExpression=&BasketCtrl1%24ctl01=&BasketCtrl1%24ctl03=&OrderList" & _
    "1%24ctl01=&OrderList1%24ctl03=&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__ASYNCPOST=true&SearchFormEx1%24btnSearch=Search"

     
     
      'strCookie = "ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
     'strCookie = "ASP.NET_SessionId=kruwfk55iwzte355mpxl2u45"
     strCookie = "ASP.NET_SessionId=ybhhpwekdbxyzkad3zmvez45; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
     
        With HTTP
            .Open "GET", "http://162.217.184.82/i2/default.aspx?AspxAutoDetectCookieSupport=1", False
            .setRequestHeader "User-Agent", "Mozilla/5.0"
            .setRequestHeader "Cookie", GetCookie("http://162.217.184.82/i2/default.aspx")
            .send ArgumentStr
            HTML.body.innerHTML = .responseText
        End With
     
        For Each elems In [{"DataGridRow","DataGridAlternatingRow"}]
            For Each posts In HTML.getElementsByClassName(elems)
                F = False
                For Each elem In posts.getElementsByTagName("a")
                    F = True: c = c + 1: Cells(r + 1, c) = elem.innerText
                Next elem
                If F Then c = 0: r = r + 1
            Next posts
        Next elems
       
    '    With Range("A1").CurrentRegion
    '        .Sort Range("A1"), xlDescending
    '    End With
    End Sub


      Function GetCookie(strUrl)
      Dim strCookie
       
        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", strUrl, False
            .setRequestHeader "REFERER", strUrl
            .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
            .setRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
            .setRequestHeader "Accept-Language", "en-us,en;q=0.5"
            .setRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
            .send
            strCookie = .getResponseHeader("Set-Cookie")
            strCookie = Split(strCookie, ";")(0)
            GetCookie = strCookie
        End With
    End Function

    Sub Demo()
    Debug.Print GetCookie("http://162.217.184.82/i2/default.aspx")
    End Sub
  15. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Here's a working code
    Code (vb):
    Option Explicit

    Sub Web_Data()
        Dim HTTP As New ServerXMLHTTP60, HTML As New HTMLDocument
        Dim posts As Object, elems As Variant, elem As Object
        Dim postdata As String, F As Boolean
    Dim r As Long
    Dim c As Long
    Dim n1, n2, n3, n4, n5

    'n1 = "16"
    'n2 = "17"
    'n3 = "114"
    'n4 = "027"
    'n5 = "0000"
    '
    n1 = "31"
    n2 = "01"
    n3 = "209"
    n4 = "005"
    n5 = "0000"

        postdata = _
        "ScriptManager1=SearchFormEx1%24UpdatePanel%7CSearchFormEx1%24btnSearch&ScriptManager1_HiddenField=%3B%3BAjaxControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3Aeffe2a26%3B%3BAjaxControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6" & _
        "d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3A475a4ef5%3A5546a2b%3A497ef277%3Aa43b07eb%3Ad2e10b12%3A37e2e5c9%3A5a682656%3A1d3ed089%3Af9029856%3Ad1a1d569&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=&Navigator1%24SearchOptions1%24DocImagesCheck=on&SearchFormEx1%24PINTextBox0=" & n1 & "&SearchFormEx1%24PINTextBox1=" & n2 & "&SearchFormEx1%24PINTextBox2=" & n3 & "&SearchFormEx1%24PINT" & _
        "extBox3=" & n4 & "&SearchFormEx1%24PINTextBox4=" & n5 & "&SearchFormEx1%24ACSTextBox_Subdivision=&SearchFormEx1%24ACSTextBox_BlockNo=&SearchFormEx1%24ACSTextBox_LotNo=&SearchFormEx1%24ACSTextBox_PartOfLotNo=&SearchFormEx1%24ACSTextBox_DeclarationCondoNo=&SearchFormEx1%24ACSTextBox_BuildingNo=&SearchFormEx1%24ACSTextBox_UnitNo=&SearchFormEx1%24ACSTextBox_AcresNo=&SearchFormEx1%24ACSTextBox_Quarter" & _
        "3=&SearchFormEx1%24ACSTextBox_Quarter2=&SearchFormEx1%24ACSTextBox_Quarter1=&SearchFormEx1%24ACSTextBox_Part1Code=&SearchFormEx1%24ACSTextBox_Part2Code=&SearchFormEx1%24ACSTextBox_OneHalfCode=&ImageViewer1%24ScrollPos=&ImageViewer1%24ScrollPosChange=&ImageViewer1%24_imgContainerWidth=0&ImageViewer1%24_imgContainerHeight=0&ImageViewer1%24isImageViewerVisible=true&ImageViewer1%24hdnWidge" & _
        "tSize=&ImageViewer1%24DragResizeExtender_ClientState=&CertificateViewer1%24ScrollPos=&CertificateViewer1%24ScrollPosChange=&CertificateViewer1%24_imgContainerWidth=0&CertificateViewer1%24_imgContainerHeight=0&CertificateViewer1%24isImageViewerVisible=true&CertificateViewer1%24hdnWidgetSize=&CertificateViewer1%24DragResizeExtender_ClientState=&PTAXViewer1%24ScrollPos=&PTAXViewer1%24Scrol" & _
        "lPosChange=&PTAXViewer1%24_imgContainerWidth=0&PTAXViewer1%24_imgContainerHeight=0&PTAXViewer1%24isImageViewerVisible=true&PTAXViewer1%24hdnWidgetSize=&PTAXViewer1%24DragResizeExtender_ClientState=&DocList1%24ctl09=&DocList1%24ctl11=&NameList1%24ScrollPos=&NameList1%24ScrollPosChange=&NameList1%24_SortExpression=&NameList1%24ctl03=&NameList1%24ctl05=&DocDetails1%24PageSize=&DocDetails" & _
        "1%24PageIndex=&DocDetails1%24SortExpression=&BasketCtrl1%24ctl01=&BasketCtrl1%24ctl03=&OrderList1%24ctl01=&OrderList1%24ctl03=&__ASYNCPOST=true&SearchFormEx1%24btnSearch=Search"

    '    postdata = _
    '    "ScriptManager1=SearchFormEx1%24UpdatePanel%7CSearchFormEx1%24btnSearch&ScriptManager1_HiddenField=%3B%3BAjaxControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3Aeffe2a26%3B%3BAjaxControlToolkit%2C%20Version%3D3.5.40412.0%2C%20Culture%3Dneutral%2C%20PublicKeyToken%3D28f01b0e84b6" & _
    '    "d53e%3Aen-US%3A1547e793-5b7e-48fe-8490-03a375b13a33%3A475a4ef5%3A5546a2b%3A497ef277%3Aa43b07eb%3Ad2e10b12%3A37e2e5c9%3A5a682656%3A1d3ed089%3Af9029856%3Ad1a1d569&__EVENTTARGET=&__EVENTARGUMENT=&__LASTFOCUS=&__VIEWSTATE=&Navigator1%24SearchOptions1%24DocImagesCheck=on&SearchFormEx1%24PINTextBox0=16&SearchFormEx1%24PINTextBox1=17&SearchFormEx1%24PINTextBox2=114&SearchFormEx1%24PINT" & _
    '    "extBox3=027&SearchFormEx1%24PINTextBox4=0000&SearchFormEx1%24ACSTextBox_Subdivision=&SearchFormEx1%24ACSTextBox_BlockNo=&SearchFormEx1%24ACSTextBox_LotNo=&SearchFormEx1%24ACSTextBox_PartOfLotNo=&SearchFormEx1%24ACSTextBox_DeclarationCondoNo=&SearchFormEx1%24ACSTextBox_BuildingNo=&SearchFormEx1%24ACSTextBox_UnitNo=&SearchFormEx1%24ACSTextBox_AcresNo=&SearchFormEx1%24ACSTextBox_Quarter" & _
    '    "3=&SearchFormEx1%24ACSTextBox_Quarter2=&SearchFormEx1%24ACSTextBox_Quarter1=&SearchFormEx1%24ACSTextBox_Part1Code=&SearchFormEx1%24ACSTextBox_Part2Code=&SearchFormEx1%24ACSTextBox_OneHalfCode=&ImageViewer1%24ScrollPos=&ImageViewer1%24ScrollPosChange=&ImageViewer1%24_imgContainerWidth=0&ImageViewer1%24_imgContainerHeight=0&ImageViewer1%24isImageViewerVisible=true&ImageViewer1%24hdnWidge" & _
    '    "tSize=&ImageViewer1%24DragResizeExtender_ClientState=&CertificateViewer1%24ScrollPos=&CertificateViewer1%24ScrollPosChange=&CertificateViewer1%24_imgContainerWidth=0&CertificateViewer1%24_imgContainerHeight=0&CertificateViewer1%24isImageViewerVisible=true&CertificateViewer1%24hdnWidgetSize=&CertificateViewer1%24DragResizeExtender_ClientState=&PTAXViewer1%24ScrollPos=&PTAXViewer1%24Scrol" & _
    '    "lPosChange=&PTAXViewer1%24_imgContainerWidth=0&PTAXViewer1%24_imgContainerHeight=0&PTAXViewer1%24isImageViewerVisible=true&PTAXViewer1%24hdnWidgetSize=&PTAXViewer1%24DragResizeExtender_ClientState=&DocList1%24ctl09=&DocList1%24ctl11=&NameList1%24ScrollPos=&NameList1%24ScrollPosChange=&NameList1%24_SortExpression=&NameList1%24ctl03=&NameList1%24ctl05=&DocDetails1%24PageSize=&DocDetails" & _
    '    "1%24PageIndex=&DocDetails1%24SortExpression=&BasketCtrl1%24ctl01=&BasketCtrl1%24ctl03=&OrderList1%24ctl01=&OrderList1%24ctl03=&__ASYNCPOST=true&SearchFormEx1%24btnSearch=Search"
       
        With HTTP
            .Open "POST", "http://162.217.184.82/i2/default.aspx?AspxAutoDetectCookieSupport=1", False
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
            .setRequestHeader "User-Agent", "Mozilla/5.0"
            '.setRequestHeader "Cookie", "AspxAutoDetectCookieSupport=1; BIGipServerpool_rod_public_website=1376261292.20480.0000; ASP.NET_SessionId=cxdtice3jvihww555vx4yk55"
         
            .setRequestHeader "Cookie", "ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
            .setRequestHeader "X-Requested-With", "XMLHttpRequest"
            .send postdata
            HTML.body.innerHTML = .responseText
        End With

        For Each elems In [{"DataGridRow","DataGridAlternatingRow"}]
            For Each posts In HTML.getElementsByClassName(elems)
                F = False
                For Each elem In posts.getElementsByTagName("a")
                    F = True: c = c + 1: Cells(r + 1, c) = elem.innerText
                Next elem
                If F Then c = 0: r = r + 1
            Next posts
        Next elems
    End Sub

     
    It is working well but if I commented out that line it will not work
    Code (vb):
    .setRequestHeader "Cookie", "ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
    At the same time this cookie is temporary and will be invalid after a period of time
  16. shahin

    shahin Active Member

    Messages:
    899
    When you are done creating a scraper using xmlhttp or serverxmlhttp or winhttp request, just compare the execution time between the one you are after and the one I'm pasting below:
    Code (vb):

    Sub Get_Info()
        Dim IE As New InternetExplorer, HTML As HTMLDocument
        Dim posts As Variant, post As Object, elem As Object
        Dim tablerow As Variant, elems As Variant, F As Boolean
        Dim maxpage As Object, loaditem As Object

        With IE
            .Visible = False
            .navigate "http://162.217.184.82/i2/default.aspx"
            While .Busy = True Or .readyState < 4: DoEvents: Wend
            Set HTML = .document
        End With
       
        HTML.getElementById("SearchFormEx1_PINTextBox0").innerText = "31"
        HTML.getElementById("SearchFormEx1_PINTextBox1").innerText = "01"
        HTML.getElementById("SearchFormEx1_PINTextBox2").innerText = "209"
        HTML.getElementById("SearchFormEx1_PINTextBox3").innerText = "005"
        HTML.getElementById("SearchFormEx1_PINTextBox4").innerText = "0000"
        HTML.getElementById("SearchFormEx1_btnSearch").Click
       
        Do: Set maxpage = HTML.getElementById("DocList1_PageView100Btn"): DoEvents: Loop While maxpage Is Nothing
        maxpage.Click
       
        Do: Set loaditem = HTML.getElementsByClassName("DataGridAlternatingRow"): DoEvents: Loop While loaditem Is Nothing
       
        For Each elems In [{"DataGridRow","DataGridAlternatingRow"}]
            For Each posts In HTML.getElementsByClassName(elems)
                F = False
                For Each elem In posts.getElementsByTagName("a")
                    F = True: c = c + 1: Cells(R + 1, c) = elem.innerText
                Next elem
                If F Then c = 0: R = R + 1
            Next posts
        Next elems
        IE.Quit
    End Sub
     
    I'm getting 66 rows filled in. I suppose that much data are there. It took me less than 5 seconds to get the data. Hope it makes sense.
    YasserKhalil likes this.
  17. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Thank you very much Mr. Shahin for your great and awesome help
    The problem with that code is that I don't get all the required data .. try this number 16-17-114-027-0000
    I tested your code and got only 20 records while they are 26

    ** As for the code in post #15 working very well .. but the problem will be with that line
    Code (vb):
    .setRequestHeader "Cookie", "ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
    as the session id will be changeable after some time
  18. shahin

    shahin Active Member

    Messages:
    899
    I ran using the number "16-17-114-027-0000" you have provided above and got 26 rows filled in with results. Just need to replace this portion with the existing one:
    Code (vb):

        Do: Set maxpage = HTML.getElementById("DocList1_PageView5Btn"): DoEvents: Loop While maxpage Is Nothing
        maxpage.Click
     
    YasserKhalil likes this.
  19. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    The same problem .. I got only 2 records not the 26 records ...
    Is it working well for you? If so what 's my problem? IE version or what?
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    So that's the portion where you will use the function I gave in post #2 to scrape fresh cookie and use that to construct the header that you need to.

    Other than that, the site is pretty straight forward to scrape.
    YasserKhalil likes this.
  21. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !
    When a request is not at your level, when you waste too much time,
    easier is to pilot IE or use a scrapping tool …
  22. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    I already tried to do that .. I have used the UDF and got the cookie and used it in the working code but I got nothing .. but when manaully do these steps to get the cookie as in the first post it is working well .. at this point the only problem is to get the cookie correctly


    @MarcL
    Thanks for the advice .. But I like to learn even if the issue was not at my level .. my slogan is to try and fail even if I failed a thousand times ..
    As for using IE Mr. Shahin already offered a good working one .. but the problem is that it grabs only one page not all the pages .. while it is working for him and grabs all the records (that's weird ..!!)

    Thanks a lot for sharing the issue. I appreciate a lot your great efforts
  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    You may want to build in error checks. But to get cookie string in the form you can use... change code in post #2 to something like below.
    Code (vb):
    Function GetCookie(strUrl)
        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", strUrl, False
            .SetRequestHeader "REFERER", strUrl
            .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
            .SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
            .SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
            .SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
            .Send
            strCookie = .getAllResponseHeaders
            strCookie = Split(strCookie, vbCrLf)
            GetCookie = Trim(Split(Split(strCookie(5), ";")(0), ":")(1)) & "; " & Trim(Split(Split(strCookie(6), ";")(0), ":")(1))
        End With
    End Function

    Sub Demo()
    Debug.Print GetCookie("http://162.217.184.82/i2/default.aspx") & "; PageSize=100"
    End Sub
     
    This will return...
    Code (vb):
    ASP.NET_SessionId=rem4za45hzasud45xntglnau; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100
    shahin, shrivallabha and YasserKhalil like this.
  24. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Mr. Champion
    Let me lift hats up for you .. You are brilliant
    That's exactly what I was searching for and requesting in my first post
    And that is worked very very well with the code in post # 15

    I just replaced that line
    Code (vb):
    .setRequestHeader "Cookie", "ASP.NET_SessionId=sgh24ey5hdnhmg454gk4fsjb; BIGipServerpool_rod_public_website=1376261292.20480.0000; PageSize=100"
    With that magic line
    Code (vb):
    .setRequestHeader "Cookie", GetCookie("http://162.217.184.82/i2/default.aspx") & "; PageSize=100"
    Now it is PERFECT

    Thank you very very very much for this awesome solution
  25. shahin

    shahin Active Member

    Messages:
    899
    The way you have shown to play with Cookies is awesome @sir Chihiro. I've tried to follow your method to give a twitch in order to get all the Cookies separately but can't go forward.

    Code (vb):

    Sub GetCookie()
        Dim strCookie As Variant

        With CreateObject("WinHttp.WinHttpRequest.5.1")
            .Open "GET", "http://162.217.184.82/i2/default.aspx", False
            .setRequestHeader "User-Agent", "Mozilla/5.0"
            .send
            strCookie = .getAllResponseHeaders
            strCookie = Split(strCookie, "Cookie:")
            strCookie = Trim(strCookie(UBound(strCookie)))
            MsgBox strCookie
        End With
    End Sub
     
    However, with the above approach I can get the one big raw cookie string. Now, I wish to disassemble each of the portion from ";" and separate each of the cookies so that I can use multiple cookies when in need. Thanks in advance.
    Last edited: Feb 8, 2018

Share This Page