VBA - Acessando um banco de dados MS Access no Excel 2007

Neste artigo vou mostrar como acessar um banco de dados Microsoft Access usando a tecnologia ADO no Excel 2007 via VBA.

A partir da versão 2007 houve alterações na forma de acessar um banco de dados MS Access com os aplicativos Office.

A primeira coisa que você deve fazer é baixar e instalar o novo provedor de acesso a dados que deverá ser usado no lugar do Jet a partir deste link: 2007 Office System Driver: Data Connectivity Components

Nas versões anteriores utilizava-se o Microsoft Jet e a string de conexão usada pode ser vista no trecho de código abaixo:

Set conn = New ADODB.Connection
With conn
   .Provider = "Microsoft.JET.OLEDB.4.0"
   .ConnectionString = "Data Source=" & caminhoArquivoDados;
   .Open
End With

Na versão 2007 o mesmo trecho de código, agora usando o novo provedor, ficaria assim :

Set conn = New ADODB.Connection
With conn
   .Provider = "Provider=Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & caminhoArquivoDados;
   .Open
End With

Além destas houve outras alterações como:

- Para abrir um arquivo excel Com linha de cabeçalho: Data Source =c:\ExcelArq.xlsx;HDR=yes;Format=xlsx;

- Para abrir um arquivo excel Sem linha de cabeçalho: Data Source =c:\ExcelArq.xlsx;HDR=no;Format=xlsx;

Quanto aos tipos de arquivos excel 2007 temos as seguintes definições:

O arquivo excel pode ser tratado como texto definindo o flag: IMEX=1 na final da string de conexão:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\dados\Excel2007Arq.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Enfim, a mudança não foi tão grande assim...

Acessando o Microsoft Access

Vamos então mostrar um exemplo prático, acessando a tabela Employees do banco de dados Northwind.mdb que esta na pasta c:\dados, de como acessar o Microsoft Access.

Obs: Para saber como criar o código VBA descrito neste artigo leia os artigos :

Abra o Excel 2007 e crie um novo projeto com o nome AcessoBancoDados (menu Salvar Como) selecionando o tipo : Pasta de Trabalho Habilitada para Macro do Excel e clicando no botão Salvar;

A seguir pressione ALT+F11 para abrir o Editor Visual Basic (VBE);

Do VBE você pode ir para o Excel clicando no botão Excel no topo/esquerdo de sua tela . Assim usando os dois botões você pode navegar do VBE para o Excel e, vice-versa.

Vamos criar um procedimento que vai implementar a leitura da tabela Employees do banco dados Northind.mdb localizado na pasta c:\dados indicada, sendo que o resultado será exibido planilha atual da pasta de trabalho.

Inclua um módulo no editor VBA. Para inserir um módulo usando os menus do Visual Basic, selecione (no editor do VB) Inserir => Módulo;

A seguir inclua o procedimento ConexaoDB e altere o nome do módulo para ConexaoDB conforme abaixo:

Entendendo o código usado:

Observe a string de conexão usada pelo Excel 2007: cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDbPath

Obs: A string de conexão para versões anteriores ao Excel 2007 esta comentada.

Definimos o comando SQL para acessar a tabela Employees e obter todos os registros para o funcionário com código igual a 9.

cmd.CommandText = "SELECT * FROM Employees Where EmployeeID = 9;"

Atribuimos o resultado obtido à planilha atual a partir da célula A2:

Set qt = ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=ActiveSheet.Range("A2"))

Na célula A1 informamos a instrução SQL usada : ActiveSheet.Range("A1") = qt.Recordset.Source

Agora vamos usar o procedimento criado de forma a executá-lo para que a tarefa seja concluída.

Como podemos fazer isso ?

Podemos criar um botão de comando na Planilha Excel e chamar o procedimento.

Clique na guia Desenvolvedor e a seguir no menu Inserir e selecione Botão;

A seguir clique na planilha e desenhe o botão conforme mostra a figura abaixo:

Após isso selecione o botão e clique com o botão direito do mouse;

No menu suspenso clique em Editar Texto informando a seguir o texto: Acessar Dados;

Selecione novamente o botão e clique com o botão direito do mouse;

No menu suspenso clique em Atribui macro...;

Na janela Atribuir macro seleciona o procedimento que criamos no editor VBA: ConexaoDB e clique em OK;

Retorne a planilha Excel e clique no botão - Acessar Dados - para executar o código:

Você verá o resultado exibido, conforme mostra a figura abaixo, onde vemos na célula A1 a instrução SQL usada, na célula A2 os nomes dos campos e na célula A3 os dados para o registro selecionado.


Pegue a planilha aqui: AcessoBancoDadosExcel.zip

Slm 133:1 Oh! quão bom e quão suave é que os irmãos vivam em união!

Slm 133:2 É como o óleo precioso sobre a cabeça, que desceu sobre a barba, a barba de Arão, que desceu sobre a gola das suas vestes;

Slm 133:3 como o orvalho de Hermom, que desce sobre os montes de Sião; porque ali o Senhor ordenou a bênção, a vida para sempre.

Referências:

José Carlos Macoratti