PDA

View Full Version : Get table values from HTML IE website custom generated table



Ignas
11-10-2017, 12:23 AM
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:


<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_grd1148 9_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){basicDatePickerButtonElementCl ick(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,selectableWeekendD ays:true,selectablePrevMonthDays:true,selectableNextMonthDays:true,maximumD ate:new Date(9999,11,31,0,0,0),minimumDate:null,twoDigitYearBreak:50,openCalendarOn TextBoxFocus:false,monthSelectorEnabled:true,yearSelectorEnabled:true,title Format:"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,sh owTodayButton: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,downYearSelectorI mageFileName:"arrow_down.gif",upDownYearSelectorImageWidth:9,upDownYearSelectorImageHeight:5,upYearSelec torText:"+",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

offthelip
11-12-2017, 02:44 AM
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

Ignas
11-12-2017, 11:16 PM
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