Category Archives: VBA

Dicas, modelos, exemplos da ferramenta que faz a grande diferença a suíte Microsoft Office

VBA – Opening a web page on IE

Despite the fact that it is a very old issue, practicing is the best way to remember a hint. And the hint is, opening a web page through VBA code. Take a look at code below:

Private Sub OpenPage()
    Dim browser As Variant
    Set browser = CreateObject(“InternetExplorer.Application”)
    browser.Navigate (“www.google.com”)
    browser.Visible = True
End Sub

So far so good, but the best thing in the code above is, the Internet Explorer is under your control! You can have access to all webpage data through the Internet Explorer object’s properties. Of course, you can play a bit with some simple properties, like:

Private Sub DrawingIn_Click()
    Dim browser As Variant
    Set browser = CreateObject(“InternetExplorer.Application”)
    browser.Navigate (“www.google.com”)
    browser.StatusBar = False
    browser.Toolbar = False
    browser.Visible = True
    browser.Resizable = False
    browser.AddressBar = False
End Sub

Enjoy!

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

Importando dados do SQL Server para o Excel com VBA e ADO

Essa é uma pergunta que aparece quase sempre nos fóruns de Excel que frequento. O bom é que a própria Microsoft dá a resposta:

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
40
Sub RetrieveSQLServerData()
' Cria a conexão.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
 
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
 
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"
 
    'Usa autenticação integrada.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
    'Abre a conexão.
    cnPubs.Open strConn
 
    ' Cria o objeto Recordset.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
 
    With rsPubs
        ' Associa a conexão.
        .ActiveConnection = cnPubs
        ' Extrai os dados.
        .Open "SELECT * FROM Authors"
        ' Coloca os dados na planilha.
        Plan1.Range("A1").CopyFromRecordset rsPubs
 
        ' Fecha a transação
        .Close
    End With
 
    ' Fecha conexão
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
 
End Sub

Como importar dados de Microsoft SQL Server em Microsoft Excel

Apenas para constar, segue o código fonte pronto para usar:Bom proveito!

Tomás Vásquez

Parametrizando a importação de dados no Excel

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