CODEWITHSUNDEEP
excelvbaweb-scrapinghtml-select
vipin Kumar
vipin Kumar

Reputation: 61

How to select value from drop down on web url?

I am trying to select data from the dropdown in the web URL, my all code is working fine but I am unable to select the value from the dropdown.

Sub pulldata2()
        Dim tod As String, UnderLay As String
        Dim IE As Object
        Dim doc As HTMLDocument

'Html table

    Dim Tbl As HTMLTable, Cel As HTMLTableCell, Rw As HTMLTableRow, Col As HTMLTableCol
    Dim TrgRw As Long, TrgCol As Long

'Create new sheet

    tod = ThisWorkbook.Sheets("URLList").Range("C2").Value   
    have = False
    For Each sht In ThisWorkbook.Sheets
        If sht.Name = tod Then
        have = True
        Exit For
        End If
    Next sht

    If have = False Then
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = tod
    Else
    If MsgBox("Sheet " & tod & " already exists Overwrite Data?", vbYesNo) = vbNo Then Exit Sub
    End If

'Start Internetexplorer

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate "https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=NIFTY&date=25APR2019"

        Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
        Loop

    Set doc = IE.document

Dim ColOff As Long

'Put data to sheet and loop to next URL

For Nurl = 2 To 191
ColOff = (Nurl - 2) * 23
TrgRw = 1
    UnderLay = ThisWorkbook.Sheets("URLList").Range("A" & Nurl).Value
    doc.getElementById("underlyStock").Value = UnderLay
    doc.parentWindow.execScript "goBtnClick('stock');", "javascript"

'now i want to select data from dropdown id=date, value= 27JUN2019

doc.querySelector("Select[name=date] option[value=27JUN2019]").Selected = True


        Do While IE.Busy Or IE.readyState <> 4
            Application.Wait DateAdd("s", 1, Now)
        Loop

        Set Tbl = doc.getElementById("octable")

        ThisWorkbook.Sheets(tod).Cells(TrgRw, ColOff + 1).Value = UnderLay
        ThisWorkbook.Sheets(tod).Cells(TrgRw, ColOff + 1).Font.Size = 20
        ThisWorkbook.Sheets(tod).Cells(TrgRw, ColOff + 1).Font.Bold = True
        ThisWorkbook.Sheets(tod).Cells(TrgRw, ColOff + 1).Select
        TrgRw = TrgRw + 1


        For Each Rw In Tbl.Rows
            TrgCol = 1
            For Each Cel In Rw.Cells
                ThisWorkbook.Sheets(tod).Cells(TrgRw, ColOff + TrgCol).Value = Cel.innerText
                TrgCol = TrgCol + Cel.colSpan   ' if Column span is > 1 multiple
            Next Cel
            TrgRw = TrgRw + 1
        Next Rw

    TrgRw = TrgRw + 1
    Next Nurl

'exit the internetexplorer

        IE.Quit
        Set IE = Nothing
    End Sub

why my code not working, I am new in VBA please help to find an error in my code.

Upvotes: 1

Views: 430

Answers (1)

QHarr
QHarr

Reputation: 84465

Simply alter the url rather than use dropdown

https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=NIFTY&date=27JUN2019

You can also use xhr to get the content rather than a slow browser. I use the clipboard to write out the table.

Option Explicit
Public Sub GetInfo()
    Dim html As Object, hTable As Object, ws As Worksheet, clipboard As Object
    Set html = New HTMLDocument
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=NIFTY&date=27JUN2019", False
        .send
        html.body.innerHTML = .responseText
        Set hTable = html.getElementById("octable")
        clipboard.SetText hTable.outerHTML
        clipboard.PutInClipboard
        ws.Range("A1").PasteSpecial
    End With
End Sub

Alternative:

1) You could loop the tr and td within hTable above to write out the table

2) You could also use powerquery from web (via data tab Excel 2016+ , or using free powerquery add-in for 2013. You paste the url into the top of the pop up browser and press Go then select the table to import.

Changing stocks:

Stocks are part of the url query string e.g. symbol=NIFTY , so you can concatenate the new symbol into the url during a loop

"https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=" & yourSymbolGoesHere & "&date=27JUN2019"

If you really want to use IE be sure to encase the value of the attribute within '' e.g. '27JUN2019'

Option Explicit
'VBE > Tools > References: Microsoft Internet Controls
Public Sub ClickButton()
    Dim ie As InternetExplorer
    Const URL As String = "https://nseindia.com/live_market/dynaContent/live_watch/option_chain/optionKeys.jsp?segmentLink=17&instrument=OPTIDX&symbol=NIFTY&date=25APR2019"
    Set ie = New InternetExplorer

    With ie
        .Visible = True
        .Navigate2 URL

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .querySelector("[value='27JUN2019']").Selected = True
            Stop
        End With
    End With
End Sub

Upvotes: 2

Related Questions