Blog

Postado em em 26 de março de 2022

Banco de Dados com VBA Parte1 – Integração com Access

Na aula de hoje: banco de dados com VBA parte1. Vou te mostrar a integração do VBA com o banco de dados Access.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Banco de Dados com VBA Parte1

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fala, Impressionadores! Hoje vamos te mostrar como fazer a integração de banco de dados com VBA e a utilização de um formulário no VBA para atualizar as informações!

Eu quero te mostrar como conectar Excel com Access (que é um banco de dados da Microsoft) e como vamos cadastrar, editar e excluir informações no banco de dados.

A ideia de conectar Excel com banco de dados é que o banco de dados é feito para o armazenamento de dados, então ele é mais seguro, faz isso de uma forma mais otimizada e consegue armazenar uma quantidade absurda de informações.

O Excel mesmo consegue utilizar 1 milhão de linhas, mas isso já deixaria o programa bem lento, então não seria ideal utilizá-lo como banco de dados.

Por isso vamos fazer a integração do Access com VBA para fazer essas operações de forma mais rápida e vamos utilizar o formulário no VBA para deixar essa interação mais fácil!

Banco de Dados – Exemplo

Temos um arquivo que armazena os dados de cadastro de funcionários de uma empresa.

Ele foi disponibilizado para download junto com os arquivos de aula. Veja abaixo ele aberto:

Banco de dados Access
Banco de dados Access


Inserindo um módulo

Devemos utilizar um código específico para utilizar/invocar as funções/códigos necessários para a comunicação com o banco de dados (Access). Para tal, precisaremos ativar uma referência específica no VBA.

Então, você precisará:

  • Abrir o VBA (Alt(+Fn)+F11)
  • Ir na guia Ferramentas
  • Referências…
  • Marcar a caixinha correspondente ao “Microsoft ActiveX Data Objects 6.1” (como na foto abaixo), ou a versão mais recente
  • OK

Para criar um código dentro de um módulo, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Como não temos nenhum módulo destinado ao tema da aula de hoje, e por questão de organização, vamos criar um módulo à parte.

Vamos na guia Inserir > Módulo, e uma caixa branca se abrirá.

Mudaremos a sua propriedade name para “modConectarAccess” e colocaremos a seguinte function dentro dele:

Function conectarAccess(conexaoBanco As ADODB.Connection)

Dim textoConexao As String, caminhoBanco As String

caminhoBanco = ThisWorkbook.Path & "BancoDeDadosVBA.accdb"
'caminhoBanco = "C:UsersdamorOneDriveÁrea de TrabalhoBancoDeDadosVBA.accdb"

textoConexao = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & caminhoBanco & ";" & _
               "Persist Security Info=False;"

conexaoBanco.Open (textoConexao)

End Function

Explicação do código:

  • Vamos utilizar de uma function com um argumento específico: uma variável, batizada de “conexaoBanco”, que vai representar uma conexão da biblioteca ADODB (abreviação para AdobeX Data Objects Database)
  • Dimensionar:
    • a variável “textoConexao” como uma String (texto)
    • a variável “caminhoBanco” como uma String (texto)
  • A variável “caminhoBanco” vai receber o resultado da combinação do comando Path (que vai retornar o caminho da pasta da planilha que contém o código) e do nome do arquivo do banco de dados (extensão .accdb – Access database). Portanto, ela armazenará o caminho completo do banco de dados
  • A variável “textoConexao” vai receber a junção dos textos:
    • “Provider=Microsoft.ACE.OLEDB.12.0;”Data Source=”
    • O texto resultante da variável “caminhoBanco”
    • “;Persist Security Info=False;”
  • No final da function vai ser invocada de fato a conexão do VBA/Excel com o banco de dados presente no Access mencionado no caminho no meio do código
  • Se encerra a function

Atualização dos dados

Já criamos a function que é responsável pela conexão com o nosso banco de dados. Precisamos agora, criar um outro código que vai de fato buscar e pegar os dados do banco de dados.

