Extract data within iframes using VBA code looping problem

Question

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

Which URL does it fail on please? The 3 you have provided work fine. Do you have valid URLs in every cell? See my answer to last question here for dealing with this. — QHarr
– QHarr, Commented Sep 30, 2018 at 10:00
ya, the url work fine.And I already gotten all my values. But everytime the process end, It show Run-Time error'424' object required. It seem like could not end smooth. — Hong Yew
– Hong Yew, Commented Sep 30, 2018 at 11:15
That would seem to imply you are trying to access a document which is no longer available, are you sure that Cells(100, 1) has a valid URL in to work with and that the loop stops at this point as expected? If you had looped properly and got all your values then the loop should not be returning to With .document.getElementById("bm_ann_detail_iframe").contentDocument , which is where you say the error is, no? — QHarr
– QHarr, Commented Sep 30, 2018 at 11:18
the number of url is depend on the page I extract from, so it vary from number between 1 to 100. If cells(100,1) is blank, will it cause the looping could stop well?I am thinking that is the cause of problem — Hong Yew
– Hong Yew, Commented Sep 30, 2018 at 11:22
Yes. You need to dynamically determine the end of the loop. Store it in a variable and loop to that. If you loop to a blank URL then you get problems as there is no document to work with. In my answer to your other question I do a simple test to see if it is a URL in the cell i.e. If InStr(links(u), "http") > 0 Then . In this case rather than cells in the sheet I had put the contents of the cells into an array but same principle. — QHarr
– QHarr, Commented Sep 30, 2018 at 11:23

QHarr · Accepted Answer · 2018-09-30 11:45:03Z

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

Collectives™ on Stack Overflow

Extract data within iframes using VBA code looping problem

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related