VBA code to select an option from HTML drop down, using value rather than index

devendra.dvm · Mar 10, 2018

Hi experts please help with the code..

Iam working on a project ''Automation IE'' but got stucked at selecting dropdown list ..
I want to select a option in a dropdown list using value.. i know the code for index selection but i want to select with value only as dropdown list is dynamic, list get increase month on month so that user will give input for the same...

this is my VBA code

Code:

Sub KBOSS_WarrantDetails()

Set Browser = CreateObject("internetexplorer.application")
Browser.navigate ("website")



Do
DoEvents
Loop Until Browser.ReadyState = 4

Browser.Document.getElementByID("txtUserId").Value = "xxxxxxx"
Browser.Document.getElementByID("txtPassword").Value = “xxxxxx”
Browser.Document.getElementByID("selFund").selectedIndex = "26"
Browser.Document.getElementByID("btnSubmit").Click

Do
DoEvents
Loop Until Browser.ReadyState <> 4


Do
DoEvents
Loop Until Browser.ReadyState = 4

Browser.Document.getElementByID("chkquery").Checked = True
Browser.Document.getElementByID("btnSubmit").Click

Do
DoEvents
Loop Until Browser.ReadyState <> 4

Browser.navigate ("website – next page")


Do
DoEvents
Loop Until Browser.ReadyState <> 4

Browser.getElementByID("ctl00_ContentPlaceHolder1_ddlCycleID").Value = cycleid

Do
DoEvents
Loop Until Browser.ReadyState = 4


Browser.Document.getElementByID("ctl00_ContentPlaceHolder1_txtAgCode").Focus
Browser.Document.getElementByID("ctl00_ContentPlaceHolder1_txtAgCode").Value = “xxxx”

Do
DoEvents
Loop Until Browser.ReadyState <> 4


Application.Wait (Now + TimeValue("0:00:05"))



Do
DoEvents
Loop Until Browser.ReadyState = 4

Browser.Document.getElementByID("ctl00_ContentPlaceHolder1_btnView").Focus
Browser.Document.getElementByID("ctl00_ContentPlaceHolder1_btnView").Click


End Sub

Below is the HTML Source code for the same

Code:

<select name="ctl00$ContentPlaceHolder1$ddlCycleID" id="ctl00_ContentPlaceHolder1_ddlCycleID" class="SelectCls" style="width:70%;">


  <option value="">Select</option>
  <option value="0">All</option>
  <option selected="selected" value="294">Cycle 294 - Feb -2018</option>
  <option value="18">Cycle 18 - Nov -2010</option>
  <option value="17">Cycle 17 - Nov -2010</option>
  <option value="16">Cycle 16 - Oct -2010</option>
  <option value="15">Cycle 15 - Oct -2010</option>
  <option value="14">Cycle 14 - Sep -2010</option>
  <option value="13">Cycle 13 - Sep -2010</option>
  <option value="12">Cycle 12 - Aug -2010</option>
  <option value="11">Cycle 11 - Aug -2010</option>
  <option value="10">Cycle 10 - Jul -2010</option>
  <option value="9">Cycle 9 - Jul -2010</option>
  <option value="8">Cycle 8 - Jun -2010</option>
  <option value="7">Cycle 7 - Jun -2010</option>
  <option value="6">Cycle 6 - Jun -2010</option>
  <option value="5">Cycle 5 - May -2010</option>
  <option value="4">Cycle 4 - May -2010</option>
  <option value="3">Cycle 3 - Apr -2010</option>
  <option value="2">Cycle 2 - Apr -2010</option>
  <option value="1">Cycle 1 - Mar -2010</option>

</select>

shahin · Mar 11, 2018

Try this. It should fix the issue.

Code:

Sub Select_Item()
    Dim post As Object, elem As Object

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "C:\Users\WCS\Desktop\element.html"  ''stored the html elements within "element.html" file to test it locally
        While .Busy = True Or .ReadyState < 4: DoEvents: Wend

        Set post = .Document.getElementById("ctl00_ContentPlaceHolder1_ddlCycleID")
        ''I din't use any index to do the job
        For Each elem In post.getElementsByTagName("option")
            If InStr(elem.Value, "10") > 0 Then elem.Selected = True: Exit For
        Next elem
    End With

