Web Scraping from - https://www.reliancepetroleum.com/locateafuelstation

Question

Request you to please help me in getting data from - https://www.reliancepetroleum.com/locateafuelstation. Here drop down selections has to be made for all the states. I want to extract dropdown search result for all the states on a daily basis. In total this website has 22 -23 states I want all states information in 1 excel sheet. Request you to please automate this via VBA code or any web scraping method.

What have you tried so far? Are you just asking someone to code for you? If you need a couple of examples you can perhaps start [Here](https://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba) — Miguel_Ryu, Jan 29 '18 at 13:30
Hi, I have tried using below mentioned VBA but its not copying the data in to the excel sheet. — Deepika.Gohil, Jan 29 '18 at 13:45
I have tried with below VBA but its not copying the data into excel sheet. — Deepika.Gohil, Jan 29 '18 at 13:46
Part one - Sub extracttablesdata() Dim IE As Object, obj As Object Dim mystate As String Dim t As Integer, r As Integer, c As Integer Dim elementcollection As Object Set IE = CreateObject("InternetExplorer.Application") mystate = InputBox("Enter State") With IE .Visible = True .navigate ("https://www.reliancepetroleum.com/locateafuelstation") While IE.ReadyState <> 4 DoEvents Wend For Each obj In IE.Document.All.Item("States").Options If obj.innerText = mystate Then obj.Selected = True End If Next obj IE.Document.getElementsByName("btnSubmit").Item.Click — Deepika.Gohil, Jan 29 '18 at 13:49
Would you like to expand your question including code you've tried, problems, and errors occurred in a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) — Miguel_Ryu, Jan 29 '18 at 13:49
part 2 - Do While IE.Busy: DoEvents: Loop ThisWorkbook.Sheets("Sheet1").Range("A1:K1000").ClearContents Set elemCollection = IE.Document.getElementsByTagName("TABLE") For t = 0 To (elemCollection.Length - 1) For r = 0 To (elemCollection(t).Rows.Length - 1) For c = 0 To (elemCollection(t).Rows(r).Cells.Length) ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText Next c Next r Next t End With Set IE = Nothing End Sub — Deepika.Gohil, Jan 29 '18 at 13:49
Do you get an error? Did you try to debug the code (F8)? Did you read the HTML on the website to know which Elements to pick up? ... I suggest as well you edit you question with the code for people to be more inclined to help you. — Miguel_Ryu, Jan 29 '18 at 14:03

Xabier · Answer 1 · 2018-01-30T08:59:55.543

I've tested this and it works as expected to retrieve data for all States, you simply have to amend the line HTML.getElementById("storetab").innerText to get the text from whichever HTML element you wish:

Enum READYSTATE
    READYSTATE_UNINITIALIZED = 0
    READYSTATE_LOADING = 1
    READYSTATE_LOADED = 2
    READYSTATE_INTERACTIVE = 3
    READYSTATE_COMPLETE = 4
End Enum
Sub Test()
Dim ie As New InternetExplorer
Dim HTML As HTMLDocument
Dim i As Long

strURL = "https://www.reliancepetroleum.com/locateafuelstation"

ie.Visible = True
ie.navigate strURL

Do While (ie.Busy Or ie.READYSTATE <> READYSTATE.READYSTATE_COMPLETE)
  DoEvents
Loop

Set HTML = ie.document

Set ctY = HTML.getElementById("idState")
NumberOfoptions = ctY.Options.Length
    For i = 1 To NumberOfoptions
        Set ctY = HTML.getElementById("idState")

        Do While (ie.Busy Or ie.READYSTATE <> READYSTATE.READYSTATE_COMPLETE)
          DoEvents
        Loop

        ctY.selectedIndex = i

        HTML.getElementById("idBtnSubmit").Click

        Do While (ie.Busy Or ie.READYSTATE <> READYSTATE.READYSTATE_COMPLETE)
          DoEvents
        Loop

        Sheet1.Cells(i, 1).Value = HTML.getElementById("storetab").innerText

        'ie.GoBack
        ie.navigate strURL

        Do While (ie.Busy Or ie.READYSTATE <> READYSTATE.READYSTATE_COMPLETE)
          DoEvents
        Loop

    Next i

End Sub

Web Scraping from - https://www.reliancepetroleum.com/locateafuelstation

1 Answers1