Blog João Neto

Use o objeto Range para copiar registros de um banco de dados do Microsoft Access

João A. Neto -
Este post foi originalmente publicado em http://officevbavsto.blogspot.com/2011/09/copiando-registros-de-um-banco-de-dados.html.

Neste post, eu escrevo sobre como copiar registros de um banco de dados do Microsoft Access usando o objeto Range do Microsoft Excel.

Microsoft ActiveX DataObjects 2.1

O Microsoft ActiveX Data Objects (ADO) permite aplicações clientes acessar e manipular dados de uma variedade de fontes através de um provedor OLE DB [1]. Hoje, os dados existem em vários formatos, desde bancos de dados do Microsoft Access e SQL Server à documentos do Word, mensagens, planilhas eletrônicas do Microsoft Excel e muitos outros. ADO, ou ActiveX Data Objects, simplifica o uso de dados de diversas fontes, livrando desenvolvedores de aprender chamadas específicas às API dos fornecedores e quaisquer outros pormenores para cada formato de dado envolvido [2].

Neste código, precisaremos adicionar referência a biblioteca Microsoft ActiveX DataObjects 2.1. Execute os seguintes passos:

  • Acesse o Visual Basic Editor;
  • Clique em Ferramentas;
  • Referências;
  • Marque a opção Microsoft ActiveX DataObjects 2.1.

Como exemplo, eu uso o banco de dados do Microsoft Access Northwind Traders disponibilizado pela Microsoft.

Crie um novo módulo de código e digite o código a seguir:

Sub RecuperarDados()

     Dim objConnection As ADODB.Connection

    Dim objRecordset As ADODB.Recordset

     'Instancia um novo objeto Connection

    Set objConnection = New ADODB.Connection

    'Define o provedor para a conexao Jet
    objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"

    'Abre a conexao entre o banco de dados da NorthWind
    objConnection.Open "D:\Nwind.mdb"

    'Instancia um novo objeto Recordset
    Set objRecordset = New ADODB.Recordset

    'Abre a tabela Fornecedores (Suppliers)
    objRecordset.Open "Suppliers", objConnection

    '... continua

    'Fecha a tabela Fornecedores (Suppliers)
    objRecordset.Close

    'Fecha a conexao entre o banco de dados da NorthWind
    objConnection.Close

    'Limpa a instancia da memoria para os objetos Connection e Recordset
    Set objRecordset = Nothing
    Set objConnection = Nothing

End Sub

Neste código, você observa como criar uma conexão com banco de dados usando VBA.

Primeiro, declaramos uma variável do tipo ADODB.Connection. A classe Connection é usada para criar uma conexão aberta à uma fonte de dados. Através desta conexão, você pode acessar e manipular um banco de dados [3].

Em seguida, definimos o Provider (ou provedor). A propriedade Provider define o nome do provedor para um objeto Connection específico [4]. O Provider muda de acordo com a fonte de dados. O site Connection Strings [5] armazena a relação de Provider por tipo de fonte de dados (Excel, Access, mySQL, SQL Server e etc.). Em nosso código, definimos o Provider “Microsoft.Jet.OLEDB.4.0”, que é o Provider do Microsoft Access.

Por fim, abrimos a conexão através do método Open passando como parâmetro o caminho do banco de dados Northwind Traders. O método Open abre a conexão com a fonte de dados. Quando a fonte de dados está aberta, você pode executar comandos nesta fonte de dados [6], como por exemplo, inserir, selecionar, excluir e atualizar dados.

A seguir, declaramos uma variável do tipo ADODB.Recordset. A classe Recordset representa um conjunto de registros de uma tabela [7]. Ao utilizar ADO, você manipula os dados quase que inteiramente usando apenas objetos Recordset [7]. Um Recordset consiste de linhas e colunas (campos) [8].

Logo após, abrimos os registros da tabela “Suppliers” através do método Open passando como parâmetro o nome da tabela e o objeto Connection. Quando você executa o método Open, o Recordset apontará para o primeiro registro disponível [8].

E por fim, fechamos o Recordset e o Connection através do método Close. Você deve primeiro fechar o Recordset, e depois o objeto Connection.
Agora vamos ler os dados usando um método especial do objeto Range.

O método CopyFromRecordset

O método CopyFromRecordset copia o conteúdo de um objeto Recordset ADO ou DAO em uma planilha [9].

Continuando o código anterior:

Sub RecuperarDados()

  Dim objConnection As ADODB.Connection
  Dim objRecordset As ADODB.Recordset
  
  'Instancia um novo objeto Connection
  Set objConnection = New ADODB.Connection
  
  'Define o provedor para a conexao Jet
  objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
  
  'Abre a conexao entre o banco de dados da NorthWind
  objConnection.Open "D:\Nwind.mdb"
  
  'Instancia um novo objeto Recordset
  Set objRecordset = New ADODB.Recordset
  
  'Abre a tabela Fornecedores (Suppliers)
  objRecordset.Open "Suppliers", objConnection
  
  ActiveSheet.Range("A1").CopyFromRecordset objRecordset
  
  'Fecha a tabela Fornecedores (Suppliers)
  objRecordset.Close
  
  'Fecha a conexao entre o banco de dados da NorthWind
  objConnection.Close
  
  'Limpa a instancia da memoria para os objetos Connection e Recordset
  Set objRecordset = Nothing
  Set objConnection = Nothing

End Sub

Pronto! Agora, você está copiando os dados da tabela diretamente na sua planilha.

E se eu quiser escrever os nomes das colunas da tabela como primeira linha e então copiar os registros, como faço?

Veja o código abaixo:

Sub RecuperarDados()    

  '[...]   
  
  'Abre a tabela Fornecedores (Suppliers) 
  objRecordset.Open "Suppliers", objConnection   
  
  'Escreve os nomes das colunas 
  Dim intContador As Integer    
  
  For intContador = 0 To objRecordset.Fields.Count - 1 
  
    ActiveSheet.Cells(1, intContador + 1).Value = objRecordset.Fields(intContador).Name
  
  Next   
  
  'Copia os dados 
  ActiveSheet.Range("A2").CopyFromRecordset objRecordset   
  
  'Fecha a tabela Fornecedores (Suppliers) 
  objRecordset.Close   
  
  'Fecha a conexao entre o banco de dados da NorthWind 
  objConnection.Close    
  
  '[...]    

End Sub

Execute o código acima passo a passo e veja o que acontece!

Créditos

Photo by Anete Lusina from Pexels

Bibliografia