EXPLORER HANDLING THROUGH VBA

Question

EXPLORER HANDLING THROUGH VBA

Navigation

#1 by (2 votes)

1

Good morning, I'm starting to work with VBA so my question may be pretty basic, but my problem is this: When I run the code (which is below) it looks like an error alert in the 'For each' line

"Run-time error '438'. Object does not support this property or method "

The code is analogous to another one I got 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.

vba excel-vba

asked by anonymous 19.03.2018 / 13:52

1 answer

Browse the best-selling product Change a clone's style

score 2 · Accepted Answer

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) 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 Split function to split the string into multiple elements from a separator.

And the Trim function is used to avoid errors when space is used as a separator, because this function removes spaces at 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:

Another example: EXTRAIRELEMENTO("http://sdro.ons.org.br/SDRO/DIARIO/2018_03_15/Html/DIARIO_15-03-2018.xlsx", 2, "/") returns 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 tabs, such as links, directories, product codes, dates, etc.

Expect IE

I usually use an SOen function to perform the wait time for the page to load completely, only While IE.Busy Or IE.ReadyState <> 4: Wend may not be enough.

Then instead of While IE.Busy Or IE.ReadyState <> 4: Wend you call the EsperaIE function and choose the wait time in ms. Example: EsperaIE IE, 5000

Primary code

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

NOTE: Do not forget to enable references: Microsoft HTML Object Library and Microsoft Internet Controls.

In Tools > References ...