Obter dados de Pagina WEB
Enviado: Seg Abr 20, 2020 8:13 pm
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 VBA puro adaptado(não recupera os dados).
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