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;"> </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:">",prevMonthText:"<",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
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;"> </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:">",prevMonthText:"<",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