Consulting

Results 1 to 3 of 3

Thread: Get table values from HTML IE website custom generated table

  1. #1
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    2
    Location

    Get table values from HTML IE website custom generated table

    Hi,

    I can't find a way how to get needed values from IE website. The website itself is intranet so not accessable outside. The code i got so far is:


    Sub WebIE()
    
    
        Set IE = New InternetExplorerMedium
        IE.Visible = True
        IE.navigate ("link to the website")
    
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
    
        IE.Document.getElementById("txtUser").Value = "user"
        IE.Document.getElementById("txtPassword").Value = "password"
        IE.Document.getElementById("btnLogin").Click
      
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
      
       IE.navigate ("link to the website/table")
    
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
    
     IE.Document.All("_ctl9:grd11639:text").Value = "Value1"                        
     IE.Document.All.Item("_ctl9:grd11639:text").FireEvent ("onchange")
     
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
    
     IE.Document.All("_ctl9:grd11458").Value = "Value 2"                                             
     IE.Document.All("_ctl9:grd11867").Value = "Value 3"                                                
     
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
     
     IE.Document.All("_ctl9_grd11637").Click                                                        'Apply button
     
    Do While IE.Busy Or IE.ReadyState <> 4: Application.Wait (Now + TimeValue("00:00:01")): Loop
    
    
    'missing part to fetch the needed data
    
    
     Set IE = Nothing
    End Sub
    There;s a lot of HTML coding in that site, but the field i need is in table id "formTable" the code for the field is:

    HTML Code:
    <span id="_ctl21__ctl4__ctl19_grd11489" class="datePicker required" controluniqueid="28a91534-4f8a-43f9-9ec7-19190911d68e" onclick="ScrollPos();return true;" style="display:inline-block;width:100%;"><!-- Version 1.3.5.0. Copyright Coolite Inc. 2004-2017. All rights reserved. Please visit  for more information. --><span style="display:none !important;">&nbsp;</span><input name="_ctl21:_ctl4:_ctl19_grd11489:textBox" type="text" value="13.11.2017" id="_ctl21__ctl4__ctl19_grd11489_textBox" onfocus="basicDatePicker.ehFocus(this);" onblur="basicDatePicker.ehBlur(document.getElementById('_ctl21__ctl4__ctl19_grd11489_image'), this, _ctl21__ctl4__ctl19_grd11489Params);" onkeydown="basicDatePicker.keyPress(event,this,'_ctl21__ctl4__ctl19_grd11489');"><img id="_ctl21__ctl4__ctl19_grd11489_image" class="bdpButton" alt="" onclick="if(window.basicDatePickerButtonElementClick){basicDatePickerButtonElementClick(this, document.getElementById('_ctl21__ctl4__ctl19_grd11489_textBox'), null, _ctl21__ctl4__ctl19_grd11489Params, true)};" src="/TIP/Images/Icons/BasicDatePicker/calendar.png" border="0" style="height:15px;width:16px;"><script type="text/javascript"><!--var _ctl21__ctl4__ctl19_grd11489Params = {enabled:true,autoPostBack:false,selectableWeekDays:true,selectableWeekendDays:true,selectablePrevMonthDays:true,selectableNextMonthDays:true,maximumDate:new Date(9999,11,31,0,0,0),minimumDate:null,twoDigitYearBreak:50,openCalendarOnTextBoxFocus:false,monthSelectorEnabled:true,yearSelectorEnabled:true,titleFormat:"MMMM yyyy",rows:1,columns:1,dayStatusBarText:"{0}",nullDateText:"",xOffset:0,yOffset:-2,nextPrevMonthImageWidth:"5px",nextPrevMonthImageHeight:"9px",nextMonthText:"&gt;",prevMonthText:"&lt;",showWeekNumbers:false,showDaysInNextMonth:true,showDaysInPrevMonth:true,showTodayButton:true,todayButtonText:"Today",showNoneButton:false,noneButtonText:"None",footNoteText:"",forceSixRows:true,firstDayOfWeek:7,dayNameFormat:"FirstLetter",displayType:"TextBoxAndImage",buttonText:"Calendar",nextPrevFormat:"Image",showDayHeader:true,showNextPrevMonth:true,showTitle:true,downYearSelectorImageFileName:"arrow_down.gif",upDownYearSelectorImageWidth:9,upDownYearSelectorImageHeight:5,upYearSelectorText:"+",downYearSelectorText:"-",upDownYearSelectorFormat:"Image",monthSelectorXOffset:-11,monthSelectorYOffset:2,yearSelectorXOffset:-11,yearSelectorYOffset:2,nullDate:null,visibleDate:null,dateOrder:"dmy",onClientDayRender:"",onClientPreParse:"DatePicker_OnClientPreParsebfa06d03a5a144e29c0a73fee1ef5b2c",onClientCalculateWeekNumber:"",onClientBeforeCalendarOpen:"",onClientBeforeCalendarClose:"",onClientAfterCalendarOpen:"",onClientBeforeSelectionChanged:"",onClientAfterSelectionChanged:"DatePicker_OnClientAfterSelectionChanged11489",onClientBeforeVisibleMonthChanged:"",onClientAfterVisibleMonthChanged:"",popUpStyle:"",popUpStyleCssClass:"bdpPopUp",calendarStyle:"",calendarStyleCssClass:"bdpCalendar",noneButtonStyle:"",noneButtonStyleCssClass:"bdpClearButton",todayButtonStyle:"",todayButtonStyleCssClass:"bdpTodayButton",titleStyle:"",titleStyleCssClass:"bdpTitle",nextPrevStyle:"",nextPrevStyleCssClass:"bdpNextPrev",dayHeaderStyle:"",dayHeaderStyleCssClass:"bdpDayHeader",dayStyle:"",dayStyleCssClass:"bdpDay",otherMonthDayStyle:"",otherMonthDayStyleCssClass:"bdpOtherMonthDay bdpDay",weekendDayStyle:"",weekendDayStyleCssClass:"bdpWeekendDay bdpDay",selectedDayStyle:"",selectedDayStyleCssClass:"bdpSelectedDay selectedDay bdpDay",todayDayStyle:"",todayDayStyleCssClass:"bdpTodayDay bdpDay",footerStyle:"",footerStyleCssClass:"bdpFooter",footNoteStyle:"",footNoteStyleCssClass:"bdpFootNote",weekNumberStyle:"",weekNumberStyleCssClass:"bdpWeekNumber",otherMonthDayWeekendDayStyle:"",otherMonthDayWeekendDayStyleCssClass:"bdpOtherMonthDay bdpDay bdpWeekendDay bdpDay bdpDay",selectedDayTodayDayStyle:"",selectedDayTodayDayStyleCssClass:"bdpTodayDay bdpDay bdpSelectedDay selectedDay bdpDay bdpDay",selectedDayWeekendDayStyle:"",selectedDayWeekendDayStyleCssClass:"bdpSelectedDay selectedDay bdpDay bdpWeekendDay bdpDay bdpDay",todayDayWeekendDayStyle:"",todayDayWeekendDayStyleCssClass:"bdpTodayDay bdpDay bdpWeekendDay bdpDay bdpDay",timePickerID:"",postBackFunction:"__doPostBack('_ctl21$_ctl4$_ctl19_grd11489','')",dateFormat:"dd.MM.yyyy",culture:"BDPCulture_de_DE",nextMonthImageUrl:"/TIP/Images/Icons/BasicDatePicker/arrow_right.gif",prevMonthImageUrl:"/TIP/Images/Icons/BasicDatePicker/arrow_left.gif",upYearSelectorImageUrl:"/TIP/Images/Icons/BasicDatePicker/arrow_up.gif",downYearSelectorImageUrl:"/TIP/Images/Icons/BasicDatePicker/arrow_down.gif",specialDates:"",clientID:"_ctl21__ctl4__ctl19_grd11489"};//--></script></span>
    as this is custom generated table by the filters set earlier the "ctl11" part changes on the results. The items in the column could variate from 0 to 100. I have tryed a lot of different ways to get the needed data. I'm lost here. It would be greate if someone could help me with this issue. I was able to get the value of one cell with this line:

    IE.Document.All("_ctl21:_ctl12:_ctl19_grd11489:textBox").Value
    But i need to somehow to loop through all the values. and i'm out of ideas.

    Would appreciate any help.

    Thank you,
    Ignas
    Last edited by Ignas; 11-10-2017 at 12:56 AM.

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    585
    Location
    You could try a totally different approach by using the Microsoft XMLhttp object instead of IE.
    This is some code I wrote to help another person on the forum. Modify the url,meta and result to match you own requirements and see where it takes you.



    Sub test()     Worksheets("sheet1").Select 
        Range(Cells(1, 1), Cells(12000, 10)) = "" 
        meta = "<meta itemprop" 
        result = "result_pk" 
        Dim str As String 
        indi = 2 
        Dim inarr As Variant 
        Dim outarr As Variant 
        Set FinHTTP = CreateObject("Microsoft.xmlHTTP") 
        Dim FinRows, FinCols 
        urlstring = "https://www.tipico.com/en/online-sports-betting/football/spain/la-liga/g36301/" 
        FinHTTP.Open "GET", urlstring, False 
        FinHTTP.send 
         
        textmess = FinHTTP.responseText 
         'MsgBox textmess
        FinRows = Split(FinHTTP.responseText, Chr(10)) 
        For j = 0 To UBound(FinRows) - 1 
             
             '  Cells(j + 1, 1) = FinRows(j)
            str = FinRows(j) 
             
            If InStr(str, meta) > 0 Or InStr(str, result) > 0 Then 
                Cells(indi, 1) = FinRows(j) 
                indi = indi + 1 
            End If 
        Next j 
         
         
    End Sub

  3. #3
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    2
    Location
    Thank you, offhelip, for the guidance. I will try such approach in the near time and will surely update on the results. Aslo, I was tinkering with the script and have got some results - it's not perfect but it looks like it works, but the process i think isn't realy correct and the code is overburdened.

    Dim i As Integer
    Dim y As Integer
    i = 1
    y = 1
    For Each Item In ie.Document.getElementsByTagName("INPUT")
    If Item.Value <> "" And Item.Type = "text" And Item.id Like "*grd11489*" Then
    Sheets("Sheet1").Range("A" & y).Value = Item.Value
    Sheets("Sheet1").Range("B" & y).Value = Item.id
    ' If Len(Item.id) = 36 Then MsgBox Left(Item.id, 20) 'this is still commented as i though i could loop through one column and get other columns results by using part of the first ID - so still working on this.
    ' If Len(Item.id) = 37 Then MsgBox Left(Item.id, 21)
    y = y + 1
    
    
    Else
    ' dont do anything
    End If
    i = i + 1
    Next

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •