Como realizar consultas SQL no Excel 2007 e versões superiores usando VBA

Este post foi originalmente publicado em https://officevbavsto.blogspot.com/2011/04/consultas-sql-no-excel-2007-e-algunas.html.

Neste artigo, eu abordo especificamente como realizar consultas SQL no Excel 2007 e versões superiores usando VBA.

Vamos ao código:

Conectando no Excel 2007

Sub consulta_excel()     

  Dim cn As ADODB.Connection     
  Dim cnString As String          
  Dim sqlString As String          

  Set cn = New ADODB.Connection          
  cnString = "Provider=Microsoft.ACE.OLEDB.12.0;"     
  cnString = cnString & "Data Source=C:\Documents and Settings\João A. Neto\Meus documentos\MyExcel.xlsx;"     
  cnString = cnString & "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34)          

  sqlString = "SELECT Dados, Antiguidade, Valor "     
  sqlString = sqlString & "FROM [Cotacoes$B2:D12]"          
  
  cn.ConnectionString = cnString          
  cn.Open          
  
  Set rs = New ADODB.Recordset     
  rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly          
  
  ThisWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rs          
  
  rs.Close     
  cn.Close          
  
  Set rs = Nothing     
  Set cn = Nothing 
  
End Sub

A primeira mudança está no provedor de acesso, que agora passa a ser Microsoft.ACE.OLEDB.12.0;

A segunda mudança está  na extensão do arquivo, que passa a ser .XLSX;

A última mudança está na configuração do Extended Properties, onde eu obrigo que existam aspas duplas entre as palavras Excel 12.0 Xml;HDR=YES. Estas aspas duplas eu forço a escrever através do comando Chr(codigo_ascii) que retorna o caracter de acordo com o número da tabela ASCII passada nele. No caso, 34 equivale a aspas duplas.

HDR=YES, significa que a primeira linha da sua planilha contém nomes de colunas e não dados.

Executar um UPDATE

Sub consulta_excel()
    Dim cn As ADODB.Connection
    Dim cnString As String
    
    Dim sqlString As String
    
    Set cn = New ADODB.Connection
    
    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;"
    cnString = cnString & "Data Source=C:\Documents and Settings\João A. Neto\Meus documentos\MyExcel.xlsx;"
    cnString = cnString & "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34)
        
    sqlString = "UPDATE [Cotacoes$B2:D12] "
    sqlString = sqlString & "SET Valor = 100"
    
    cn.ConnectionString = cnString
    
    cn.Open
    
    cn.Execute sqlString, adExecuteNoRecords
        
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
End Sub

Executar um DELETE

Não existe suporte para exclusão de dados em planilhas. Você terá que fazer sua deleção através dos métodos convencionais: apagando os dados com código VBA mesmo, ao invés de SQL..

[...]
sqlString = "DELETE FROM [Cotacoes$]"
sqlString = sqlString & " WHERE Valor = 227123"
[...]

Se você tentar executar a linha acima, aeceberá a seguinte mensagem de erro: Deleting data in a linked table is not supported by this ISAM. (Error 3617). De acordo com a Microsoft essa operação não é suportada. Ver http://support.microsoft.com/kb/257819/pt-br.

Executar um INSERT

Sub consulta_excel()

    Dim cn As ADODB.Connection
    Dim cnString As String
    
    Dim sqlString As String
    
    Set cn = New ADODB.Connection
    
    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;"
    cnString = cnString & "Data Source=C:\Documents and Settings\João A. Neto\Meus documentos\MyExcel.xlsx;"
    cnString = cnString & "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34)
        
    sqlString = "INSERT INTO [Cotacoes$](Dados, Antiguidade, Valor)"
    sqlString = sqlString & "VALUES ('Acções BVLP','desde 1988',227123)"
    
    cn.ConnectionString = cnString
    
    cn.Open
    
    cn.Execute sqlString, adExecuteNoRecords
        
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing

End Sub

Executar JOIN entre planilhas

Sub consulta_excel()
    Dim cn As ADODB.Connection
    Dim cnString As String
    
    Dim rs As Recordset
    Dim sqlString As String
    
    Set cn = New ADODB.Connection
    
    cnString = "Provider=Microsoft.ACE.OLEDB.12.0;"
    cnString = cnString & "Data Source=C:\Documents and Settings\João A. Neto\Meus documentos\MyExcel.xlsx;"
    cnString = cnString & "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34)
        
    sqlString = ""
    sqlString = sqlString & "SELECT COT.Dados"
    sqlString = sqlString & ", COT.Antiguidade"
    sqlString = sqlString & ", VAL.Valor"
    sqlString = sqlString & " FROM [Cotacoes$] AS COT"
    sqlString = sqlString & " INNER JOIN [Valores$] AS VAL"
    sqlString = sqlString & " ON COT.id_cotacao = VAL.id_cotacao"
    
    cn.ConnectionString = cnString
    cn.Open
    
    Set rs = New Recordset
    rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly
    
    ThisWorkbook.Worksheets(1).CopyFromRecordset rs
        
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
End Sub

Créditos

Photo by Mati Mango from Pexels

Fontes

• http://www.connectionstrings.com/excel-2007
• http://support.microsoft.com/kb/257819/pt-br