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:

   

   Table Table Table Table
   
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.

• Pressione as teclas ALT+F11 para abrir o Visual Basic Editor
• Clique em Inserir->Módulo
• Selecione o módulo criado e clique no botão Janela "Propriedades" ou pressionando F4
• Altere a propriedade Name para SQL
• Clique duas vezes sobre o módulo criado abrindo-o para edição
• Iremos habilitar a referência do VBA para os componentes de banco de dados
• Clique em Ferramentas->Referências e marque o item Microsoft ActiveX Data Objects 6.1 Library, se não tiver este, selecione a versão mais recente
• Agora criaremos o código para a conexão com o banco de dados

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

• O código acima cria uma variável global gConexao que será utilizada para a conexão com o banco
• O procedimento lsConectar realiza a conexão com o banco de dados. Instancia o objeto de conexão e o configura. Perceba em Data Source o caminho do arquivo Access criado anteriormente
• Crie agora o código seguinte, ele será utilizado para desconectar com o banco de dados.

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

• Perceba que a conexão é fechada e o objeto destruído, é importante para limparmos a memória do computador
• Crie agora o código para inserirmos dados na nossa tabela Clientes

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

• Incialmente neste código criamos os parâmetros com o nome dos campos que iremos passar para a tabela clientes. Em seguida executamos o procedimento lsConectar para conectar com o banco, criamos o nosso comando SQL concatenando a instrução com os parâmetros e em seguida executamos o código diretamente na conexão e encerramos chamando o procedimento lsDesconectar.
• Em seguida criamos o código para Alterar os registros da nossa tabela Clientes da base de dados Access. Digite o seguinte código em seguida.

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

• De forma muito semelhante ao procedimento de inserir dados são criados os parâmetros e passados de forma concatenada na variável lSQL e executado diretamente na conexão com o banco de dados Access no Excel. Detalhe que temos neste caso a variável lClientes_ID utilizada para identificar qual registro será alterado.
• Agora vamos criar o código para excluir os registros, digite o seguinte código:

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

• O código também é muito parecido com os anteriores, veja também que o parâmetro lClientes_ID tem que ser maior que 0, pois indica o registro que deverá ser excluído do banco de dados
• Por último neste módulo crie o seguinte código fonte que irá listar todos os dados da tabela Clientes do banco Access

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

• No código é realizada a conexão e utilizado o objeto RecordSet para retornar os dados neste objeto, posteriormente com o comando CopyFromRecordset são copiados estes dados rapidamente para a tabela, sem necessidade de tratamentos e loops
• Crie um novo módulo clicando em Inserir->Módulo e renomeie-o para Aplicacao, aqui iremos fazer o nosso código funcionar
• Digite o código seguinte neste novo módulo criado

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

• A primeira instrução identifica qual é a última linha preenchida no cadastro no Excel, na coluna B
• Em seguida é feito um loop da linha 3 até a última linha identificada e conforme a instrução que houver na coluna 8 (H), é chamada uma das funções e passado o valor da linha e coluna conforme podemos observar.
• Ao final da instrução é chamada a função lsListarDados
• O resultado final é que todos os dados são atualizados na tabela do banco de dados conforme a opção selecionada pelo cliente e ao final, com o banco atualizado, é chamado o procedimento lsListarDados para retornar as informações da tabela Clientes
• Volte para o Excel e clique na guia Inserir->Imagens->Formas, mude a cor e escreva Atualizar dados nela
• Clique com o botão direito sobre a imagem e selecione Atribuir Macro e selecione a macro lsAtualizarClientes, toda vez que clicar neste botão os dados do cadastro serão atualizados conforme o preenchimento da coluna Ação.

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.

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: