CODEWITHSUNDEEP
htmlvbaexcelweb-scraping
Bill T
Bill T

Reputation: 19

Fill in Input Box on Website with VBA

I've been banging my head against he wall trying to figure out why this VBA code will not work :(

I am simply trying to insert the value entered into the excel input box into a website's input box. I am a novice when it comes to HTML so I'm sure that has something to do with it.

Here is the HTML element from the website Zomato.com:

<input class="dark" id="location_input" role="combobox" aria-expanded="true" aria-labelledby="label_search_location" aria-owns="explore-location-suggest" aria-autocomplete="list" placeholder="Please type a location...">

Here is my VBA code:

    Sub Merchant_Extraction()

Dim IE As Object
Dim form As Variant
Dim button As Variant

Set IE = CreateObject("internetexplorer.application")

merchantzip = InputBox("Enter Zip Code")

With IE

.Visible = True
.navigate ("http://www.zomato.com")

While IE.readystate <> 4
DoEvents
Wend

IE.Document.GetElementByID(“location_input_sp”).Item.innertext = merchantzip

Set form = IE.Document.getelementsbytagname("form")

Set button = form(0).onsubmit
form(0).get

End With

Set IE = Nothing

End Sub

I am unclear why it's not working - any help would be incredible!

Upvotes: 1

Views: 11428

Answers (2)

QHarr
QHarr

Reputation: 84475

API XMLHTTP GET request

The API was mentioned. The documention is here.

The basic free account allows access to restaurant information and search APIs (up to 1000 calls/day).

An example first 0-20 results call, with a city id specified (68 for Manchester,UK), is as follows; a JSON response is received. The response it parsed into a JSON object with JSONConverter.bas

Option Explicit
Public Sub GetInfo()
    Dim URL As String, strJSON As String, json As Object

    URL = "https://developers.zomato.com/api/v2.1/search?entity_id=68&entity_type=city&start=0&count=20"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "user-key", "yourAPIkey"
        .send
        strJSON = .responseText
    End With
    Set json = JsonConverter.ParseJson(strJSON)
    'other stuff with JSON object
End Sub

Example JSON response:

enter image description here

Zomato - Common APIs:

API

Finding your city ID:

The quickest way for me was to go to concatenate the city onto a base URL string e.g. https://www.zomato.com/manchester, then click search and right-click inspect HTML on first result. Then Ctrl+F to bring up search box, search for CITY_ID, and scan through find results for the HTML until city id found e.g.

Upvotes: 1

B Hart
B Hart

Reputation: 1118

As far as entering values into webpages the correct syntax would be:

IE.Document.all.Item("location_input").Value = ""

I've combined your routine with some code that I use so you can see an example. I have not been able to test however. In my environment, the IE object disconnects after the .navigate portion so I added in a loop to find and re-assign the object...

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Sub Merchant_Extraction()
Dim IE As Object, objShellWindows As Object
Dim MerchantZip As String, strWebPath As String
Dim Form As Variant, Button As Variant
Dim X As Long

strWebPath = "http://www.zomato.com"

MerchantZip = InputBox("Enter Zip Code")
If MerchantZip = vbNullString Then Exit Sub

Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate strWebPath
End With

Do
    Sleep 250
    DoEvents
Loop While IE.Busy Or IE.ReadyState <> 4

If TypeName(IE) <> "IWebBrowser2" Or IE.Name <> "Internet Explorer" Then
    Set objShellWindows = CreateObject("Shell.Application").Windows
    For X = 0 To objShellWindows.Count - 1
        Set IE = objShellWindows.Item(X)
        If Not IE Is Nothing Then
            If IE.Name = "Internet Explorer" Then
                If InStr(1, IE.LocationURL, strWebPath, 1) > 0 Then
                    Do While IE.Busy Or IE.ReadyState <> 4
                        Sleep 250
                        DoEvents
                    Loop
                    Exit For
                End If
            End If
        End If
        Set IE = Nothing
    Next
    Set objShellWindows = Nothing
End If

If Not IE Is Nothing Then
    IE.Document.all.Item("location_input").Value = MerchantZip
    Sleep 250
    For Each Button In IE.Document.getelementsbytagname("form")
        If StrComp(Button.Type, "Button", 1) = 0 Then
            Button.Click
        End If
    Next
    Set IE = Nothing
End If

End Sub

Upvotes: 0

Related Questions