Passo-a-passo de como criar um cadastro de clientes automático em Excel usando VBA e consultando endereço dos Correios automaticamente.
Estrutura do Cadastro de Clientes no Excel
No nosso projeto iremos usar uma tabela que será alimentada pelos dados digitados em uma estrutura de campos.
Esta acima é a estrutura aonde serão digitados os dados que serão alimentados na tabela pelo clique de um botão que aciona um VBA que iremos gravar.
E a estrutura abaixo é como é o cadastro.
Para criar igual basta preencher os nomes dos campos que deseja no topo e formatar as linhas e cabeçalhos.
E para a tabela copie a mesma estrutura e pressione ALT+T+T+A para que seja formatado como tabela, ou na guia Inserir->Tabela.
Após isso já podemos passar ao ponto seguinte que é o preenchimento automático do endereço por meio de uma consulta web de API que retorna estes dados.
Na célula ID usamos a função =MÁXIMO(Tabela2[ID])+1, aonde é retornado o último número de ID + 1, para assim termos o próximo código de cliente.
Consultar Endereço Pelo CEP no Excel
Para realizar a consulta de endereços pelo CEP usamos o site viacep.
O endereço é viacep.com.br.
Como teste digite no seu navegador viacep.com.br/ws/84025480/xml
Veja que na consulta passamos o número do CEP e o retorno é um XML com os dados:
- CEP
- LOGRADOURO
- COMPLEMENTO
- BAIRRO
- LOCALIDADE
- UF
- IBGE
- GIA
- DDD
- SIAFI
Estes dados podem ser também retornados todos para o Excel como veremos.
Consultar Endereço no Excel
Para retornar uma consulta de API no Excel use a função ServiçoWEB, veja abaixo que o único parâmetro que passamos é o endereço.
O retorno que temos no Excel é o XML no formato de texto.
Veja que temos então o mesmo XML do navegador retornado no Excel.
Então usamos a função FILTROXML para filtrar e retornar somente os dados que desejamos.
=FILTROXML(F15;”//uf”).
Os parâmetros que passamos para a função é o XML que desejamos consultar alguma informação e o segundo é // e o nome do campo, no caso temos acima é a UF.
Então temos o retorno somente do que desejamos conforme abaixo.
Com isso conseguimos juntar as funções ServiçoWEB e FiltroXML com uma concatenação da célula com o campo do CEP e da consulta que desejamos, tendo então:
=SEERRO(FILTROXML(SEERRO(SERVIÇOWEB(“viacep.com.br/ws/”&G6&”/xml/”);””);”//uf”);””)
No nosso exemplo colocamos a função SERVIÇOWEB separada, pois desejamos retornar vários campos e com isso a consulta é feita na web apenas uma vez.
Com isso reutilizamos o retorno da função SERVIÇOWEB e temos a fórmula: =SEERRO(FILTROXML($C$3;”//uf”);””), trocando apenas conforme o retorno que desejamos.
Por exemplo, para a cidade usamos: =SEERRO(FILTROXML($C$3;”//localidade”);””) e assim por diante.
Temos então o seguinte resultado:
Cadastro de Clientes com VBA no Excel
Agora iremos automatizar o cadastro de clientes de modo que ao digitar os dados no topo e clicar em um botão os mesmos serão inseridos na tabela.
O primeiro passo é habilitar a guia Desenvolvedor.
Para isso clique com o botão direito sobre o menu em qualquer parte em branco e selecione Opções do Excel.
Marque a opção de Guia Desenvolvedor.
Então clique na guia Desenvolvedor e em Gravar Macro.
Digite o nome da sua macro, iremos gravar os passos que serão realizados.
Após clicar em OK, tudo que está sendo feito será gravado.
Agora acompanhe no vídeo ao topo do artigo como gravar, pois tem uma questão de referência relativa e absoluta importante aqui.
Basicamente copie os dados, navegue com as setas para a última linha da planilha e depois pra última preenchida e clique seta pra baixo.
Cole a informação.
Clique em parar gravação.
Pressionando ALT+F11 conseguirá ver os códigos gerados conforme vemos na imagem.
Clique em Inserir->Imagem e desenhe um botão com o retângulo e digite o texto nele.
Clique com o botão direito do mouse e selecione Atribuir Macro, selecione a macro gerada.
Adicione um texto e clique no botão, o processo deverá inserir então os dados na última linha do cadastro de clientes do Excel.
Download Planilha de Cadastro de Clientes Automática Excel
Clique no botão abaixo para realizar o download da planilha de senha de abertura no Excel, com exemplo de dados: