VBA/HTML how to: click drop down, enter value, click search

Js Smith

New Member
Joined
Jul 24, 2020
Messages
44
Office Version
  1. 2010
Platform
  1. Windows
Hi all! I'm trying to pull weather for certain locations. I've got the scraping part down. I'm having trouble controlling the location; results are intermittent if I use a hard coded URL or if using cells in the worksheet to complete the URL. Sometimes it if trying for Seattle, WA it ends up in Brisbane, Spain or some other random place. My thought is to use the search function on the website but having trouble hitting the right spot. Closest I've gotten is the Bing search bar at the top of the page.

When I Inspect Element this is what I saw:

HTML:
' THE BUTTON:
<a role="button" href="/en-us/weather/places?ru=/en-us/weather/today">Places</a>

'THE BLANK SPACE TO TYPE A LOCATION:
<input name="q" class="query" accesskey="L" aria-autocomplete="list" aria-label="Search for Location / City" type="search" maxlength="250" placeholder="Search for Location / City" value="" data-m='{"i":56,"p":55,"n":"weaautosuggest","y":10,"o":1}' data-id="56" autocomplete="off" data-autosuggestdelay="100" data-seemoretext="Press <Enter> to see more" data-auto-suggest-settings='{"market":"en-us","mode":"AS","baseurl":"/en-us/weather/today","action":"addfavorite","method":"get","selector":null,"urltype":"Weather","formcode":"PRWLAS","lat":43.6242,"lon":-116.289}'>

' SEARCH BUTTON:
<button title="Search" class="searchbtn" aria-label="search" type="submit" data-m='{"i":57,"p":55,"n":"wealocationsearch","y":11,"o":2}' data-id="57">
<span class="x-screen-reader">Search</span>
           </button>

I've got this bit to open the page and wait working find.

VBA Code:
Sub check()
    Dim oHTML_Element As IHTMLElement
    Dim anchorElement As HTMLAnchorElement
    Dim oIE As InternetExplorer
    Dim IE As Variant
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate "https://www.msn.com/en-us/weather"
    While IE.readyState <> READYSTATE_COMPLETE And IE.readyState <> READYSTATE_LOADED
        DoEvents
    Wend
    Application.Wait (Now() + TimeValue("00:00:03"))

End Sub


I can't for the life of me figure how to interact with the location search parts! I've tried

VBA Code:
oIE.document.getElementsByClassName("query")(0).Click
'and
oIE.document.getElementsByClassName("searchbtn")(0).Click

But get a run time error 91 Object Variable or with block Variable not set. I am an HTML novice and know enough VBA to make me dangerous. I appreciate any help and education you can provide!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I don't know how helpful this will be, as a) I know nothing about navigating the HTML DOM in VBA, and b) have very little experience in what I am about to suggest (as I am just learning), but since there is some degree of input in the webpage you want to automate, I were in your shoes I might look into something like Power Automate Desktop to help navigate through the webpage and then run your macro. It might give you an alternative avenue to try if you cannot figure out the VBA web navigation route.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top