Tomás Vásquez Fórum


https://www.tomasvasquez.com.br/forum/

Obter dados de Pagina WEB

https://www.tomasvasquez.com.br/forum/viewtopic.php?t=7536

Página 1 de 1

Obter dados de Pagina WEB

Enviado: Seg Abr 20, 2020 8:13 pm
por johnfarlei
Prezados,
Tenho procurado bastante na internet uma forma de recuperar alguns dados de contas telefonicas do site da OI para automatizar um processo de cobrança.
Consegui realizar o código com Selenium, porém o site identifica a utilização de um robo e todas as vezes é obrigatorio utilizar o Capcha, o que não ocorre com o vba puro.

Tentei adaptar o código para vba puro, mas estou com problemas no processo de recuperar os dados. toda a consulta eu consegui realizar mas não consigo recuperar os dados e colar ele no excel em celulas especificas.

Se algume puder me ajudar com esta parte do código agradeço.

segue o Código em Selenium que estou usando:

Código: Selecionar todos

Option Explicit
Private driver As WebDriver

Sub ConsultaOi360()

Dim UltCel As Range
Dim W As Worksheet
Dim Ln As Long
Dim Col As Integer
Dim Contador As Integer


Application.ScreenUpdating = False

Set driver = New ChromeDriver

Set W = Sheets("Plan3")

W.Select

Ln = 2

Set UltCel = W.Cells(100000, 5).End(xlUp)

driver.Get ("https://www.oi.com.br/minha-oi/codigo-de-barras/")
Application.Wait Now + TimeValue("00:00:02")
            
'DoEvents

Do While Ln <= UltCel.Row
    'For Contador = 1 To 3
        If W.Cells(Ln, 20) = "" Then

