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