I can't manage a select listbox in internet explorer 11 with VBA/Excel



  • I've been trying to scrape some data about schools here in Brasil, but something is driving me nuts...
    The URL is: [http://especiais.g1.globo.com/educacao/enem/2015/enem-2015-medias-por-escola/]
    There are tree listbox that I have to select, and I can put the selection data inside them but the web page doesn't load again with the new selection. I guess there is some function in Javascript that manage the listbox and I don't know how to call this function in Javascritp using Excel/VBA.

    HTML

    <div id="mytbody">
         <select><option value="">Selecione o estado</option>
              <option value="AC">AC</option>
              <option value="AL">AL</option>
              <option value="AM">AM</option>
              <option value="AP">AP</option>
              <option value="BA">BA</option>
              <option value="CE">CE</option>
              <option value="DF">DF</option>
              <option value="ES">ES</option>
              <option value="GO">GO</option>
              <option value="MA">MA</option>
              <option value="MG">MG</option>
              <option value="MS">MS</option>
              <option value="MT">MT</option>
              <option value="PA">PA</option>
              <option value="PB">PB</option>
              <option value="PE">PE</option>
              <option value="PI">PI</option>
              <option value="PR">PR</option>
              <option value="RJ">RJ</option>
              <option value="RN">RN</option>
              <option value="RO">RO</option>
              <option value="RR">RR</option>
              <option value="RS">RS</option>
              <option value="SC">SC</option>
              <option value="SE">SE</option>
              <option value="SP">SP</option>
              <option value="TO">TO</option>
         </select>
         <select>
              <option value="">Selecione a rede</option>
              <option value="Estadual">Estadual</option>
              <option value="Federal">Federal</option>
              <option value="Municipal">Municipal</option>
              <option value="Privada">Privada</option>
         </select>
         <select>
              <option value="">Selecione rural ou urbana</option>
              <option value="Rural">Rural</option>
              <option value="Urbana">Urbana</option>
         </select>
    </div>
    

    JAVASCRIPT

    <script type="text/javascript">
                    $( document ).ready(function() {
                        // $( ".app .table .bootstrap-table tbody > tr > td:nth-child(5):contains('Privada')" ).css( "text-decoration", "underline" );
    
                        // TOGGLE DOS ITENS DA TABELA DO ENEM
                        $(".btn-uf").click( function() {
                            $(".app .table .bootstrap-table tbody > tr > td:nth-child(3), .app .table .bootstrap-table thead > tr > th:nth-child(3)").toggle();
                            $(this).toggleClass("show");
                        });
    
                        $(".btn-rede").click( function() {
                            $(".app .table .bootstrap-table tbody > tr > td:nth-child(5), .app .table .bootstrap-table thead > tr > th:nth-child(5)").toggle();
                            $(this).toggleClass("show");
                        });
    
                        $(".btn-localizacao").click( function() {
                            $(".app .table .bootstrap-table tbody > tr > td:nth-child(6), .app .table .bootstrap-table thead > tr > th:nth-child(6)").toggle();
                            $(this).toggleClass("show");
                        });
    
                        $(".btn-permanencia").click( function() {
                            $(".app .table .bootstrap-table tbody > tr > td:nth-child(7), .app .table .bootstrap-table thead > tr > th:nth-child(7)").toggle();
                            $(this).toggleClass("show");
                        });
    
                        $(".btn-socio").click( function() {
                            $(".app .table .bootstrap-table tbody > tr > td:nth-child(8), .app .table .bootstrap-table thead > tr > th:nth-child(8)").toggle();
                            $(this).toggleClass("show");
                        });
                    });
                </script>
    

    Excel/VBA

    Option Explicit
    '------------------------------------------------------------------------------
    'VARIÁVEIS - Dimensionamento
    '------------------------------------------------------------------------------
    
    'Private appState                           As CAppState
    Private selenium                            As New SeleniumWrapper.WebDriver
    Private objIE                               As InternetExplorer
    Private wST                                 As Worksheet
    Private el                                  As Object
    Private obj                                 As Object
    Private elemento                            As Object
    Private totRegistros                        As Double
    Private UF()                                As String  'Vetor de unidades da Federação
    Private Rede()                              As String  'Vetor de tipo de rede de ensino
    Private Localização()                       As String  'Vetor de Localização no Município
    Private vTemporario()                       As String  'Vetor temporário
    Private linha, coluna                       As Integer 'Controladores de posição na planilha
    Private posInicial, posFinal                As Byte
    Private qt, qtuf, qtrede, qtlocalização     As Integer
    Private i, j                                As Integer
    
    Sub ImportaENEM()
    '------------------------------------------------------------------------------
    'INSTANCIAMENTO DAS VARIÁVEIS
    '------------------------------------------------------------------------------
        'Set appState = New CAppState
        'selenium.Start "IE", "http://especiais.g1.globo.com/"
        'selenium.Open "/educacao/enem/2015/enem-2015-medias-por-escola/"
        Set objIE = New InternetExplorer
        Set wST = Planilha2
           
        'appState.SetState
            
        With objIE
            .Visible = True
            .navigate ("http://especiais.g1.globo.com/educacao/enem/2015/enem-2015-medias-por-escola")
        End With
            
        Call EsperaCarregarIE(2)
        
        Call BuildVetoresSeleção(0, 0, 0, 0)
        
        Call Seletores(UF, Rede, Localização, qt, elemento, obj)
         
        Call CalculaLinhasTotais
                   
        Call Titulos
           
        Call PreencherLinhas(2, 1)
            
        MsgBox "Processo Finalizado", vbInformation
    
    TratamentoErros:
        'Exit Sub
    
                 
    wST.Columns.AutoFit
    objIE.Quit
    Set wST = Nothing
    Set objIE = Nothing
    Set el = Nothing
    Set elemento = Nothing
    'Set appState = Nothing
    End Sub
    
    Sub BuildVetoresSeleção(ByVal qt, qtuf, qtrede, qtlocalização As Integer)
    
        With objIE
    
            qt = .document.GetElementsByTagName("select").Length - 1
            Set elemento = .document.GetElementsByTagName("select")
                
            For i = 0 To qt - 1
                linha = elemento.Item(i).Length
                For j = 1 To linha - 1
                    Select Case i
                        Case 0
                            qtuf = qtuf + 1
                            ReDim Preserve UF(linha - 1)
                            UF(qtuf) = elemento.Item(i).Children(j).innerText
                            Debug.Print UF(qtuf)
                        Case 1
                            qtrede = qtrede + 1
                            ReDim Preserve Rede(linha - 1)
                            Rede(qtrede) = elemento.Item(i).Children(j).innerText
                            Debug.Print Rede(qtrede)
                        Case 2
                            qtlocalização = qtlocalização + 1
                            ReDim Preserve Localização(linha - 1)
                            Localização(qtlocalização) = elemento.Item(i).Children(j).innerText
                            Debug.Print Localização(qtlocalização)
                    End Select
                    
                Next j
            
            Next i
        
        End With
    
    End Sub
    
    Sub CalculaLinhasTotais()
    
        With objIE
                 
            For Each el In .document.GetElementsByTagName("div") ' "Showing 1 to 50 of 15,959 entries"
                If el.innerText Like "Showing*" Then
                   posInicial = InStr(el.innerText, "of ") + 3
                   posFinal = InStr(el.innerText, " entries")
                   totRegistros = Mid(el.innerText, posInicial, posFinal - posInicial)
                   totRegistros = CDbl(Replace(totRegistros, ",", ""))
                   Debug.Print totRegistros
                   Exit For
                End If
            Next
    
        End With
    
    End Sub
    
    Sub Titulos()
        
        wST.Cells.Clear
        
        wST.Cells(1, 1) = "Ranking"
        wST.Cells(1, 2) = "Nome da escola"
        'wst.Cells(1, 3) = "UF"
        wST.Cells(1, 3) = "Município"
        'wst.Cells(1, 5) = "Rede"
        'wst.Cells(1, 6) = "Urbana/Rural"
        wST.Cells(1, 4) = "Indicador de Permanência na escola"
        wST.Cells(1, 5) = "Indicador Socio-econômico"
        wST.Cells(1, 6) = "Média da escola (provas objetivas)"
        wST.Cells(1, 7) = "Média da escola (linguagem)"
        wST.Cells(1, 8) = "Média da escola (matemática)"
        wST.Cells(1, 9) = "Média da escola (ciências da natureza)"
        wST.Cells(1, 10) = "Média da escola (ciências humandas)"
        wST.Cells(1, 11) = "Média da escola (redação)"
        'wST.rows(1).Style = Bold
        
        wST.rows(1).Font.Bold = True
        
        
    End Sub
    
    Sub PreencherLinhas(ByVal linha, coluna As Integer)
        
        With objIE
            
            For Each el In .document.GetElementsByTagName("td")
                If coluna = 12 Then
                    If linha > totRegistros Then Exit Sub
                    linha = linha + 1
                    coluna = 1
                End If
                Select Case coluna
                    Case 3
                        wST.Cells(linha, coluna) = UF
                    Case 5
                        wST.Cells(linha, coluna) = Rede
                    Case 6
                        wST.Cells(linha, coluna) = Localização
                    End Select
                wST.Cells(linha, coluna) = el.innerText
                coluna = coluna + 1
            Next
            
            For Each el In .document.GetElementsByTagName("a")
                If el.innerText = "Próxima" Then el.Click: Call EsperaCarregarIE(1): Exit For
            Next                               
        End With
                
        Call PreencherLinhas(linha, coluna)
    
    End Sub
    
    Sub Seletores(ByRef UF() As String, ByRef Rede() As String, ByRef Localização() As String, _
                  ByVal qt As Integer, ByVal el As Object, ByVal obj As Object)
    
        Dim i, j, k As Integer
    
        Set obj = objIE.document.GetElementsByTagName("option")
    
            For i = 1 To UBound(UF) ' Vetor de unidades da federação
                For Each el In obj
                    If el.innerText = UF(i) Then
                        el.Selected = True
                        el.Focus
                        el.FireEvent ("onchange")
                        el.FireEvent ("onselect")
                        el.FireEvent ("onload")
                        el.FireEvent ("onmouseleave")
                        el.FireEvent ("onactivate")
                        el.FireEvent ("onclick")
                        el.FireEvent ("onfocusin")
                        el.FireEvent ("onfocusout")
                        el.FireEvent ("oninput")
                        el.FireEvent ("onloadedmetadata")
                        el.FireEvent ("onplay")
                        el.FireEvent ("onsubmit")
                        el.Click
                        MsgBox "Ok"
                        Exit For
                        End If
                Next el
    
                For j = 1 To UBound(Rede) ' Vetor do tipo de rede de ensino
                    For Each el In obj
                        If el.innerText = Rede(j) Then
                            el.Selected = True
                            el.setCapture
                            Exit For
                            End If
                    Next el
    
                    For k = 1 To UBound(Localização) ' Vetor da localização da unidade de ensino
                        For Each el In obj
                                If el.innerText = Localização(k) Then
                                    el.Selected = True
                                    el.Focus
                                    Application.SendKeys "{DOWN}"
                                    Application.SendKeys "~"
                                    Application.SendKeys "{ENTER}"
                                    el.FireEvent ("onchange")
                                    el.FireEvent ("onselect")
                                    el.FireEvent ("onload")
                                    el.FireEvent ("onmouseleave")
                                    el.FireEvent ("onactivate")
                                    el.FireEvent ("onclick")
                                    el.FireEvent ("onfocusin")
                                    el.FireEvent ("onfocusout")
                                    el.FireEvent ("oninput")
                                    el.FireEvent ("onloadedmetadata")
                                    el.FireEvent ("onplay")
                                    el.FireEvent ("onsubmit")
                                    el.FireEvent ("onmouseleave")
                                    el.Click
                                    'objIE.Refresh2
                                    Exit For
                                    End If
                        Next el
                    Next k
                Next j
            Next i
    End Sub
    
    Sub EsperaCarregarIE(ByVal segundos As Integer)
        Do While objIE.busy Or objIE.readyState <> 4
            DoEvents
            Application.Wait TimeSerial(Hour(Now), Minute(Now), (Second(Now) + segundos))
        Loop
        
    End Sub
    

    You can see that in Sub "Seletores" I tried a lot of things to manage the listbox but nothing worked. I'd really appreciate a support.


  • administrators

    You are overthinking this. Here's all the data you need - http://especiais.g1.globo.com/educacao/enem/2015/enem-2015-medias-por-escola/js/data.json?_=1485788580668

    Make a simple GET request to this URL and parse it instead of doing all that unnecessary automation.

    Read these -

    Best practices of scraping website data for beginners

    XmlHttpRequest – Http requests in Excel VBA

    Parse JSON in VBA

    Hope that helps!


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.