OPERATION OF THE EXPLORER VIA VBA

Question

Good morning, I’m starting to work with VBA so my doubt may be very basic, but my problem is the following: When I run the code (below) it looks like an error alert on the 'For each' line'

"Runtime error '438'. Object does not accept this property or method"

The code is analogous to another one I took from a friend, just making the relevant changes and his is working.

Sub TesteBusca()

Dim ie As Object
Dim sWindows As Object
Dim sJanelas As Object
Dim sDados As String


Set ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://sdro.ons.org.br/SDRO/DIARIO/index.htm"
ie.Visible = True

    Do Until (ie.readyState = 4 And Not ie.Busy)
        DoEvents
    Loop

i = 1
For Each link In ie.document.getElementByTagName("a")

    If Mid(link.href, 8, 22) = "EnergiaNaturalAfluente" Then
    i = i + 1

       With iefat
           .Visible = bMostrarNavegador
           .navigate link.href
       End With

       Do Until (iefat.readyState = 4 And Not iefat.Busy)
         DoEvents
       Loop
    link.Click
    If i = 2 Then Exit For
    End If

Next link


End Sub

Thank you in advance, Abs.

Answer 1

Code

You can accomplish this with this code:

'Declara função Sleep
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

Sub TesteBusca()

    Dim IE As Object
    Dim sWindows As Object
    Dim sJanelas As Object
    Dim sDados As String
    Dim doc As MSHTML.HTMLDocument

    Set IE = CreateObject("InternetExplorer.Application")

    IE.navigate "http://sdro.ons.org.br/SDRO/DIARIO/index.htm"
    IE.Visible = True

    EsperaIE IE, 2000

    'Debug.Print IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a").innerText
    i = 1
    For Each link In IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a")
    'Debug.Print EXTRAIRELEMENTO(link.href, 8, "/")
        If EXTRAIRELEMENTO(link.href, 8, "/") = "21_EnergiaNaturalAfluente.html" Then
            i = i + 1


            link.Click
             EsperaIE IE, 2000
            If i = 2 Then Exit For
        End If

    Next link   
End Sub

Public Sub EsperaIE(IE As Object, Optional time As Long = 250)
'Código de: https://stackoverflow.com/questions/33808000/run-time-error-91-object-variable-or-with-block-variable-not-set
Dim i As Long
Do
    Sleep time
    Debug.Print CStr(i) & vbTab & "Ready: " & CStr(IE.READYSTATE = 4) & _
                vbCrLf & vbTab & "Busy: " & CStr(IE.Busy)
    i = i + 1
Loop Until IE.READYSTATE = 4 Or Not IE.Busy
End Sub

Function EXTRAIRELEMENTO(Txt As String, n, Separator As String) As String
    On Error GoTo ErrHandler:
    EXTRAIRELEMENTO = Split(Application.Trim(Mid(Txt, 1)), Separator)(n - 1)
    Exit Function
ErrHandler:
    ' error handling code
    MsgBox "Erro, veriique os dados de entrada."
    EXTRAIRELEMENTO = CVErr(xlErrNA)
    On Error GoTo 0
End Function

Explanation

Extract Element

The User Defined Function (UDF - User Defined Function) of extract element is declared to extract the elements of a string separated by a separator, in this example "/", where each element has an index.

This uses the function Split to split the string into multiple elements from a separator.

And the function Trim is used to avoid errors when space is used as a separator, as this function removes the spaces from the beginning and end of the string.

Example: EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 1, "/") returns the value http:

Other Example: EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 2, "/") returns the value `` or empty

Other:

EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 3, "/") returns the value sdro.ons.org.br

A very useful function, which can be applied to extract elements of Strings with separators, such as links, directories, product codes, dates, among others.

Wait IE

Normally I use a Soen function to perform the function of waiting a while for the page to load completely, only While IE.Busy Or IE.ReadyState <> 4: Wend may not be enough.

So instead of While IE.Busy Or IE.ReadyState <> 4: Wend You call the function EsperaIE and chooses the waiting time in ms. Example: EsperaIE IE, 5000

Master code

As the ONS site has a hierarchy with iframes and not only the "pure" HTML. You need to search for the "a" tag with: IE.document.getElementsByTagName("frame")(1).contentDocument.getElementsByTagName("a"), where the frame index is (1), index (0) is at the top of the page.

NOTE: Don’t forget to enable references: Microsoft HTML Object Library and Microsoft Internet Controls.

In Tools > References...