End Sub

If the webpage you are trying with is a slow-loading one then make sure to put some delay so that the content of that page get loaded completely.

YasserKhalil · Mar 11, 2018

Hello my friend
Can you attach the element.html so as to learn from you ..?
Regards

shahin · Mar 11, 2018

Any file with ".html" extension is not allowed to be uploaded in this forum. Just copy the above elements in a text file and change the extension (using save as) to ".html". That's it.

devendra.dvm · Mar 12, 2018

shahin said:

Try this. It should fix the issue.

Code:

Sub Select_Item()
    Dim post As Object, elem As Object

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .navigate "C:\Users\WCS\Desktop\element.html"  ''stored the html elements within "element.html" file to test it locally
        While .Busy = True Or .ReadyState < 4: DoEvents: Wend

        Set post = .Document.getElementById("ctl00_ContentPlaceHolder1_ddlCycleID")
        ''I din't use any index to do the job
        For Each elem In post.getElementsByTagName("option")
            If InStr(elem.Value, "10") > 0 Then elem.Selected = True: Exit For
        Next elem
    End With

End Sub

If the webpage you are trying with is a slow-loading one then make sure to put some delay so that the content of that page get loaded completely.

Hi Shahin,

Thanks for the reply and your time... I have tried with your code its reached till this page then got error as showed in below images

one more thing i want to mention that in HTML source code which i have mentioned in my 1st post "Options will be dynamic, they will keep increase month on month... And is there any way to get all the options list in combo box so that user will select from the list and based selection next code should run...

Thanks in advance...

Chihiro · Mar 12, 2018

That error message is likely due to IE Integrity Level setting.
Check IE's security setting and if it's not set to Medium Integrity Level (MediumIL) then set to MediumIL.

Have a read of links.
https://blogs.msdn.microsoft.com/ieinternals/2011/08/03/default-integrity-level-and-automation/
https://stackoverflow.com/questions/30086425/excel-vba-method-document-of-object-iwebbrowser2-failed

devendra.dvm · Mar 16, 2018

Hi Chihiro,

Thanks for the reply.. I have gone through your links and found very usefull.
As mentioned in my earlier post is there any way to get all the options list from HTML source code into combo box so that user will select from the list and based selection next code should run.

Im going to share this project with my other team members who doesn't know VBA so i want to provide input options so that they select from combo box.. based on input rest of the code should run.
any help on the same would be much appreciated

Thanks in Advance.

Chihiro · Mar 16, 2018

Hmm? @shahin already gave you base code to loop through the element that house the options. Instead of selecting, put each object's .innerText into cell or into some collection/container and use that.

devendra.dvm · Mar 16, 2018

Hi Chihiro,

yes but as i mentioned option list is dynamic, list is keeps increas, im gooing to thiis with my team they cant add list every time its increase..

Chihiro · Mar 16, 2018

Just scrape option value first. Put it in the sheet or somewhere for user to choose from. Once user makes selection on option value...
Run another code to use that value to control IE.

Note: His sample is just based on the piece of HTML you posted. You can directly query the web site to fill the list using same logic using getElementById.

devendra.dvm · Mar 29, 2018

Hi Chihiro,

Hope your doing well...Thank for your response for my post.. Have tried with Shahins code with small changes and its working fine when user will enter value but still im not reached to my exact solution.. as i mentioned in my earlier post i need a code to get all the HTML dropdown list in combo box so that user will select from combo box and based on selection next code should run

Code:

Sub KBOSS_WarrantDetails()

Dim cycleid As String

cycleid = InputBox("Enter Cycleid Here (Update 0 for option -ALL-) ")

If cycleid = "" Then

MsgBox "No Details entered"

Exit Sub

End If

Set Browser = CreateObject("internetexplorer.application")

    With Browser
        .navigate " https://My website.aspx "
       While .Busy = True Or .ReadyState < 4: DoEvents: Wend

        Set post = .document.getElementById("ctl00_ContentPlaceHolder1_ddlCycleID")
   
       For Each elem In post.getElementsByTagName("option")

