Abrindo uma ADO.Recordset com dados de uma Planilha do MS Excel

Nesta postagem, venho demonstrar como é possível abrir uma ADO.Recordset com dados oriúndos de uma planilha do MS Excel.

Para esclarecer, ADO ou ActiveX Data Objects é uma biblioteca da Microsoft utilizada para acesso e atualização de dados, inclusive em bancos de dados corporativos tais como SQL Server, Oracle, Sybase, etc …  E também é possível manipular dados do MS Access. Nos dias atuais, com o surgimento da plataforma DOT.NET para programas desenvolvidos dentro desta tecnologia utiliza-se ADO.net. Já na linguagem Java, utiliza-se JDBC, Hibernate, JPA dentre outras tecnologias.

Voltando ao assunto deste artigo, a primeira coisa a se fazer é referenciar a biblioteca para que a utilizemos no VBA (Visual Basic for Applications):

referenciando ADO

Então podemos gerar alguns dados ilustrativos em uma planilha para servir de exemplo:

planilha de dados

Por fim, vamos ao código (os comentários ilustram o que está sendo feito):

Public Sub doRecordset()

 'Link interessante:
 'http://support.microsoft.com/kb/257819

 'DECLARAMOS VARIÁVEIS
 Dim rs As ADODB.Recordset
 Dim cn As ADODB.Connection
 
 'DECLARAMOS VARIÁVEIS EM LINHA
 Dim conStr As String, path As String, strSQL As String
 
 'CAMINHO COMPLETO DA PLANILHA (no diretório onde ela se encontra)
 path = ActiveWorkbook.path & "\" & ActiveWorkbook.Name
 
 'String de conexão para a planilha. Estou usando o MS Office 2007. Para outras versões deveremos variar o número 12
 conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & path & "';" & _
 "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
 
 'Instrução SQL que retorna apenas as pessoas cuja idade é > 30 anos.
 strSQL = "SELECT Nome, Sobrenome, Nascimento, CInt((Now - Nascimento)/365.25) as Idade " & _
 "FROM [Plan1$A:C] " & _
 "where (Now - Nascimento)/365.25 > 30"
 
 'Instanciamos objeto connection na variável declarada acima
 Set cn = New ADODB.Connection
 
 'Abrimos a conexão
 cn.Open conStr
 
 'Instanciamos objeto recordset na variável declarada acima
 Set rs = New ADODB.Recordset
 
 'Abrimos recordset utilizando conexão aberta acima e instrução SQL
 rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
 
 
 'Fazemos um looping por todos os registros da recordset e imprimimos no console
 Do Until rs.EOF = True
   Debug.Print rs!Nome, rs.Fields(1).Value, rs!Nascimento, rs!Idade
   rs.MoveNext
 Loop
 
 'LIMPEZA: (MUITO IMPORTANTE) Desalocamos espaço em memória fechando os objetos e os desalocando.
 If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
 End If
 
 If Not cn Is Nothing Then
   cn.Close
   Set cn = Nothing
 End If
End Sub

Se você está se perguntando: “Qual a utilidade disto?” a resposta é simples: serve para manipular dados. Podemos, de maneira alternativa, manipular dados no MS Excel utilizando-se dos objetos existentes no próprio Excel, mas, desta forma acima, cria-se a possibilidade de utilizar instruções SQL que são bastante poderosas para a finalidade de consultar e manipular dados.

Outra coisa, na instrução SQL:

SELECT Nome, Sobrenome, Nascimento, CInt((Now - Nascimento)/365.25) as Idade FROM [Plan1$A:C] where (Now - Nascimento)/365.25 > 30

Plan1$A:C determina a planilha (aba) que estamos consultando e subsequentemente a faixa de valores (Range => A:C). A:C significa que a tabela vai da coluna A até a coluna C. Se quisermos restringir a range basta substituir A1:C6  por exemplo.

O DOWNLOAD  desta planilha está disponível. Se gostou, deixe seu comentário.

Veja outros posts sobre VBA.

Fale comigo.

Anúncios
Esta entrada foi publicada em Excel, Visual Basic 6.0 e VBA com as etiquetas , , , . ligação permanente.

6 respostas a Abrindo uma ADO.Recordset com dados de uma Planilha do MS Excel

  1. Ramiro diz:

    Na parte “FROM [Plan1$A:C] ” & _ o meu da erro, diz que não é possível encontrar o objeto.

    Gostar

  2. Edgar diz:

    Oi Pedro, muito bom seu post. Estou precisando de um exemplo para poder inserir um novo dado ou atualizar em planilha de Excel usando sql. Igual ao que fez em cima. Porém para inserção ou atualização.

    Vc tem alguma sugestão ou exemplo?

    Gostar

    • pedroabs diz:

      Muito obrigado Edgar. A minha sugestão é tentar utilizar as instruções SQL de Insert e Update. Acredito que o ADO vai aceitar salvar na planilha do MS Excel sem problemas.

      Gostar

  3. Rafael jansen diz:

    Olá Pedro… demais suas dicas.

    Agora deixa eu tirar umas duvidas com vc… tenho um indicador de tabelas dinâmicas que são alimentadas através de conexões a um BD quando clico no botão atualizar, existe a possibilidade de fazer com que esse arquivo seja atualizado automaticamente ao abrir sem que precise de qq outra ação para atualizar as informações?

    Gostar

    • pedroabs diz:

      Olá Rafael,

      obrigado por ler meu site.

      A respeito da sua dúvida eu também não tenho certeza pois teria que testar o projeto para poder investigar (assim como vc).

      Eu me lembro de uma ocasião que eu tive em que sempre que abria a planilha com tabela dinâmica, mesmo depois de ter substituído a origem dos dados eu tinha que ir na aba [Dados] e clicar no comando [Atualizar Tudo]. Tentei atualizar pelo VBA mas não tive sucesso na minha experiência.

      Caso você tenha sucesso gostaria de saber qual caminho percorreu.

      Um abraço e mais uma vez agradeço pela leitura no meu site !

      Gostar

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s