Fazendo referência aos treinamentos em vídeo sobre união de planilhas usando a importação de dados no Excel, uma das dúvidas que surgiram entre os telespectadores foi sobre a parametrização dos dados nas consultas em SQL.
Pelo que entendi, foi uma tentativa de fazer analogia às consultas criadas no Access, em que costumeiramente menciona-se parâmetros na cláusula WHERE através do caractere chave colchete “[]”.
A criar uma consulta no Access como esta:
1
| SELECT *FROM Clientes WHERE ID = [Digite o Id do Cliente]; |
SELECT *FROM Clientes WHERE ID = [Digite o Id do Cliente];
Ao ser executada, surge uma caixa de texto com a mensagem colocada entre os colchetes da cláusula WHERE, para que o valor do parâmetro seja informado por digitação. O mesmo não acontece com o recurso de importação de dados do Excel. Pelo menos em meus testes, não consegui uma alternativa automática para fazer a importação de dados. Então, o jeito é fazer por código mesmo. Para tentar ajudar nesta tarefa, efetuarei o mesmo procedimento de importação de dados no vídeo de treinamentos com o gravador de macros ativado. Após isso, o seguinte código VBA é gerado:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
, _
"rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
, _
";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
, _
"se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
, "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [Clientes$] WHERE CódigoDoCliente = ""ALFKI""")
.Name = "Clientes"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\temp\Clientes.xls"
.Refresh BackgroundQuery:=False
End With
End Sub |
Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
, _
"rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
, _
";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
, _
"se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
, "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [Clientes$] WHERE CódigoDoCliente = ""ALFKI""")
.Name = "Clientes"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\temp\Clientes.xls"
.Refresh BackgroundQuery:=False
End With
End Sub
Percebam que todos os dados informados foram gravados, inclusive o nome de planilha e o comando SQL. Agora, usando um pouco de código VBA adicionai, é perfeitamente possível parametrizar nossa importação de dados. Um exemplo extremamente simples é adicionar as seguintes linhas códigos e alterar a linha onde está o comando SQL:
1
2
3
4
5
| Dim Codigo As String, SQL As String
Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ & Codigo & """"
'nova linha do comando SQL
.CommandText = Array(SQL) |
Dim Codigo As String, SQL As String
Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ & Codigo & """"
'nova linha do comando SQL
.CommandText = Array(SQL)
Agora é só executar e informar o valor do parâmetro na InputBox. Agora é usar a imaginação para construir consultas com parâmetros mais aprimorados, validação e tudo mais. É lógico que agora você pode extrair os valor do parâmetro de qualquer lugar da planilha, como o valor de uma célula, um UserForm. Abaixo segue o código completo:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
'
Dim Codigo As String, SQL As String
Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ & Codigo & """"
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
, _
"rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
, _
";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
, _
"se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
, "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array(SQL)
.Name = "Clientes"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\temp\Clientes.xls"
.Refresh BackgroundQuery:=False
End With
End Sub |
Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
'
Dim Codigo As String, SQL As String
Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ & Codigo & """"
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
, _
"rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
, _
";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
, _
"se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
, "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdSql
.CommandText = Array(SQL)
.Name = "Clientes"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\temp\Clientes.xls"
.Refresh BackgroundQuery:=False
End With
End Sub
Agora é com vocês. Boa sorte a todos.
Tomás Vásquez