Ele vai usar a function criada anteriormente como auxiliar.

Então, para criar esse outro código precisaremos inserir mais um módulo, como mostrado nas etapas anteriores.

Mudaremos a propriedade name do novo módulo inserido para “modInteracaoAccess” e colocaremos a seguinte sub dentro dele:

Sub atualizarPlanilha()


Dim conexaoBanco As ADODB.Connection
Dim entradaBanco As ADODB.Recordset

Set conexaoBanco = New ADODB.Connection
Set entradaBanco = New ADODB.Recordset

conectarAccess conexaoBanco

entradaBanco.Open "CadastroFuncionarios", conexaoBanco, adOpenKeyset, adLockOptimistic

Range("A2:F1000000").ClearContents
Range("A2").CopyFromRecordset entradaBanco

conexaoBanco.Close

End Sub

Explicação do código:

  • Dimensionar:
  • Vamos configurar a “conexaoBanco” para representar uma nova conexão da biblioteca ADODB, através do comando Set VBA
  • Vamos configurar a “entradaBanco” para representar um conjunto de registros, que é um elemento da biblioteca ADODB também, através do comando Set VBA
  • Chamar a function “conectarAccess” criada anteriormente, passando como argumento pra ela a variável “conexaoBanco”. Rodando a function, ela vai entender que teremos que entrar no banco de dados passado pra ela, no caminho especificado
  • Após isso, vamos de fato abrir o banco de dados, considerando os argumentos:
    • de nome “CadastroFuncionarios”, dentro do arquivo que contém o banco de dados (extensão .accdb), que vai ser a nossa fonte de informações
    • que estará dentro da conexaoBanco, que é a nossa conexão, de fato
    • da forma adOpenKeyset, que vai ser a forma de pegar informações, sem ver uma possível exclusão por outra pessoa que manipula o banco de dados
    • e adLockOptimistic, que é o tipo de bloqueio da nossa entrada (para edições simultâneas) que vai assumir que ninguém vai estar atualizando o banco de dados no momento, assumindo que poucas pessoas ou só você o modifica
  • Vamos, em seguida, excluir as informações da planilha, considerando o intervalo entre A2 e F1000000
  • Pegar/copiar tudo que está no conjunto de registros (Recordset) vindo de “entradaBanco”, e vai colocar na célula A2 da planilha
  • Fechar/desativar a conexão com o banco de dados (desativar a variável “conexaoBanco”)
  • Se encerra o código

Disparar macro ao abrir a planilha: evento de abertura

Para fazer com que a nossa macro rode automaticamente ao abrir a planilha, deveremos utilizar de um evento no VBA, mais especificamente falando de um evento de abertura da planilha.

Um evento nada mais é do que um acontecimento que vai fazer alguma(s) ação(ões) disparar: o nosso código.

Com o VBA aberto, teremos que programar um evento Workbook_Open da forma mostrada abaixo, clicando duas vezes no menu à esquerda em “EstaPastaDeTrabalho”, e colocando o seguinte código:

Conectar Excel com banco de dados
Conectar Excel com banco de dados
Private Sub Workbook_Open()

Call atualizarPlanilha

End Sub

Explicação do código acima:

  • O código acima diz respeito ao evento de abertura da planilha. Quando ela fora aberta, esse código irá ser disparado
  • Vamos chamar a macro criada anteriormente, a atualizarPlanilha
  • Se encerra o código

Conclusão do Banco de Dados com VBA Parte1

Nessa aula você aprendeu a lidar com:

Até o próximo post, Impressionadores! Fique ligado no nosso canal do Youtube para mais conteúdo de VBA toda semana! Valeu!

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!


Quer aprender mais sobre VBA com um Minicurso Gratuito?


Vire uma referência no Mercado de Trabalho por causa do Power BI!

Quer saber como? Inscreva-se no Intensivão de Power BI! É gratuito!