Using VBA to select drop-down values on a website

Question

I am trying to create a macro which pulls a report from a website and this has four drop-down lists to select values. I am able to login to the page and direct myself to the report page, but for some reason am having trouble with the drop-down lists. I have tried several 'solutions' which were provided online, but I keep getting an error message:

Run-time error '438': Object doesn't support this property or method.

Here is one of the drop-downs to select from:

<select name="LocationID">
<option value="0" selected="">All Location</option>
<option value="9">Atlanta</option>
<option value="7">Denver</option>
<option value="3">Las Vegas</option>
<option value="1">Los Angeles</option>
<option value="4">Miami</option>
<option value="6">New Jersey</option>
<option value="10">Phoenix</option>
<option value="2">San Francisco</option>
<option value="8">Seattle</option>
<option value="11">Vancouver</option>
</select>

And here is what I currently have in VBA:

Option Explicit
Const MyUserID As String = "test123"
Const MyPassword As String = "test123"
Const READYSTATE_COMPLETE As Integer = 4
Dim objIE As Object


Public Sub LoginScript()

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.Silent = True
.navigate ("https://wwww.mywebsite.com")
Do Until .readyState = READYSTATE_COMPLETE
DoEvents
Loop
Application.Wait Now() + TimeValue("00:00:02")
.document.all.txtuserid.Value = MyUserID
.document.all.txtPassword.Value = MyPassword
objIE.document.getElementsByName("btnSubmit")(0).Click
Do Until .readyState = READYSTATE_COMPLETE
DoEvents
Loop
Application.Wait Now() + TimeValue("00:00:02")
.navigate("https://www.mywebsite.com/sample.html")
Do Until .readyState = READYSTATE_COMPLETE
DoEvents
Loop
.document.getElementByName("LocationID").Value = "7"
objIE.document.getElementsByName("view")(0).Click
End With
End Sub

With the given example above I receive the following message: Run-time error '438': Object doesn't support this property or method. In debug it highlights this line: .document.getElementByName("LocationID").Value = "7" — Francis, Feb 07 '18 at 16:41
It should be `getElementsByName` (plural) - You have it correct on the other lines, but not that one. — braX, Feb 07 '18 at 16:45

K.Dᴀᴠɪs · Accepted Answer · 2018-02-07T16:50:36.463

0

I assume you are getting your error message on this line?..

.document.getElementByName("LocationID").Value = "7"

That would be because the Name is an element collection. Unlike ID (getElementByID), element is plural on collections.

The proper syntax in this case would be:

.document.getElementsByName("LocationID")(0).Value = "7"

Notice the appended (0) as well? This is because again it's a collection, so you will also need to select the collection item as well. (may not necessarily be (0), but I cannot tell you for sure without looking at the entire HTML code.)

edited Feb 07 '18 at 16:50

answered Feb 07 '18 at 16:46

K.Dᴀᴠɪs

9,945
11
33
43

Amazing. This did the job! Thanks heaps, K.Davis :) I assume for the other drop-downs, I just add the additional lines as reflect in your post, correct? – Francis Feb 07 '18 at 16:49
@Francis Sorry I don't follow. If you are using anything other than `getElementByID()`, make sure that **elements** is plural and that you append the collection item (ex: `(0)`) to the end of the element unless you want to loop through them. – K.Dᴀᴠɪs Feb 07 '18 at 16:55
@K.Davis It's all good. It works. Sorry, if I wasn't clear. I just quickly asked whether .document.getElementsByName("LocationID")(0).Value = "7" can be then applied for the additional drop-downs, of course with the correct names, and yes, it works. Again, thanks for taking the time looking into this. – Francis Feb 07 '18 at 16:57
@Francis It's difficult to say with 100% confidence without seeing the HTML code, but it should work if the element has a `Name` property. Not all elements have this property, just as not all elements have an ID. But if it has the name property then you shouldn't have any issues. – K.Dᴀᴠɪs Feb 07 '18 at 17:00

score 0 · Answer 2 · answered Feb 10 '18 at 15:23

It should be something like this...

Sub passValueToComboBox1()
   Dim ie  As Object
   Dim oHTML_Element As IHTMLElement

   Set ie = CreateObject("InternetExplorer.Application")
   ie.Visible = True
   ie.navigate "http://www.your_web_site.com"
   While ie.Busy Or ie.readyState <> 4: DoEvents: Wend

   Set oHTML_Element = ie.document.getElementsByName("selectedReportClass")(0)
   If Not oHTML_Element Is Nothing Then oHTML_Element.Value = "com.db.moap.report.FUBU"

   For Each oHTML_Element In ie.document.getElementsByTagName("input")
      If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
   Next
End Sub

Using VBA to select drop-down values on a website

2 Answers2