Criando um Ping Tester no VBA do Excel

E ae pessoal tudo bem com vocês?

Estou aqui escrevendo meu primeiro artigo no portal, e este tem por base uma necessidade que passei e contornei sem custos. Estava precisando de um software para monitorar alguns serviços aqui na empresa em que trabalho, um software que poderia verificar se meus servidores, alguns aparelhos VoIPs e impressoras estão conectados na rede ou por algum motivo desconectado.

Imagem via Shutterstock

Imagem via Shutterstock

Começei uma busca na internet, encontrei softwares bons e ruins, mas a grande maioria pagos. Não queria investir em um software deste ramo, visto que era um simples “PING” para um endereço. Bom, então foi aí que busquei meus 12 anos em desenvolvimento de sistemas e resolvi criar um “Ping Tester no Excel”, onde poderia monitorar se algum serviço está desconectado ou não.

Bom vamos lá, no passo a passo da criação.

1º Passo – Criar o layout da Planilha

Vamos criar uma planilha conforme a imagem demonstrada abaixo, com colunas de IP, descrição do Equipamento e o Status de conectado ou não.

planilhaExcel

2º Passo – Vamos Programar

Para programar dentro do Excel utilizamos a linguagem VBA (Visual Basic for Application), uma linguagem muito parecida com Visual Basic, que tem um mercado muito interessante em muitas regiões.

Para acessar o ambiente de programação VBA no excel, vá na guia “Desenvolvedor” e clique no botão “Visual Basic” como demonstrado abaixo.

visualBasic

Com isso você irá para um ambiente de programação VBA.

Abaixo, mostro um pouco das ferramentas para familiarização com a nova tela.

tools

2.1 – Adicionando um módulo

Bom, com isso podemos programar. Comece clicando no botão “Novo”, e escolha a opção “Módulo”, automaticamente será adicionado um novo modulo no “project explorer”. Clique duas vezes neste módulo e vamos começar a digitar nossas linhas de programação.

Public Type ping

                ‘ Aqui estamos criando um tipo de variavel novo, este tipo de variavel se chamará “PING”
                ‘E este tipo de variavel terá uma descrição um tamanho de buffer e outros itens interessantes.

               descricao As String
bufferSize As String
bufferTime As String
TTL As String

End Type

Public Sub executaTeste()

‘ Aqui estou criando uma chamada para um forme que iremos criar em sequencia.
frmTestaConexao.Show 1

End Sub

2.2 – Adicionando um Formulário

Agora vamos clicar novamente no botão “Novo” e escolher formulário. Note que em seu “project explorer” será incluído um novo formulário.

Clique sobre o formulário e na janela de propriedades, localizada abaixo do “project explerer”, altere a propriedade “(name)” para frmTestaConexao.

Agora vamos desenhar a tela conforme demonstrada abaixo.

design

Após desenhar o layout acima, é preciso dar os nomes corretos aos botões.

Clique uma vez sobre o botão “Iniciar Teste” e altere sua propriedade “Name” para “cmdReTestar”. Depois clique sobre o fechar e altere seu “Name” para “cmdSair”. Clique no label e altere a propriedade “Name” para “lblTexto” e “caption” para “Sistema preparado para testar.”

Com o procedimento acima realizado, clique no botão alterar o modulo para programação e informe a programação conforme abaixo:

Function sPing(sHost) As ping

Dim oPing As Object, oRetStatus As Object

Set oPing = GetObject(“winmgmts:{impersonationLevel=impersonate}”).ExecQuery _
(“select * from Win32_PingStatus where address = ‘” & sHost & “‘”)

For Each oRetStatus In oPing

If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then

sPing.descricao = “”
sPing.bufferTime = 0

Else

sPing.descricao = “Sucesso”
sPing.bufferSize = oRetStatus.bufferSize
sPing.bufferTime = oRetStatus.ResponseTime
sPing.TTL = oRetStatus.ResponseTimeToLive

End If

Next

End Function

Private Sub cmdReTestar_Click()
testaRede
End Sub

Private Sub cmdSair_Click()
Unload Me

End Sub

 

