CODEWITHSUNDEEP
excelvbafunctionweb-scrapinggetelementbyid
Hong Yew
Hong Yew

Reputation: 97

Extract html source code into excel using VBA

I am trying to simply paste the content or innertext into excel using getElementByID function. The content is actually the iframe link which I am trying to extract it and paste into cell. The photo shown is the html source code.

 Sub GetData()

    Dim ie As New SHDocVw.InternetExplorer
    Dim htmldoc As MSHTML.HTMLDocument
    Dim result As MSHTML.IHTMLElement


    ie.Visible = True
    ie.navigate "http://www.bursamalaysia.com/market/listed-companies/company-announcements/5925865"

    Do While ie.readyState <> READYSTATE_COMPLETE
    Loop


    Application.Wait (Now() + TimeValue("00:00:016")) ' For internal page refresh or loading

    Set htmldoc = ie.document
    Set Results = HTML.getElementById("bm_ann_detail_iframe")

    Sheets("Sheet1").Range("a1").Value = Results.innerText

End Sub

html source code

Upvotes: 0

Views: 1610

Answers (1)

QHarr
QHarr

Reputation: 84465

You should use consistent variable naming in your code. If you put Option Explicit at the top of your code that will help.

You want to access the src attribute of the iframe to get the URL shown.

If you plan to use the new URL then you actually want the part before the "#". This means changing to: 

ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Split(ie.document.getElementById("bm_ann_detail_iframe").src, "#")(0)

Code:

Option Explicit
Public Sub GetData()
    Dim ie As New SHDocVw.InternetExplorer
    ie.Visible = True
    ie.navigate "http://www.bursamalaysia.com/market/listed-companies/company-announcements/5925865"

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

     ThisWorkbook.Worksheets("Sheet1") = ie.document.getElementById("bm_ann_detail_iframe").src
    ie.Quit
End Sub

Upvotes: 1

Related Questions