Cadastro em Excel com banco de dados Access – SQL

Cadastro em Excel com banco de dados Access – SQL

Neste artigo você verá como criar um cadastro com interface Excel e acesso ao banco de dados Access. O artigo demonstra desde a criação do banco de dados até a criação das instruções de consulta, inserção, alteração e exclusão do banco de dados.

Construindo o banco de dados no Access

O nosso objetivo não é ensinar tudo sobre banco de dados, apenas dar um exemplo prático e adaptável de um sistema com cadastro no Excel com banco de dados Access.

Então você verá aqui apenas os passos necessários para a criação do sistema.

Iniciando você precisará ter uma versão do Excel e o Access, no nosso caso utilizamos o Access 2016, mas qualquer versão acima do 2007 inclusive, pode ser utilizada para a construção do exemplo.

Para a construção do banco de dados siga os passos:

  1. Abra o Access;
  2. Selecione a opção Banco de dados em branco;
  3. Na tela seguinte coloque um nome para o seu banco de dados, no nosso caso chamamos de SistemaGE, e clique no botão para criar;
  4. Na tela seguinte clique em Salvar, o sistema irá pedir um nome para a sua tabela, digite Clientes;
  5. Agora crie os campos conforme a tabela abaixo:
    Excel com banco de dados Access - SQL 1

  6. Agora o nosso banco de dados já está pronto. Faremos no Excel o código VBA para manipular e retornar as informações desta tabela do banco de dados SistemaGE.

Criação da interface do cadastro no Excel

No Excel criaremos um cadastro em formato de tabela, a ideia é de que o usuário possa fazer ações rapidamente e em massa.

Por exemplo inserir vários registros no banco de dados de uma só vez, ou fazer várias alterações no banco, inclusões e exclusões e ao final clicar em atualizar dados e todos os dados serem populados no banco de forma super rápida e segura.

Siga os passos seguintes para criarmos a estrutura da tabela que armazenará os dados.

  1. Na célula A1 digite Cadastro de clientes e formate conforme a imagem acima
  2. Selecione as células de A1 á H1 e formate o alinhamento horizontal com a opção Centralizar seleção. Sempre que possível evite utilizar o mesclar células.
  3. Digite nas células dos cabeçalhos, logo após o nome da tabela os campos
    1. Código
    2. Nome
    3. Logradouro
    4. Número
    5. Bairro
    6. Cidade
    7. UF
    8. Ação
  4. Selecione as células de H3 á H1048576
  5. Clique no menu em Dados->Validação de dados, selecione Lista e digite em fonte: Inserir;Alterar;Excluir
  6. Selecione a célula A3 e na guia Página Inicial->Formatação condicional->Nova regra
  7. Selecione a última opção “Usar uma fórmula para determinar quais células devem ser formatadas”
  8. Digite a fórmula: =E($B3<>””;MOD(LIN(); 2)=1), esta fórmula faz com que se a célula da coluna B estiver diferente de vazia e a célula for ímpar ela receba a formatação
  9. Ainda nesta tela selecione Formatar e altere o preenchimento e a borda, no nosso caso colocamos um tom de azul claro no preenchimento e a borda em um tom de azul mais forte
  10. Clique em OK e no Gerenciador de regras de formatação condicional altere o intervalo aplica-se a para o intervalo de A3:H1048576 conforme a imagem:
  11. Repita a fórmula para as células
  12. Desta forma já temos o cadastro pronto, apenas formate ele conforme a imagem no início desta seção.

Criando os códigos em VBA para o Cadastro Access e Excel

Nesta seção iremos criar a conexão com o banco de dados Access e realizaremos a manipulação dos dados do banco de dados e o retorno destes dados para o nosso cadastro de clientes.

Para isso siga os passos seguintes.

Dim gConexao As ADODB.Connection

Private Sub lsConectar()
    Dim strConexao As String
    Set gConexao = New ADODB.Connection
    
    strConexao = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Rieper\Blog Guia do Excel\Excel com banco de dados Access - SQL\SistemaGdE.accdb;Persist Security Info=False"
    
    gConexao.Open strConexao
End Sub

Private Sub lsDesconectar()
    If Not gConexao Is Nothing Then
        gConexao.Close
        Set gConexao = Nothing
    End If
End Sub

Public Sub lsInserir(ByVal ldes_nome As String, ByVal ldes_logradouro As String, ByVal lnum_nrLogradouro As Long, _
                      ByVal ldes_bairro As String, ByVal ldes_cidade As String, ByVal ldes_uf As String)
    Dim lSQL As String
                      
    lsConectar
    lSQL = "INSERT INTO Clientes ( des_nome, des_logradouro, num_nrLogradouro, des_bairro, des_cidade, des_uf )" & _
    "VALUES ( """ & ldes_nome & """,""" & ldes_logradouro & """," & lnum_nrLogradouro & ",""" & ldes_bairro & """,""" & ldes_cidade & """,""" & ldes_uf & """)"
    
    gConexao.Execute lSQL
    lsDesconectar
End Sub

Public Sub lsAlterar(ByVal ldes_nome As String, ByVal ldes_logradouro As String, ByVal lnum_nrLogradouro As Long, _
                      ByVal ldes_bairro As String, ByVal ldes_cidade As String, ByVal ldes_uf As String, ByVal lClientes_ID As Long)
    Dim lSQL As String
                      
    If lClientes_ID > 0 Then
        lsConectar
        lSQL = "UPDATE Clientes SET des_nome = """ & ldes_nome & """, des_logradouro = """ & ldes_logradouro & """," & _
               " num_nrLogradouro = " & lnum_nrLogradouro & ", des_bairro = """ & ldes_bairro & """, des_cidade = """ & _
               ldes_cidade & """, des_uf = """ & ldes_uf & """ where Clientes_ID = " & lClientes_ID
        
        gConexao.Execute lSQL
        lsDesconectar
    End If
End Sub

Public Sub lsExcluir(ByVal lClientes_ID As Long)
    Dim lSQL
    
    If lClientes_ID > 0 Then
        lsConectar
        
        lSQL = "DELETE FROM Clientes WHERE Clientes_ID = " & lClientes_ID
        gConexao.Execute lSQL
        
        lsDesconectar
    End If
End Sub

Public Sub lsListarDados()
    Dim lrs As ADODB.Recordset
    Set lrs = New ADODB.Recordset

    lsConectar
    lrs.Open "Select * from clientes", gConexao

    Sheets("Clientes").Range("a3:h1048576").ClearContents

    Sheets("Clientes").Cells(3, 1).CopyFromRecordset lrs
    
    If Not lrs Is Nothing Then
        lrs.Close
        Set lrs = Nothing
    End If
    lsDesconectar
End Sub

Public Sub lsAtualizarClientes()
    Dim i                   As Long
    Dim lUltimaLinhaAtiva   As Long
    
    lUltimaLinhaAtiva = Worksheets("Clientes").Cells(Worksheets("Clientes").Rows.Count, 2).End(xlUp).Row
    
    For i = 3 To lUltimaLinhaAtiva
        Select Case Cells(i, 8).Value
            Case "Inserir"
                lsInserir Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6), Cells(i, 7)
            Case "Alterar"
                lsAlterar Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6), Cells(i, 7), Cells(i, 1)
            Case "Excluir"
                lsExcluir Cells(i, 1)
        End Select
    Next i
    
    lsListarDados
End Sub

Faça o download deste exemplo gratuitamente, basta se inscrever para receber as novidades do site no link abaixo. Depois clique no link gerado logo abaixo automaticamente.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:

Sair da versão mobile