Private Sub testaRede()
‘limpa os status
lblTexto.ForeColor = vbBlack
Plan15.Range(“C4”, “C500”) = “”

Dim linha As Integer
Dim dados As ping
Dim qtdErros As Integer

linha = 4
qtdErros = 0
While Plan15.Cells(linha, 1) <> “”
lblTexto.Caption = “Testando conexão servidor: ” & Plan15.Cells(linha, 1) & ” – ” & Plan15.Cells(linha, 2)
DoEvents

dados = sPing(Plan15.Cells(linha, 1))
If Not Trim(dados.descricao) = “” Then
Plan15.Cells(linha, 3) = dados.descricao & ” – ” & dados.bufferTime & “ms”
Plan15.Cells(linha, 3).Font.Color = vbGreen
lblTexto.ForeColor = vbBlack
Else
Plan15.Cells(linha, 3) = “Erro”
Plan15.Cells(linha, 3).Font.Color = vbRed
lblTexto.ForeColor = vbRed

qtdErros = qtdErros + 1
End If

linha = linha + 1
Wend

If CInt(qtdErros) = 0 Then
lblTexto.Caption = “Testes Concluido com SUCESSO.”
lblTexto.ForeColor = vbBlack
Else
lblTexto.Caption = “Testes Concluido, com ERROS.”
lblTexto.ForeColor = vbRed
End If

End Sub

Com isso temos a programação do nosso “ping tester” pronta, agora podemos inserir qualquer componente na planilha do Excel e criar um vinculo com a macro “executaTeste” que criamos no modulo.

Vocês podem fazer o download da planilha clicando aqui.

Espero que tenham gostado do primeiro post. Deixem abaixo seus comentários com complementos e dúvidas.

Abraços.

Eduardo Chiaradia

Mais artigos deste autor »

Estudante de Mestrado do ITA, formado em Administração de empresas pelo ITES, cursei MBA em gerencia de TI na Unitau e com pouco mais de 14 anos na área de TI, onde comecei aos meus 14 anos como estagiário na área de desenvolvimento de sistemas, fui programador, analista de sistemas, DBA, desenvolvedor front-end, back-end, coordenador, Analista de TI, Analista de implantação, ou seja passei por varias áreas desse mundo de TI, e busco a cada dia um novo desafio.


33 Comentários

Marcelo Almeida
2

Parabéns Du!
Como sempre se mostrando um profissional de TI brilhante!
Forte abraço!

Lucas Alcântara
4

Excelente Script, Eduardo. Parabéns.
Gostaria de abusar da sua boa vontade e te fazer uma pergunta: o que devo modificar para que o botão “Executar teste de conexão” já realize diretamente o teste e a caixa apareça apenas para mostrar o processamento?
Um grande abraço e muito obrigado por compartilhar o seu conhecimento.

Eduardo Chiaradia
5

Boa Tarde Lucas Alcântra,
A linguagem VBA é uma linguagem orientada a eventos, a função que está dentro do evento “click” do botao você pode copia-la para o evento “inicialize” do formulario, ou pode inserir uma chamada do botão neste evento inicialize do form, como mostro abaixo:
Private Sub UserForm_Initialize()
cmdReTestar_Click
End Sub
Abraço.

Luis Vagner
6

Boa noite!
Quando abro o arquivo está apresentando os seguintes erros:
Erro de compilação: è impossível localizar o projeto ou a biblioteca
Para bem aqui:
Private Sub Workbook_Open()
Plan12.Activate
End Sub

Joao Roberto
7

Estou tentando executar a planilha em um sistema operacional 64 bits e não esta dando, como poderia resolver isso!!!

JohnBoy
10

Muito bom esse esquema do ping, parabéns, fiz somente algumas alterações, como tenho 3 redes, tenho 3 abas no excel, então fiz o seguinte.
– Substitui “Plan5.” por “ActiveSheet.”
– Coloquei um label no form (não sei criar variável publica) chamado lbStatus, com o texto inicial “Parado”.
no Botão Executar coloquei
lbStatus.Caption = “Executando”
TestaRede
no Botão Fechar coloquei
lbStatus.Caption = “Parado”
Unload Me
no while coloquei
While ActiveSheet.Cells(linha, 1) “” And lbStatus.Caption = “Executando”
Também coloquei logo embaixo do meu botâo (Célula H5 no meu caso), a data e hora da última execução.
ActiveSheet.Range(“H9”, “H262”) = “” (esse já tinha)
ActiveSheet.Cells(5, 8) = Now (esse eu coloquei).
Abraço
Johm

