Cadastro de Clientes no Excel com Consulta de Endereço pelo CEP na Internet

Nesta planilha pronta de cadastro de clientes no Excel com consulta de endereço pelo CEP na Internet.

Com uso de VBA você terá uma planilha gratuita de cadastro de clientes pronta e disponível para customizar o que precisar, pois é desbloqueada e ensino passo-a-passo como criá-la do zero.

No exemplo de cadastro são cadastrados os seguintes campos:

Cadastro de clientes excel com endereço automático web

Os campos Nome, CEP e NR. são preenchidos pelo cliente, ao preencher o CEP os dados são automaticamente preenchidos pelo Excel.

Estes dados retornarão da WEB via uma consulta XML com WebService e uma integração com VBA no Excel.

Veja abaixo como ela funciona e no final pode baixar o sistema pronto.

No vídeo no topo do artigo você também pode ver a aula criando a mesma desde o zero.

Cadastro de clientes com VBA e consulta de endereço WEB

A estrutura da pasta de trabalho Excel é composta por três planilhas:

Formulário: Formulário aonde serão digitados os dados para o cadastro do cliente.
Consulta: Planilha auxiliar que serve para retornar a consulta WEB com a consulta pelo CEP pelo Excel.
Banco: Os dados digitados no formulário são transferidos automaticamente para o banco pelo clique no botão de Inserir do formulário.

Formulário:

Cadastro de Clientes no Excel com Consulta de Endereço pelo CEP na Internet

Em branco estão os campos que precisam ser preenchidos manualmente e em cinza os campos retornados automaticamente à partir da digitação do CEP.

O endereço é então retornados automaticamente da internet para o Excel.

A consulta do endereço é realizada na planilha Consulta.

Consulta:

Os campos em azul que estão acima são retornados dinamicamente por uma consulta de XML no endereço: http://republicavirtual.com.br/web_cep.php?cep=89201280

Cole este endereço no seu navegador para verificar o retorno.

Esta é uma consulta que retorna um XML do endereço pelo CEP para o Excel e estes dados são tratados e utilizados no cadastro de clientes.

Banco:

O banco é aonde os dados são cadastrados ao clicar no botão Inserir no formulário de cadastro e clientes.

Caso queira pode incluir novos campos no cadastro seguindo o passo-a-passo e adaptar facilmente seguindo a vídeo-aula no início deste artigo.

Consulta de Endereço na Internet pelo CEP no Excel

Inicialmente precisamos realizar uma consulta na internet à partir de um CEP qualquer e retornar estes dados para o Excel.

À partir dos dados do endereço retornado para o Excel iremos realizar adaptações para que estes dados sejam retornados automaticamente para o formulário.

A primeira etapa é então clicar na guia Desenvolvedor e clicar no botão Código Fonte que fica no grupo XML.

Nele clique no botão Mapas XML… e no botão Adicionar.

Coloque o seguinte endereço no nome do arquivo: http://republicavirtual.com.br/web_cep.php?cep=89201280

Após isto clique em Abrir, o sistema irá então criar um mapa automaticamente com os dados do endereço retornado pelo XML na internet e retornará para o Excel.

Ficará algo parecido com o mapa abaixo:

Então selecione uma célula aonde deseja colocar a informação e clique com o botão direito sobre cada campo que desejar adicionar.

Clique em Mapear Elemento, como sugestão adicione apenas os campos que sempre existirão: UF, CIDADE, BAIRRO, TIPO_LOGRADOURO e LOGRADOURO.

No nosso exemplo colocamos dispostos desta forma as informações, colocando então em uma coluna anterior a descrição de cada informação.

A nossa consulta está pronta, agora iremos fazer com que ela fique dinâmica.

Consultar endereço pelo CEP na internet VBA Excel

Com as informações anteriores já conseguimos consultar o endereço pelo CEP, mas precisamos mudar o CEP de acordo com o cadastro de clientes.

Então para isso clique ALT+F11 para abrir o Editor VBA.

Clique em Inserir->Módulo e cole o seguinte código fonte:

Sub lsConsultaCEP(ByVal vstrCEP As String)
    ActiveWorkbook.XmlMaps("webservicecep_Mapa").Import Url:= _
        "http://republicavirtual.com.br/web_cep.php?cep=" & vstrCEP
End Sub

No código acima altere a parte webservicecep_Mapa pelo nome do mapa criado quando mapeou o endereço. Veja no topo desta imagem:

Agora na planilha de formulário inclua o seguinte código:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$13" Then
        lsConsultaCEP Target.Value
    End If
End Sub

Este código faz com que a consulta CEP seja realizada no momento em que o valor da célula H13 é alterado.

Se a célula H13 que é a célula referente ao CEP for alterada o endereço é automaticamente consultado pelo procedimento VBA de consulta que criamos, alterando então o CEP e retornando o endereço para o Excel.

Alterando o Cadastro de Clientes para Retornar o Endereço

A parte do formulário é a mais fácil para adaptar, basta digitar = e clicar na planilha Consulta para retornar a informação de cada campo correspondente.

No campo de código para trazer o próximo número basta retornar a informação Máximo à partir do Banco e somar 1: =MÁXIMO(tbCadastro[Código])+1.

Cadastro de clientes VBA Excel

Para adicionar os dados do cliente cadastrado abra o VBA pressionando ALT+F11.

Adicione no módulo já criado o seguinte código fonte:

Public Sub lsInserirDados()
    Dim lRow As Long
    
    'Próxima Linha
    lRow = Banco.Cells(Rows.Count, 2).End(xlUp).Row + 1
    
    'Inserir os dados no cadastro
    Banco.Cells(lRow, 2).Value = UCase(Formulario.Range("H9").Value)
    Banco.Cells(lRow, 3).Value = UCase(Formulario.Range("H11").Value)
    Banco.Cells(lRow, 4).Value = UCase(Formulario.Range("H13").Value)
    Banco.Cells(lRow, 5).Value = UCase(Formulario.Range("H15").Value)
    Banco.Cells(lRow, 6).Value = UCase(Formulario.Range("J15").Value)
    Banco.Cells(lRow, 7).Value = UCase(Formulario.Range("S15").Value)
    Banco.Cells(lRow, 8).Value = UCase(Formulario.Range("H17").Value)
    Banco.Cells(lRow, 9).Value = UCase(Formulario.Range("H19").Value)
    Banco.Cells(lRow, 10).Value = UCase(Formulario.Range("S19").Value)
    
    'Limpar formulário de cadasto de clientes
    Formulario.Range("H11").Value = ""
    Formulario.Range("H13").Value = ""
    Formulario.Range("S15").Value = ""
    
    'Mensagem de cadastro concluído
    MsgBox "Cliente cadastrado!"
    
End Sub

O código já está devidamente comentado e na vídeo-aula no topo deste artigo é explicado também todos os pontos deste código.

Mas basicamente ele verifica qual a última linha do cadastro, inclui os dados do formulário e limpa os campos do formulário para que possa ser realizado um novo cadastro cliente.