(12) comments
Add Your Reply
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.
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:
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.
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.
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.
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
*/
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é 🙂
Oi, aqui é o Josué. Sou o criador do site dba-pro.com. Minha missão é ajudar pessoas a usarem o potencial do SQL Server em suas profissões, para que trabalhem felizes e bem remuneradas. Sou consultor e empreendedor, atuando em empresas desde 2000 e dando aulas desde 2004. Adoro SQL, Excel, automatização de processos, finanças e Coaching. Quando não estou trabalhando ou estudando, gosto de leitura, games para PC, filmes, séries e conversas estimulantes.