Renato Santos
13

Bom Dia.
Para quem obteve erro e esta utilizando a versão 64 bits do pacote office. Vá em menu Ferramentas e haverá a opção Referencias, basta desabilitar todas as caixas, algumas não possibilitara esta opção. Depois confirme a seleção e execute novamente. Espero ter ajudado.

Glaydson
14

Olá amigo, gostaria de sua ajuda: Onde se encontra o botão “Alterar o módulo de programação que você citou acima? Desculpe, não entendo muito de vba. Poderia me ajudar dando mais detalhes? inlcuindo a parte de linkar com o componente da planilha?

Ana
15

Eduardo, boa tarde! Excelente código. Obrigada. Porém, gostaria de contar com sua ajuda para que eu possa adaptar à seguinte situação: Eu tenho uma lista enorme de hostnames e preciso testar o estado de conexão e também, alimentar uma coluna suplementar, com os endereços IP destes hosts. Como eu faria isso? Obrigada e no aguardo.

Felipe
16

Então tentei fazer a tabela e tudo mais, mas não consegui kk. Minha duvida é a seguinte, como faço as mudanças para verificar outros IP’s, por que aqui me diz que as macros e a biblioteca não esta sendo encontrada.

Nivaldo
17

Boa noite!
Também estou com o seguinte erro:
Quando abro o arquivo está apresentando os seguintes erros:
Erro de compilação: è impossível localizar o projeto ou a biblioteca
Sou novo em programação VBA, poderia me dar uma dica de como resolver?
Obrigado.

Nivaldo
18

Gostaria de enviar pacote UDP pelo excel, não achei informações ainda. Achas que é possível?
Obrigado.

Mauro
19

Bom dia, amigos!
Sou novo em programação VBA, e gostaria muito de colocar essa planilha para funcionar!!!
Tentei utilizar seu código para verificar algumas máquinas da minha rede, mas também estou tendo problemas ao executar o teste, onde sempre aparece o seguinte erro:
“Erro de compilação: é impossível localizar o projeto ou a biblioteca”
e quando fecho a mensagem, o texto “TRIM” (no código abaixo) fica selecionado:
if Not Trim(dados.descricao) = “” Then
Será que alguém poderia me dar ajudar a resolver?
Obrigado.

Germano
23

Parabéns Eduardo!
baixei a sua planilha para testar aqui.
Alterei os IP, porém quando chega no For Each oRetStatus In oPing dá um erro
“Não é possível criar mais Threads no sistema. utilizo o excel 2003
Grato

Franco
24

Bom dia, achei muito interessante sua programação. Gostaria de saber se já fez algum exemplo para troca de dados TCP, enviando e recebendo algo para IP e Porta específicos.
Obrigado desde já

Daniel
26

Boa noite! É possível adicnionar um botão de pausa a este código? Se sim, mostrar como por favor!!!
Grrato!!

Fagner
27

Amigão, não consegui baixar a planilha pelo link fornecido, poderia me enviar por e-mail?

Alisson
30

Parabéns pelo excelente trabalho Eduardo!!

Gostei demais da planilha!!

Gostaria de saber se é possível e como faço para alterar a quantidade de solicitações (opção “-n” do prompt) do ping para 5.

Obrigado por compartilhar seu conhecimento com o mundo!

Gilberto
31

Bom dia..

Eduardo obrigado pelo código facilitou muito uma rotina que eu fazia, tenho uma duvida gostaria que me ajudasse se possível logico ou quem estiver lendo eu preciso que na coluna ” D ” na frente do status fosse retornado o numero MAC do equipamento no cmd consigo com comando ARP pingando no ip.. teria como rodar junto com esse código ????

Obrigado.

Deixe seu comentário

Seu endereço de e-mail não será publicado. Campos com * são obrigatórios!