Olá! Seja bem-vindo!

Hoje veremos como executar qualquer consulta no SQL usando Excel VBA, sem a necessidade do usuário editar o código VBA quando quiser alterar o SELECT da consulta. Mostrarei ainda como acessar um textbox (caixa de texto) usando o VBA, uma técnica muito útil para simplificar a forma como o usuário inclui informações para processamento do VBA.

 

 

Respondendo as dúvidas do vídeo anterior

Depois do vídeo onde mostrei como acessar o SQL Server pelo Excel via macro, surgiram algumas dúvidas que responderei hoje. As principais foram:

 

Como alterar as querys/consultas sem mudança no código VBA

Para responder essa primeira dúvida, disponibilizaremos ao usuário da planilha uma caixa de texto para que ele mesmo inclua a consulta desejada. O VBA lerá a query inserida na caixa de texto (textbox) e depois fará a buscar dados no SQL.

O que muda no código e na planilha para a execução de consultas mais complexas, (ex: querys com PIVOT)

A segunda dúvida é mais simples, pois você verá no vídeo que qualquer query pode ser executada usando a mesma estratégia do primeiro vídeo. Se você está tendo problemas para fazer isso há duas possíveis causas:
1) Sua query não retorna um resultset. O método copyfromrecordset que usamos precisa de um recordset, no vídeo mostro a diferença entre um comando com e sem retorno de resultset.
2) Sua query com PIVOT não está com a sintaxe correta. Confira a query no SQL Management Studio como fiz no vídeo. Se ela retorna resultset corretamente deve funcionar tanto no SQL Management Studio quando no Excel/VBA/Macro.

Como incluir automaticamente os nomes das colunas das tabelas do SQL no Excel

Para nossa sorte os nomes das colunas são retornados junto com as informações do resultset no objeto recordset que usamos para buscar os dados da consulta, então basta ler esses dados e incluí-los no Excel.
Infelizmente não um método que simples para copiar os cabeçalhos / nomes de colunas análogo ao método copyfromrecordset, mas isso não será problema como você verá no vídeo.

EXECUTAR qualquer CONSULTA no SQL USANDO Excel VBA

Aqui vai o script que usamos no vídeo de hoje:

------------------------------------------------------------------
-- Executar qualquer query no SQL usando o Excel
------------------------------------------------------------------
-- comando sem resultset:
print @@version

-- comando com resultset:
select @@version

--------------------------------------------------
-- Banco de dados de testes
--------------------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null 
	drop database curso
go
create database curso
go
use curso
go
create table colaboradores (
	id smallint identity(1,1) primary key,
	nm varchar(100) not null,
)
insert into colaboradores values 
	('Abismail do Rosário'),
	('Josemar Pinto Godinho'),
	('Francisco Sanguessuga'),
	('Rolando da Rocha'),
	('Necrotério Silveira'),
	('Navegador da Luz')

create table produtos (
	id smallint identity(1,1) primary key,
	nm varchar(100),
	vl decimal(10,2),
	ic_ativo bit not null default (1)
)
insert produtos (nm, vl) values 
	('Mouse Gammer 25 botões', 200),
	('Teclado Gammer 350 teclas', 300),
	('Monitor 32 Pol Full HD', 1000),
	('RAM DDR4 4GB Powerturbo', 500),
	('CPU Nasa 10Ghz', 2000),
	('HD SSD 1TB', 2000)

create table vendas (
	id int not null identity(1, 1) primary key,
	dt datetime not null,
	id_produto smallint not null foreign key references produtos(id),
	id_vendedor smallint foreign key references colaboradores(id),
	qt int check (qt > 0),
	vl_unitario decimal (10,2), 
	vl_venda as qt * vl_unitario
)
;with cte_seq as (
	select 1 as nr_cont
	union all
	select nr_cont + 1 
	from cte_seq where nr_cont < 10000)
insert into vendas (dt, id_produto, id_vendedor, qt, vl_unitario)
	select 
		dateadd(day, -(abs(checksum(newid())) % 1825), getdate()) dt,
		produto.id,
		colaborador.id,
		1 + (abs(checksum(newid())) % 15) nr_quantidade,
		produto.vl vl_unitario
	from cte_seq
	left join (select top 1 id, vl from produtos order by newid()) produto on 1=1
	left join (select top 1 id from colaboradores order by newid()) colaborador on 1=1
	option (maxrecursion 0)
go

--------------------------------------------------
-- Código VBA
--------------------------------------------------

/*
Option Explicit
Public Sub sb_RetornaConsulta()
    Dim obj_Connection As New ADODB.Connection
    Dim obj_RecordSet As New ADODB.Recordset
    Dim str_SQL As String
    Dim str_PlanilhaDestino As String
    Dim str_ConnString As String
    Dim str_LinhaInicial As String
    Dim nr_coluna As Integer
    
    str_PlanilhaDestino = "Resultado"
    str_ConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=curso;Data Source=."
    str_LinhaInicial = 15
    str_SQL = Worksheets(str_PlanilhaDestino).Shapes("consulta").TextFrame.Characters.Text
    
    ' Limpa dados
    Range(str_PlanilhaDestino & "!A15:XFD10000").Clear

    ' Executa query no SQL
    obj_Connection.Open str_ConnString
    obj_RecordSet.Source = obj_Connection
    obj_RecordSet.Open str_SQL, obj_Connection
       
    ' Inclui cabeçalhos da query:
    For nr_coluna = 0 To obj_RecordSet.Fields.Count - 1
        Worksheets(str_PlanilhaDestino).Cells(str_LinhaInicial, nr_coluna + 1).Value = obj_RecordSet.Fields(nr_coluna).Name
    Next
    
    ' Salva dados no Excel
    Sheets(str_PlanilhaDestino).Cells(CInt(str_LinhaInicial + 1), 1).CopyFromRecordset obj_RecordSet

    ' Finaliza conexão e objetos
    obj_RecordSet.Close
    obj_Connection.Close
    Set obj_RecordSet = Nothing
    Set obj_Connection = Nothing
    
End Sub


*/


--------------------------------------------------
-- Código SQL
--------------------------------------------------
/*
-- querys de teste:
Select * from information_schema.tables

select *
from (
	select 
		year(dt) as Ano,
		c.nm as Colaborador,
		sum(vl_venda) as Valor
	from vendas 
	join colaboradores c on c.id = vendas.id_vendedor
	group by c.nm, year(dt)
) em_linha
pivot (sum(valor) for ano in ([2013], [2014], [2015], [2016], [2017], [2018])) em_colunas 
order by 1


*/

--------------------------------------------------
-- Excluindo dados de teste
--------------------------------------------------
/*
use master
drop database curso
*/

 

CONCLUSÃO

Como você viu, integrar o SQL Server ao Excel é simples e divertido. Com os exemplos de código que lhe mostrei você com certeza está pronto para começar se divertir e aproveitar essas duas ferramentas incríveis.

Abraço do seu amigo Josué 🙂