How to close an alert in IE in with VBA

Question

How to close an alert in IE in with VBA

Navigation

#1 by (0 votes)

1

I have an Excel macro that connects to a local tool and extracts the information. Basically I take a report according to the user. I have a column with the users, however if the user does not present results in one day the tool triggers an alert and application to in the loop. I already tried to use IE.Sendkeys "{ENTER"} fault% error as on error GoTo and did not get results.

OnErrorResumeNextDimIEAsObjectPlan1.Range("A5:L1000000").ClearContents

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "https://link portal"

    While IE.Busy Or IE.ReadyState <> 4
    Wend

IE.document.getElementById("txtUsuario").Value = "user"
IE.document.getElementById("txtSenha").Value = "pass"
IE.document.getElementById("btnLogar").Click

    While IE.Busy Or IE.ReadyState <> 4
    Wend

IE.Navigate "https://link para extrair relatorio"

While IE.Busy Or IE.ReadyState <> 4
    Wend

Dim i As Integer
aux = 2
Dim cont As Integer
cont = 5

While Plan2.Range("A" & aux).Value <> ""

    IE.document.getElementById("txtAgente").Value = Plan2.Range("A" & aux).Value
    IE.document.getElementById("calDataInicial_DateText").Value = Me.TextBox1.Text
    IE.document.getElementById("calDataFinal_DateText").Value = Me.TextBox1.Text
    IE.document.getElementById("imgPesquisar").Click


    While IE.Busy Or IE.ReadyState <> 4
        Wend 'a execução é interrompida aqui

   Set tabela = IE.document.all.tags("table")(7)

    For Each linha In tabela.all.tags("tr")

            Data = linha.all.tags("td")(0).innertext
            cpf = linha.all.tags("td")(1).innertext
            fila = linha.all.tags("td")(2).innertext
            operacao = linha.all.tags("td")(3).innertext
            contrato = linha.all.tags("td")(4).innertext
            agente = linha.all.tags("td")(5).innertext
            telefone = linha.all.tags("td")(6).innertext
            extensao = linha.all.tags("td")(7).innertext
            data_pagto = linha.all.tags("td")(8).innertext
            data_agen = linha.all.tags("td")(9).innertext
            motivo = linha.all.tags("td")(10).innertext
            obs = linha.all.tags("td")(11).innertext

            linha.MoveNext
            Plan1.Range("A" & cont) = Data
            Plan1.Range("B" & cont) = cpf
            Plan1.Range("C" & cont) = fila
            Plan1.Range("D" & cont) = operacao
            Plan1.Range("E" & cont) = contrato
            Plan1.Range("F" & cont) = agente
            Plan1.Range("G" & cont) = telefone
            Plan1.Range("H" & cont) = extensao
            Plan1.Range("I" & cont) = data_pagto
           ' Plan1.Range("J" & cont) = data_agen
            Plan1.Range("J" & cont) = motivo
            Plan1.Range("K" & cont) = obs

            cont = cont + 1
        Next

        aux = aux + 1

 Wend
 IE.Quit
 IE.nothing

macro excel vba internet-explorer-9

asked by anonymous 26.02.2018 / 19:34

1 answer

When to use AsyncTask, ThreadPoolExecutor or Service? Move sprite to the position of the mouse with Phaser

score 0 · Answer 1

I've researched once and found no way to hit the error button with VBA, maybe using AutoIt to accomplish this . However, the recommended one is the handling of the errors, the solution of these is better for the program ...

I usually use an SOen function to perform the wait time for the page to load completely, only While IE.Busy Or IE.ReadyState <> 4: Wend may not be enough.

'Inicia Cabeçalho
'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 

'Funções iniciam aqui

'Inserir sua função após as declarações

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

So instead of While IE.Busy Or IE.ReadyState <> 4: Wend You call the EsperaIE function and choose the wait time in ms. Example: EsperaIE IE, 5000

This is a common mistake of using IE in VBA because the page was not loaded completely and the code continued to roll.

An alternative to the Sleep function is Application.Wait