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
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.
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.
Com isso você irá para um ambiente de programação VBA.
Abaixo, mostro um pouco das ferramentas para familiarização com a nova tela.
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 StringEnd Type
Public Sub executaTeste()
‘ Aqui estou criando uma chamada para um forme que iremos criar em sequencia.
frmTestaConexao.Show 1End 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.
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 = 0Else
sPing.descricao = “Sucesso”
sPing.bufferSize = oRetStatus.bufferSize
sPing.bufferTime = oRetStatus.ResponseTime
sPing.TTL = oRetStatus.ResponseTimeToLiveEnd If
Next
End Function
Private Sub cmdReTestar_Click()
testaRede
End SubPrivate Sub cmdSair_Click()
Unload MeEnd 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 Integerlinha = 4
qtdErros = 0
While Plan15.Cells(linha, 1) <> “”
lblTexto.Caption = “Testando conexão servidor: ” & Plan15.Cells(linha, 1) & ” – ” & Plan15.Cells(linha, 2)
DoEventsdados = 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 = vbRedqtdErros = qtdErros + 1
End Iflinha = linha + 1
WendIf CInt(qtdErros) = 0 Then
lblTexto.Caption = “Testes Concluido com SUCESSO.”
lblTexto.ForeColor = vbBlack
Else
lblTexto.Caption = “Testes Concluido, com ERROS.”
lblTexto.ForeColor = vbRed
End IfEnd 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.
33 Comentários
Demais esse código!
Parabéns Du!
Como sempre se mostrando um profissional de TI brilhante!
Forte abraço!
Show!!! Muito grato por compartilhar.
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.
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.
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
Estou tentando executar a planilha em um sistema operacional 64 bits e não esta dando, como poderia resolver isso!!!
Parabéns, excelente dica!
muito bom script.. parabens
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
Muito bom! Parabéns pela postagem.
Boa tarde
Baixei mas pede para modificar para windows e office 64, oque tenho que modificar.
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.
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?
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.
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.
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.
Gostaria de enviar pacote UDP pelo excel, não achei informações ainda. Achas que é possível?
Obrigado.
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.
Excelente código!! Parabéns ao autor.
Oii, eu nao consegui usar no mac (64bit) consegue me ajudar?
Como eu consigo, através do vba, registrar na planilha a velocidade da internet?
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
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á
Boa tarde, amigos!
Teria como deixar o teste rodando em tempo integral?
Boa noite! É possível adicnionar um botão de pausa a este código? Se sim, mostrar como por favor!!!
Grrato!!
Amigão, não consegui baixar a planilha pelo link fornecido, poderia me enviar por e-mail?
o sr faria a gentileza de me enviar cópia de sua planilha?
Olá gostaria da planilha também mas está com erro..
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!
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.
Boa noite! Amigo o seu código nao roda em excel 32 bits.
So corrigindo so roda em 32 bits. Em Excel 64 tem bug