Cadastro em Excel com banco de dados Access – SQL

8

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
    CampoTipoDescricao
    Clientes_IDNumeracao AutomaticaCodigo unico e sequencial
    des_nomeTexto CurtoNome completo
    des_logradouroTexto CurtoDescricao do endereco
    num_nrLogradouroNumeroNumero do endereco
    des_bairroTexto CurtoDescricao do bairro
    des_cidadeTexto CurtoDescricao da cidade
    des_ufTexto CurtoDescricao da UF
  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.

Excel com banco de dados Access - SQL 2

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;ExcluirExcel com banco de dados Access - SQL 3
  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.

INSCREVA-SE GRATUITAMENTE PARA REALIZAR O DOWNLOAD DOS ARQUIVOS E RECEBER NOVIDADES DO GUIA DO EXCEL POR E-MAIL SEMANALMENTE:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

8 COMENTÁRIOS

  1. Obrigado pela divulgação dos seus conhecimentos.
    Gostaria de lhe perguntar se conhece alguma macro que apague as linhas que se encontrem “fora” de data prevista.
    Eu explico: tenho uma base de dados com as consulta que vou ter no hospital. Tenho uma coluna com a data da mesma e outra que me avisa se o prazo esta próximo. Contudo quando o prazo é ultrapassado tenho que apagar manualmente essa informação para ter a base de dados sempre atualizada.
    Obrigado

  2. Caro, como eu coloco o resultado do select numa variável.
    lsConectar

    lrs.Open “Select ALUNO from LISTA_DIARIA WHERE ALUNO = ‘074049-5′”, gConexao

    VAR_UAX = ?
    If Not lrs Is Nothing Then
    lrs.Close
    Set lrs = Nothing
    End If
    lsDesconectar

  3. Rieper, boa tarde, primMarcos, boa tarde, primeiramente meus parabéns pela iniciativa. Estou utilizando o cadastro, porém não consigo cadastrar números decimais, exemplo um preço. Qual tratamento devo fazer para realizar este tipo de cadastro?eiramente parabens pela

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here