'            driver.Get ("https://www.oi.com.br/minha-oi/codigo-de-barras/")
'            Application.Wait Now + TimeValue("00:00:02")
            driver.Refresh
            Application.Wait Now + TimeValue("00:00:03")
            'DoEvents
            
            'MsgBox "Aguardando site Oi carregar...!"
            
            driver.FindElementById("cpf_cliente").SendKeys W.Cells(Ln, 5).Text
            'DoEvents
            driver.FindElementById("btn_submit").Click
            Application.Wait Now + TimeValue("00:00:01")
            
            On Error Resume Next
            driver.FindElementByCss(".BuscaProdutoBloco").Click
            Application.Wait Now + TimeValue("00:00:01")
            
            MsgBox "Preencher/Confirmar Capcha...!"
            'DoEvents
            W.Cells(Ln, 20).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[2]/div/div[2]/strong").Text
            'DoEvents
            If Not driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 21).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 22).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
            End If
            
            If Not driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 23).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 24).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
            End If
            
            On Error Resume Next
            If Not driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 25).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 26).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 25).Value = "" Then
                    W.Cells(Ln, 25).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 26).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
            
            If Not driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 27).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 28).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 27).Value = "" Then
                    W.Cells(Ln, 27).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 28).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
            
            If Not driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 29).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 30).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 29).Value = "" Then
                    W.Cells(Ln, 29).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 30).Value = driver.FindElementByXPath("//*[@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
            
            'driver.Quit
        
            'Ln = Ln + 1
            'Application.ScreenUpdating = True
        End If
    Ln = Ln + 1
    'Next
Loop

driver.Quit

Application.ScreenUpdating = True

End Sub



Código VBA puro adaptado(não recupera os dados).

Código: Selecionar todos

'Inclui referência ao Microsoft Internet Controls
Sub lReferenciaIE()
    Dim ObRef
    On Error Resume Next

    ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1
End Sub

Sub ConsultaConta()
    'Inclui a referência se não houver
    lReferenciaIE
    
    Dim IE                  As InternetExplorer
    Dim lCPF                As String
    Dim lUltimaLinhaAtiva   As Long
    Dim lContador           As Long
    Dim W                   As Worksheet
    Dim Ln                  As Long
 
    
    'Identifica a última célula ativa da lista
    lUltimaLinhaAtiva = Worksheets("Plan1").Cells(Worksheets("Plan1").Rows.Count, 1).End(xlUp).Row
    
    'Cria um objeto Internet Explorer
    Set IE = New InternetExplorer
    Set W = Sheets("Plan1")
    Ln = 2
        
    'Torna o objeto visível
    IE.Visible = True

    'Faz um loop por todas as linhas da planilha
    For lContador = 2 To lUltimaLinhaAtiva
        'Navega ao site dos correios
        IE.Navigate "https://www.oi.com.br/minha-oi/codigo-de-barras/"
        
        'Identifica se a página já foi totalmente carregada
        While IE.ReadyState <> READYSTATE_COMPLETE
        Wend
        
       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 3 > Timer
        Loop
        
        'Carrega o CPF que será preenchido na Página
        lCPF = Range("A" & lContador).Value
                
        'Carrega os dados do CPF e submente os dados do formulário
        
        IE.Document.getElementById("cpf_cliente").Focus
        Application.SendKeys lCPF ', True
        
       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 2 > Timer
        Loop
        
        'Foca no Botão Emitir Código de Barras
        IE.Document.getElementById("btn_submit").Click

       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 10 > Timer
        Loop
        
      'Deveria recuperar os dados a partir daqui  
        If W.Cells(Ln, 20) = "" Then
           W.Cells(Ln, 20).Value = IE.FindElementByXPath("//*				 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[2]/div/div[2]/strong").Text
            'DoEvents
            If Not IE.FindElementByXPath("//*                                       
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 21).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 22).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
            End If
            
            If Not IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 23).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 24).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
            End If
            
            On Error Resume Next
            If Not IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 25).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 26).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 25).Value = "" Then
                    W.Cells(Ln, 25).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 26).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[2]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
            
            If Not IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 27).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 28).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[5]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 27).Value = "" Then
                    W.Cells(Ln, 27).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 28).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[3]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
            
            If Not IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/p").Value Then
                W.Cells(Ln, 29).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                W.Cells(Ln, 30).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[5]/div/div[6]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                If W.Cells(Ln, 29).Value = "" Then
                    W.Cells(Ln, 29).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/p").Text
                    W.Cells(Ln, 30).Value = IE.FindElementByXPath("//* 
             [@id=""__next""]/div/div/div/div[2]/section[3]/section/div/div[1]/div/div/div[6]/div/div[4]/div[1]/div[1]/div[1]/div/div[2]/div[1]").Text
                End If
            End If
          
        End If

        Ln = Ln + 1
        
        Next lContador
    
    MsgBox "Concluído!"
End Sub

Re: Obter dados de Pagina WEB

Enviado: Seg Mai 04, 2020 11:04 am
por johnfarlei
Apesar de perceber muita pesquisa e nenhuma resposta de ninguem aqui no Forum, eu consegui obter as informações que precisava.

Segue abaixo o Código sem utilizar Selenium funcionando.
Como a pagina em questão Utiliza Capcha Para validação, o Código aguarda o Capcha e não procegue se ele estiver sendo solicitado.

Código: Selecionar todos

'Inclui referência ao Microsoft Internet Controls
Sub lReferenciaIE()
    Dim ObRef
    On Error Resume Next

    ThisWorkbook.VBProject.References.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1
End Sub

Sub ConsultaConta()
    'Inclui a referência se não houver
    lReferenciaIE
    
    Dim IE                  As InternetExplorer
    Dim lCPF                As String
    Dim lUltimaLinhaAtiva   As Long
    Dim lContador           As Long
    Dim W                   As Worksheet
    Dim lRet                As String
    Dim objElement          As IHTMLElement
    Dim strElement          As String
    Dim Contar              As Integer
 
    
    'Identifica a última célula ativa da lista
    lUltimaLinhaAtiva = Worksheets("Plan1").Cells(Worksheets("Plan1").Rows.Count, 1).End(xlUp).Row
    
    'Cria um objeto Internet Explorer e seta os valores padrões
    Set IE = New InternetExplorer
    Set W = Sheets("Plan1")
    Contar = 2
    Item = 0
    
        
    'Torna o objeto visível
    IE.Visible = True

    'Faz um loop por todas as linhas da planilha
    For lContador = 2 To lUltimaLinhaAtiva
    
    'Torna o objeto visível
    IE.Visible = True
    
    
        'Navega ao site
        IE.Navigate "https://www.oi.com.br/minha-oi/codigo-de-barras/"
        
        'Identifica se a página já foi totalmente carregada
        While IE.ReadyState <> READYSTATE_COMPLETE
        Wend
       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 3 > Timer
        Loop
        
        'Carrega o CPF que será preenchido na Página
        lCPF = Range("A" & lContador).Value
                
        'Carrega os dados do CPF e submente os dados do formulário
        
        IE.Document.getElementById("cpf_cliente").Focus
        Application.SendKeys lCPF ', True
               
       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 1 > Timer
        Loop
        
        'Foca no Botão Emitir Código de Barras
        IE.Document.getElementById("btn_submit").Click
        
        
        lRet = "Vazio"
        
        
       'Aguarda retorno da pagina
        sng = Timer
        Do While sng + 3 > Timer
        Loop
        
            Do While lRet = "Vazio"
        
                   Set objElement = IE.Document.body.getElementsByTagName("h3").Item(1)
                   On Error Resume Next
      
                        lRet = objElement.innerText
                        
                   Set objElement = IE.Document.body.getElementsByClassName("Text__TextStyle-fp0yjz-0 byFGJl").Item(0)
                   On Error Resume Next
                   
                        lRet = objElement.innerText
                   
                   
                   Set objElement = IE.Document.body.getElementsByClassName("Text__TextStyle-fp0yjz-0 beCits").Item(0)
                   On Error Resume Next
                   
                        lRet = objElement.innerText
                        
                        
               'Aguarda retorno da pagina
                sng = Timer
                Do While sng + 1 > Timer
                Loop
           Loop
        
        
        On Error Resume Next
        
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(0).getElementsByClassName("Title__TitleStyle-sc-5b4olk-0 idUyWf").Item(0)
                W.Cells(lContador, 2) = objElement.innerText

            For Contar = 3 To 14
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(0).getElementsByClassName("Title__TitleStyle-sc-5b4olk-0 kCdfIG").Item(Item)
                W.Cells(lContador, Contar) = objElement.innerText
                
                Contar = Contar + 1
        
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(0).getElementsByClassName("Text__TextStyle-fp0yjz-0 hBikh").Item(Item)
                W.Cells(lContador, Contar) = objElement.innerText
                                
                Item = Item + 1
            Next Contar
            '_________________
                        Item = 0
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(1).getElementsByClassName("Title__TitleStyle-sc-5b4olk-0 idUyWf").Item(0)
                W.Cells(lContador, 15) = objElement.innerText
                Contar = Contar + 1
                
            For Contar = 16 To 28
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(1).getElementsByClassName("Title__TitleStyle-sc-5b4olk-0 kCdfIG").Item(Item)
                W.Cells(lContador, Contar) = objElement.innerText
                
                Contar = Contar + 1
        
                Set objElement = IE.Document.body.getElementsByClassName("styled__Col-sc-4ph28m-0 cEyrnf sc-1paz868-1 eIbqKd").Item(1).getElementsByClassName("Text__TextStyle-fp0yjz-0 hBikh").Item(Item)
                W.Cells(lContador, Contar) = objElement.innerText
                                
                Item = Item + 1
            Next Contar
           
          Item = 0
    
    Next lContador
          
          MsgBox "Concluído!"
End Sub
Todos os horários são UTC-03:00
Página 1 de 1

Powered by phpBB® Forum Software © phpBB Limited

Traduzido por: Suporte phpBB