Problems in VBA code execution

Asked

Viewed 275 times

1

This code clicks on the 'Legislation' tab and fills a field with a given text (2º For Each) and clicks on the search button (3º For Each). The problem is that when I run the code, it clicks on the tab and fills in the text, but it Zera the field before doing the research, and I tried it in two different ways. What should I do?

   '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 CDE_ANEEL()

    Dim IE As Object
    Dim doc As Object, doc1 As Object, doc2 As Object, aba As Object
    Dim sFilename As String, sFilepath As String
    Dim objStream As Object
    Dim strData As String
    Dim el

    Set objStream = CreateObject("ADODB.Stream")
    sFilename = "CDE.txt"
    sFilepath = "C:\Users\leandro.lazari\Desktop\Dados MegaWhat\Dados" & "\" & sFilename

    Set IE = CreateObject("InternetExplorer.Application")

    IE.navigate "http://biblioteca.aneel.gov.br/index.html"
    IE.Visible = True
    Sleep 2000

        Set doc = IE.document.getElementsByTagName("frame")(0).contentDocument.body
        Set doc1 = doc.getElementsByClassName("inputLegEsq")
        Set doc2 = doc.getElementsByClassName("button_busca")
        Set aba = doc.getElementsByClassName("text-aba")

'clica em aba 'Legislação'

 For Each el In aba
        'Debug.Print el.InnerText
        If el.innerText = "Legislação" Then el.Click
    Next el

    'preenche campo: 'Todos os Campos'

   For Each el In doc1
       'Debug.Print el.Name, el.Value
       If el.Name = "leg_campo1" Then el.Value = "Conta de Desenvolvimento Energético"
       Next el

    'Apertar Botão
    For Each el In doc2
        Debug.Print el.Title, el.onclick
        Debug.Print InStr(1, el.onclick, "Confere(5613,5,'',parent.hiddenFrame.modo_busca)")
        If InStr(1, el.onclick, "Confere(5613,5,'',parent.hiddenFrame.modo_busca)") > 0 Then el.Click
    Next el

  ' Apertar o Botão de outra forma

    For Each el In doc2
         If InStr(1, el.onclick, "Confere(5613,5,'',parent.hiddenFrame.modo_busca)") > 0 Then el.Click
    Next el

   End Sub
vba excel-vba

  • Check that on this site, in the text box you are updating or on the button, there is some associated script that is clearing the value of the box.

  • Pagotti, has a Javascript that in the text has p written "autoReset: true". Since I don’t know much about it, I don’t know if this is the issue or how to solve it.

  • It is likely that this is causing this behavior. Try to force the event change before calling the click

  • And how I do it?

  • 1

    I don’t know the IE DOM object model via automation like you’re using, but I believe it’s something like making a el.Focus before putting the value and a el.Blur after. Search the IE object model to know the commands you can execute on an element.

1 answer

2


Code

You need to trigger the onchange event with the following code

'https://stackoverflow.com/questions/49537537/ie-click-not-working-properly-due-to-value-onchange
If el.Name = "leg_campo1" Then
    el.Value = "Conta de Desenvolvimento Energético"
    Set evnt = doc.ownerDocument.createEvent("HTMLEvents")
    evnt.initEvent "change", True, True
    el.dispatchEvent evnt
End If

So the whole code goes like this:

'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 CDE_ANEEL()

    Dim IE As Object
    Dim doc As Object, doc1 As Object, doc2 As Object, aba As Object
    Dim el

    Set IE = CreateObject("InternetExplorer.Application")

    IE.navigate "http://biblioteca.aneel.gov.br/index.html"
    IE.Visible = True
    EsperaIE IE, 2500
    Set doc = IE.document.getElementsbyTagName("frame")(0).contentdocument.body
    Set doc1 = doc.getElementsbyClassName("inputLegEsq")
    Set doc2 = doc.getElementsbyClassName("button_busca")
    
    'Digita no campo de Busca
    For Each el In doc1
        If el.Name = "leg_campo1" Then
            el.Value = "Conta de Desenvolvimento Energético"
            el.InnerText = "Conta de Desenvolvimento Energético"
            Set evnt = doc.OwnerDocument.createEvent("HTMLEvents")
            evnt.initEvent "change", True, True
            el.dispatchEvent evnt
        End If
    Next el

    Sleep 5000

    'Apertar Botão
    For Each el In doc2
        If InStr(1, el.onclick, "Confere(5613,5,'',parent.hiddenFrame.modo_busca)") > 0 Then
            el.Click
            Exit For
        End If
    Next el


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

Browser other questions tagged vba excel-vba

You are not signed in. Login or sign up in order to post.