CODEWITHSUNDEEP
excelvbaurlweb-scrapingextract
Hong Yew
Hong Yew

Reputation: 97

Extract data within iframes using VBA code looping problem

Below code ran well with result and I have gotten the value I need. But at the end of running process, it shows "Run-time error '424' Object required" on the line " With .document.getElementById("bm_ann_detail_iframe").contentDocument".

Sample url link used to extract data from: http://www.bursamalaysia.com/market/listed-companies/company-announcements/5927925

http://www.bursamalaysia.com/market/listed-companies/company-announcements/5927809

http://www.bursamalaysia.com/market/listed-companies/company-announcements/5927509

Public Sub GetInfo()
Dim IE As New InternetExplorer, clipboard As Object
With IE
    .Visible = False

    For u = 2 To 100

    .navigate Cells(u, 1).Value

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


    With .document.getElementById("bm_ann_detail_iframe").contentDocument
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 3) = .getElementsByClassName("formContentDataH")(3).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 4) = .getElementsByClassName("company_name")(0).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 5) = .getElementsByClassName("formContentDataH")(1).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 6) = .getElementsByClassName("formContentData")(3).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 7) = .getElementsByClassName("formContentData")(4).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 8) = .getElementsByClassName("formContentData")(5).innerText
        ThisWorkbook.Worksheets("Sheet1").Cells(u, 9) = .getElementsByClassName("formContentData")(9).innerText

   End With
   Next u
End With
End Sub

Upvotes: 1

Views: 208

Answers (1)

QHarr
QHarr

Reputation: 84465

I am going to suggest a simple test that there is an "http" string within the cell (use that as a basis for there being a URL to work with). I prefer this to an IsEmpty test. This is to mitigate attempting to access a non existent page during your loop. Your error seems to indicate that the frame is not located during the loop. So, possibilities include no URL in cell (it seems from comments that this is a possibility) or an invalid URL/One that does not conform to the expected pattern.

I deal with the latter posssibilities by using an On Error Resume Next statement. You may wish to debug on specific failing URLs if present to check for the case of iframe not present when expected and identify any further refinements required.

In the case of differing number of URLs, you can have a fixed end loop, as you currently have, or dynamically determine the last row e.g. 

Dim lastRow As Long
With ThisWorkbook.Worksheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Then loop from 

For u = 2 To lastRow

I still prefer to have an Instr test of "http" being present.

VBA:

Option Explicit
Public Sub GetInfo()
    Dim IE As New InternetExplorer, u As Long
    With IE
        .Visible = True

        For u = 2 To 100
            If InStr(Cells(u, 1).Value, "http") > 0 Then

                .navigate Cells(u, 1).Value

                While .Busy Or .readyState < 4: DoEvents: Wend
                On Error Resume Next
                With .document.getElementById("bm_ann_detail_iframe").contentDocument
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 3) = .getElementsByClassName("formContentDataH")(3).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 4) = .getElementsByClassName("company_name")(0).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 5) = .getElementsByClassName("formContentDataH")(1).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 6) = .getElementsByClassName("formContentData")(3).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 7) = .getElementsByClassName("formContentData")(4).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 8) = .getElementsByClassName("formContentData")(5).innerText
                    ThisWorkbook.Worksheets("Sheet1").Cells(u, 9) = .getElementsByClassName("formContentData")(9).innerText
                End With
                On Error GoTo 0
            End If
        Next u
    End With
End Sub

Upvotes: 1

Related Questions