'here i make small change in code'
            If elem.Value = cycleid Then elem.Selected = True: Exit For
        Next elem
    End With

Do
DoEvents
Loop Until Browser.ReadyState = 4

Browser.document.getElementById("ctl00_ContentPlaceHolder1_btnView").Focus
Browser.document.getElementById("ctl00_ContentPlaceHolder1_btnView").Click

Browser.Visible = True

End Sub

Chihiro · Mar 29, 2018

You need to do it in two stages.

First, loop through all option and fill listbox, combobox or whatever control you are using in your application (be it userform, data validation etc).

Then in second stage, when user selects option, read the selected value in to the code and use it to specify which option element should have selected property set to true.

devendra.dvm · Apr 4, 2018

Hi Chihiro,

Hope your doing well...Thank for your response.. Please help me with the code for my same project...

Im working based on your idea and its under process.. while doing this im facing difficulty in next page where im trying to automation to spool one excel report from website and there is one generate button and one download button as mentioned in below image.

For Generate button below is the HTML Source code

Code:

 <input type="submit" name="ctl00$ContentPlaceHolder1$btnGenerate" value=" Generate " onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$ContentPlaceHolder1$btnGenerate&quot;, &quot;&quot;, true, &quot;&quot;, &quot;&quot;, false, false))" id="ctl00_ContentPlaceHolder1_btnGenerate" tabindex="4" title="Click Here" class="dxbButton_Glass">

After Generating the report by clicking on generate button one message will appear as shown in below image and then only download button will get highlight.. but here im facing difficulty for building up the code.

Im trying to building a code that after appearing the message only clicking download button code should run.. Thanks in advance.

Below is the HTML source code for message

Code:

<span id="ctl00_ContentPlaceHolder1_lblMsg" style="color:Blue;font-weight:bold;">Report Spooled Successfully</span>

Code:

Sub KBOSS_AUM_Merger_Report()

Set Browser = CreateObject("internetexplorer.application")
Browser.navigate ("web site")


Do
DoEvents
Loop Until Browser.ReadyState = 4

Browser.Document.getElementById("txtUserId").Value = "user"
Browser.Document.getElementById("txtPassword").Value = psswd
Browser.Document.getElementById("btnSubmit").Click

Do
DoEvents
Loop Until Browser.ReadyState <> 4


Browser.Document.getElementById("ctl00_ContentPlaceHolder1_btnGenerate").Focus
Browser.Document.getElementById("ctl00_ContentPlaceHolder1_btnGenerate").Click



Set my_data = Browser.Document.getElementById("ctl00_ContentPlaceHolder1_lblMsg").getElementsByTagName("td")(2).innerText


If my_data.Visible = True Then



Application.Wait (Now + TimeValue("0:00:02"))
Browser.Document.getElementById("ctl00_ContentPlaceHolder1_lnkDownload").Click

End if

End sub

Chihiro · Apr 4, 2018

Try looping using Do loop.

Basically looping until some condition is met (i.e. until .innerText of element equals "Report Spooled Successfully")

Code:

Do
DoEvents
Loop Until SomeElement.innerText = "Text"

devendra.dvm · Apr 10, 2018

New
Hi Chihiro,

have tried to build a code based on your idea but same getting error.. Can you help me w

Chihiro said:
Try looping using Do loop.

Basically looping until some condition is met (i.e. until .innerText of element equals "Report Spooled Successfully")

Code:

Do DoEvents Loop Until SomeElement.innerText = "Text"

VBA code to select an option from HTML drop down, using value rather than index

devendra.dvm

Member

shahin

Active Member

YasserKhalil

Well-Known Member

shahin

Active Member

devendra.dvm

Member

Chihiro

Excel Ninja

devendra.dvm

Member

Chihiro

Excel Ninja

devendra.dvm

Member

Chihiro

Excel Ninja

devendra.dvm

Member

Chihiro

Excel Ninja

devendra.dvm

Member

Chihiro

Excel Ninja

devendra.dvm

Member