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):
Então podemos gerar alguns dados ilustrativos em uma planilha para servir de exemplo:
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.
Na parte “FROM [Plan1$A:C] ” & _ o meu da erro, diz que não é possível encontrar o objeto.
GostarGostar
Faz o download do exemplo que tem no post e execute o método VBA doRecordset(). Não se esqueça de habilitar o VBA.
GostarGostar
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?
GostarGostar
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.
GostarGostar
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?
GostarGostar
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 !
GostarGostar
Bom dia Pedro,
Muito bom teu post! Estou com um problema, no qual tenho um arquivo onde algumas colunas foram abreviadas com exemplo “Loc. Prestação”, com isso o SQL retornar erro com msg que o “.” é invalido, quando retiro essa coluna da consulta, funciona de boa.
Tens alguma dica?
Att,
GostarGostar
Obrigado por ler meu site Rafael. Tenta colocar os campos entre parênteses Select [Loc.], [XYV]….
GostarGostar
Obrigado pelo retorno. Já havia tentando isso, mas existe alguma forma de indicar o numero da coluna e não o nome?
Att,
GostarGostar
no vba só se vc tentar rs.Fields(3).Value. Mas na instrução SQL eu não me lembro. Acho que não tem como indicar o número.
GostarGostar
Os dados processados neste post, estão sendo enviados para que local. Executo o VBA e não tenho a informação processada. Obrigado.
GostarGostar
Oi Alberto, os dados não estão sendo enviados para lugar nenhum. Apenas são oriundos de uma planilha.
GostarGostar
Como faço para o “rs” enviar os dados a outra planilha, ex. plan2? E agradeço a rápida resposta. Sou um reles neófito no tema.
GostarGostar
Você teria que iterar pelo Recordset e ir salvando os dados na outra planilha.
GostarGostar
‘ TESTE DA RECORDSET
Public Sub iterate(ByVal rs As ADODB.Recordset)
‘Imprime na janela imediata uma recordset
Dim cTAB As String: cTAB = Chr(9)
Dim campo As ADODB.Field
Dim registro As String
For Each campo In rs.Fields
registro = registro & campo.Name & cTAB
Next campo
Debug.Print registro
rs.MoveFirst
Do While rs.EOF = False
registro = “”
For Each campo In rs.Fields
If campo.Name = “Delta” Then
registro = registro & Format(rs.Fields(campo.Name).Value, “Percent”) & cTAB
Else
registro = registro & rs.Fields(campo.Name).Value & cTAB
End If
Next campo
Debug.Print registro & ” (” & rs.AbsolutePosition & “)”
rs.MoveNext
Loop
End Sub
GostarGostar
Public Sub exportaRecordsetParaExcel(ByVal rs As Object, ByVal path As String, Optional ByVal toFileName As String)
Dim objExcel As Object ‘New Excel.Application
Dim objWorkbook As Object ‘Excel.Workbook
Dim objWSheet As Object ‘Excel.Worksheet
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Add()
Set objWSheet = objWorkbook.worksheets.Item(1)
rs.MoveFirst
Dim fld As field
Dim Linha As Long
Linha = 1
Dim coluna As Integer
For coluna = 1 To rs.Fields.Count
objWSheet.Cells(Linha, coluna) = rs.Fields(coluna – 1).Name
Next coluna
Linha = Linha + 1
‘Grava as linhas da recordset
While rs.EOF = False
For coluna = 1 To rs.Fields.Count
objWSheet.Cells(Linha, coluna) = rs.Fields(coluna – 1)
Next coluna
rs.MoveNext
Linha = Linha + 1
Wend
objWorkbook.SaveAs fileName:=path & “\” & toFileName
erro1:
If Not objWSheet Is Nothing Then
Set objWSheet = Nothing
End If
If Not objWorkbook Is Nothing Then
objWorkbook.Close
Set objWorkbook = Nothing
End If
If Not objExcel Is Nothing Then
objExcel.Quit
Set objExcel = Nothing
End If
End Sub
GostarGostar
Obrigado Pedro. Abraço
GostarGostar
É possível fazer uma conexão ADO com o próprio excel, só que fazendo JOIN ultilizando mais de um arquivo?
GostarGostar
Olá Luiz, acredito que não porém não testei. O fato é que Excel não é banco de dados mas sim uma planilha. Caso você consiga por favor me informe. Um abraço e boa sorte !
GostarGostar