Tag Archives: Excel

Excel, Microsoft Office, VBA

Excel: O famoso (mas nem tanto) SAMPLES.XLS

Tenho que admitir. Nos fóruns de Excel VBA que frequento, muitas perguntas só pude responder por conta da existência deste arquivo.

O arquivo SAMPLES.XLS é copiado para a pasta SAMPLES que fica dentro da pasta onde o Microsoft Office foi instalado (exceto para o Office 2003). O arquivo contém alguns dos mais utilizados exemplos dos recursos do Microsoft Excel, desde funções até códigos em VBA. O grande destaque é que o arquivo é montado pela esquipe da Microsoft em um padrão muito bem formatado e auto-explicativo. A versão em português não deixa a desejar. Segue uma figura com a cara do arquivo:

Para quem não conseguir encontrar o dito em sua máquina, disponibilizo aqui o link para download. A cópia do arquivo é da própria Microsoft e assinada digitalmente por ela:

SAMPLES.XLS

Bom proveito!

Tomás Vásquez

Excel, Microsoft Office, VBA

Parametrizando a importação de dados no Excel

2 Comments

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];

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

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)

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

Agora é com vocês. Boa sorte a todos.

Tomás Vásquez

Excel, Microsoft Office

Entendendo as limitações do Excel

2 Comments

Depois da criação dos Vídeos treinamentos sobre o recurso de importação de dados no Excel com o auxílio de comandos SQL para manipulação dos dados, tenho recebido uma série de dúvidas a respeito e tentarei neste blog, esclarecer algumas delas e também estender as funcionalidades, além daquelas demonstradas nos vídeos dispostos.

O primeiro ponto que é necessário mencionar, é que devemos admitir que só o fato do recursos de importação permitirem enxergar uma pasta de trabalho do Excel como um fonte de dados, já é uma proeza e tanto. É necessário assumir que tentar ir além disso, é fugir demais do foco do aplicativo. Apesar de vantajoso, não é prudente usar o Excel como base de dados. Se a necessidade exigir consultas mais complexas ou um tratamento de dados mais apurados, além de consistência, comece a pensar em utilizar um sistema de banco de dados. O exemplo mais próximo do Excel é o Access, mas não se esqueçam que existem uma série de alternativas, boas e gratuitas, como o MSDE e o SQL Server 2005 Express, para não citar outras. Não vou entrar no mérito destes banco de dados, mas fica aí a idéia.

Tentar transformar o Excel num mega-aplicativo de acesso a dados, pode se tornar tão oneroso que compensaria estudar uma tecnologia dedicada a este fim. Pensem na utilização destes recursos como alternativas, não corriqueiras. A função do Excel é manipular dados, não controlá-los.

Sobre as dúvidas sugestões e suas respostas, tratarei de mencionar todas por este veículo.

Sugestões e comentários, é só avisar.

Um grande abraço a todos.

Tomás Vásquez

Excel, Microsoft Office

Comparar pastas de trabalho lado a lado

Um novo método disponível é a comparação lado a lado que permite que você veja mais facilmente as diferenças entre duas pastas de trabalho, sem precisar mesclar todas as alterações em uma única pasta de trabalho. Você pode rolar pelas duas pastas de trabalho ao mesmo tempo para identificar diferenças entre elas. Menu Janela / Comparar Lado a lado com.

  1. Abra as pastas de trabalho que deseja comparar lado a lado.
  2. No menu Janela, clique em Comparar Lado a Lado com.
  3. Na barra de ferramentas Comparar Lado a Lado, siga um destes procedimentos:
    • Para rolar através das pastas de trabalho ao mesmo tempo, clique em Rolagem Sincronizada .
    • Para redefinir as janelas da planilha para as posições em que se encontravam quando você iniciou a comparação das pastas de trabalho, clique em Redefinir Posição da Janela .
  4. Clique em Fechar Lado a Lado para interromper a comparação das pastas de trabalho.

Observação Se você abrir duas pastas de trabalho, o comando no menu Janela incluirá o nome de arquivo de uma dessas pastas de trabalho. Por exemplo, abra “pasta1.xls” e “pasta2.xls”. Durante a exibição de “pasta1.xls”, o menu Janela mostrará o comando como Comparar Lado a Lado com pasta2.xls.

Se você tiver fechado a barra de ferramentas Comparar Lado a Lado mas quiser mostrá-la novamente, clique em Personalizar no menu Ferramentas, clique na guia Barra de Ferramentas e, em seguida, selecione Comparar Lado a Lado na lista Barra de Ferramentas.

AbraçosTomás Vásquez