Criar Conexão de Banco de Dados no Excel VBA
Neste artigo você verá como criar conexão de banco de dados no VBA do Excel.
Aqui você verá várias dicas de códigos prontos para a conexão de banco de dados.
- Como criar uma conexão com banco de dados no VBA
- Como desconectar uma conexão no VBA
- Como realizar uma consulta conectando dados com o VBA
Como criar uma conexão com banco de dados no VBA
O primeiro ponto para criar uma conexão no VBA é saber que isto varia de acordo com o banco de dados que será utilizado e também com o método que será utilizado.
Neste exemplo você aprenderá como utilizar a biblioteca Microsoft ActiveX Data Objects 6.1 Library.
Então o primeiro passo é dar ALT+F11 no Excel e no VBA marcar esta biblioteca.
Com isso vamos então criar a nossa conexão. No nosso exemplo iremos conectar com um banco de dados Access, mas mais adiante neste artigo falaremos de outros bancos 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
Acima nós temos o código fonte de exemplo para realizar uma conexão com o banco de dados.
A primeira etapa para qualquer iteração entre o VBA e um banco de dados é realizar a conexão.
Acima veja que nós temos a declaração da gConexao como um tipo ADODB.Connection. Este é o conector do banco de dados.
Na sub lsConectar é instanciado então a gConexao e passado o seguinte parâmetro no método Open, que abre então o banco de dados para realizar tarefas:
“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”
Esta string acima é que define a conexão com o banco de dados. Entenda os campos:
Provider: O tipo de banco de dados que terá a sua conexão, no caso do Access a conexão é o Microsoft.ACE.OEDB.12.0. Varia conforme o tipo de conexão.
Data Source: O local aonde está o seu banco de dados. No caso do banco de dados access definimos simplesmente o local aonde está o arquivo.
Persist Security Info: O valor padrão para Persist Security Info é falso; nós recomendamos usar esta opção em todas as cadeias de conexão. Configurar Persist Security Info como true ou yes permite informações confidenciais de segurança, incluindo a identificação de usuário e a senha, para serem obtidas de uma conexão depois que ela tiver sido aberta. Quando Persist Security Info for definido como false ou no, as informações de segurança serão descartadas após serem usadas para abrir a conexão, garantindo que uma fonte não confiável não tenha acesso a informações confidenciais de segurança.
Conexão com senha Access VBA no Excel
Para conectar com um banco de dados Access no Excel pelo VBA você deve utilizar a seguinte string de conexão:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Rieper\Blog Guia do Excel\Excel com banco de dados Access – SQL\SistemaGdE.accdb;Jet OLEDB:Database Password=MyDbPassword;”
Substitua o caminho do Data Source pelo caminho do seu arquivo Access e altere a senha no campo Password, MyDbPassword pela senha do seu banco de dados Access.
Conexão VBA com Bases de Dados
Para realizar outras conexões, com bancos de dados diferentes basta mudar a string de conexão conforme a sua necessidade. Veja exemplos:
Conectar SQL Server com VBA Excel: Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;
Conectar MySql com VBA Excel: Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;
Conectar Excel XLSX com VBA Excel: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=“Excel 12.0 Xml;HDR=YES”;
Conectar Excel XLSB com VBA Excel: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties=“Excel 12.0;HDR=YES”;
Conectar Excel XLSM com VBA Excel: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties=“Excel 12.0 Macro;HDR=YES”;
Conectar Oracle com VBA Excel: Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
Para outros tipos de conexões de bancos de dados com o Excel veja também o site ConnectionStrings.com
Desconectar Banco de Dados VBA Excel
Para desconectar um banco de dados do VBA Excel você pode utilizar o seguinte código fonte VBA:
Private Sub lsDesconectar()
If Not gConexao Is Nothing Then
gConexao.Close
Set gConexao = Nothing
End If
End Sub
Veja que ele identifica se a variável gConexao está instanciada, e se estiver é utilizado o método Close e é liberado da memória o objeto setando ele como Nothing.
Consultando dados Banco de Dados VBA Excel
Para realizar consultas iremos utilizar o objeto Recordset no VBA.
Marque então em Ferramentas->Referências a opção Microsoft ActiveX Data Objects Recordset 2.8 Library.
Veja um exemplo de código de consulta:
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 acima criamos o objeto lrs do tipo ADODB.Recordset, o mesmo é instanciado pelo set.
Em seguida chamamos o método lsConectar que criamos antes, ele verifica se a conexão está ativa e se não estiver conecta. Então já podemos realizar a nossa consulta.
Para a consulta utilizamos o método Open em lrs.Open “Select * from clientes”, gConexao.
Veja que é passado no primeiro parâmetro a consulta que desejamos e no segundo parâmetro a conexão com o banco de dados.
Então a planilha é limpa e em seguida é utilizado o método CopyFromRecordSet e passado o parâmetro lrs.
As informações são então coladas na célula C1.
E por fim é verificado se o objeto lrs retornou algo e se retornou é fechado e destruído.
Em seguida é fechada a conexão chamando então o procedimento lsDesconectar que criamos anteriormente.
Veja mais
No artigo Cadastro em Excel com banco de dados Access Sql temos uma aula completa do zero de como criar uma conexão com um banco de dados Access com VBA Excel.
Na aula você aprenderá como criar um cadastro de clientes com conexão, inclusão, alteração e exclusão de dados pelo VBA no Excel em um banco de dados Access.
Quer aprender VBA Excel?
Se deseja aprender VBA para automatizar suas tarefas, criar programas e crescer na sua empresa, conheça o nosso curso de VBA do zero ao profissional.
Nele você aprenderá mesmo que não conheça nada de programação, pois ele ensina desde a lógica de programação, como em um curso técnico ou faculdade.
No curso você aprenderá como programar em VBA com cases reais, envios de e-mail, automações de rotinas, criação de formulários e conexão com banco de dados pelo VBA desde a criação do banco de dados no Access.
