Capturar o nome do usuário via VBA

Muito simples e útil, principalmente quando se precisa de algum tipo diferenciado de acesso conforme o usuário:

Dim usuario As String
usuario = VBA.Environ("username")
MsgBox usuario


;-)

Minha opinião sobre o Office 2007

Depois de 3 meses trabalhando de segunda a sexta com a dupla "Windows Vista + Office 2007", cheguei as seguintes conclusões:
- É tempo suficiente para formar uma opinião sobre os produtos;
- O Windows Vista é um "comilão" de recursos do hardware sem nada dar em troca do que consome, ou seja, é preciso uma máquina de ponta para ter o Vista e tudo o que você vai ter em troca são apenas alguns recursos visuais "mais bonitinhos". Nada mais;
- O Office 2007 também tem um visual muito mais atraente que a versão anterior, mas para usuários comuns, leia-se "no máximo intermediários", oferece muito pouco pelo que consome;
- A mudança do tradicional menu + barra de ferramentas para a faixa de opções, foi uma mudança infeliz que só dificulta a vida do usuário. E não digam que eu não tentei: personalizei a faixa de opções ao máximo para me adaptar e mesmo assim, depois de 3 meses de uso direto, ainda preciso procurar determinados comandos/recursos;
- Já comentei em algum post no passado que, para a maior parte dos usuários, ainda hoje o Office 97 seria mais que suficiente para o dia a dia e continuo com a mesma opinião. Para que migrar da versão em uso, seja ela 2002 (XP), 2003 ou 2000, para a versão 2007 se você não precisa dos recursos que a nova versão lhe oferece? Por exemplo, é muito bom ter 1 milhão de linhas no Excel, mas quem usa tudo isso?
- Sinceramente, esperava mais do Access 2007 em termos de capacidade e confiabilidade. Meus bancos de dados atuais corrompem com uma frequencia maior do que os criados com o Access 2003 e continuo limitado a 2 Gb;

Resumindo:
A melhor combinação para TRABALHO ainda é o Windows XP + Office 2003.
Palavra de quem usa Vista + Office 2007 todos os dias e não de quem usou algumas vezes para formar a opinião.

Abs!

Criando uma pausa na execução do código

Código muito simples, dispensa maiores explicações.
Já vi gente fazendo um "For - Next" sem nada dentro do looping (até funciona...) mas esta é uma solução mais "elegante"!

Public Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Public Sub Pausa(ByVal segundos As Single)
     Call Sleep(Int(segundos * 1000#))
End Sub

Fechando um processo no TaskManager

A automação no Office nos permite controlar outros aplicativos, mas existe um pequeno "problema" já comentado aqui em um post anterior: mesmo finalizando o aplicativo controlado com "Application.quit" ou limpando a váriavel com "Set var_aplicativo = nothing", o processo continua ativo no gerenciador de tarefas do Windows (TaskManager), o que por vezes pode resultar em erro quando se executa o código duas vezes.
Não sei se isso é um bug ou uma caracterítica do recurso, mas vou deixar aqui um código que elimina o problema.
Obs.: o código não foi escrito por mim, consegui-o nas inúmeras pesquisas pela internet e estou apenas repassando aqui. Já utilizei-o por várias vezes, sempre com sucesso.

'Para usar - exemplo:
'Call Closeprocess("Excel.exe")

================================

Option Explicit

Public Const TH32CS_SNAPPROCESS As Long = 2&
Public Const MAX_PATH As Long = 260

Public Type PROCESSENTRY32
   dwSize As Long
   cntUsage As Long
   th32ProcessID As Long
   th32DefaultHeapID As Long
   th32ModuleID As Long
   cntThreads As Long
   th32ParentProcessID As Long
   pcPriClassBase As Long
   dwFlags As Long
   szExeFile As String * MAX_PATH
End Type

Public Const PROCESS_ALL_ACCESS As Long = &H1F0FFF

Public Declare Function CreateToolhelp32Snapshot Lib "kernel32" _
  (ByVal lFlags As Long, ByVal lProcessID As Long) As Long
Public Declare Function ProcessFirst Lib "kernel32" _
   Alias "Process32First" _
  (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Public Declare Function ProcessNext Lib "kernel32" _
   Alias "Process32Next" _
  (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Public Declare Function OpenProcess Lib "kernel32" _
  (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
   ByVal dwProcessId As Long) As Long
Public Declare Function TerminateProcess Lib "kernel32" _
  (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
Public Declare Sub CloseHandle Lib "kernel32" _
  (ByVal hPass As Long)

Public Function CloseProcess(EXEName As String) As Boolean

  Dim hSnapShot  As Long
  Dim uProcess   As PROCESSENTRY32
  Dim hProcess   As Long

  CloseProcess = False
  hSnapShot = CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0&)
  If hSnapShot = -1 Then Exit Function

  uProcess.dwSize = Len(uProcess)
  If ProcessFirst(hSnapShot, uProcess) = 1 Then
     Do
        If LCase$(Left$(uProcess.szExeFile, InStr(1, uProcess.szExeFile, vbNullChar) - 1)) _
           = LCase$(EXEName) Then
           hProcess = OpenProcess(PROCESS_ALL_ACCESS, False, uProcess.th32ProcessID)
           CloseProcess = TerminateProcess(hProcess, ByVal 0&) > 0
           Exit Do
        End If
     Loop While ProcessNext(hSnapShot, uProcess)
  End If

  Call CloseHandle(hSnapShot)

End Function

A partir de uma planilha, ler dados de outra via ADO

Arquivos XLS não são multiusuários por natureza.
O Excel até permite o compartilhamento, via Ferramentas > Compartilhar Pasta de Trabalho, mas são tantos detalhes que precisam ser observados que o usuário precisa se adaptar ao recurso, sendo que na verdade o recurso deveria facilitar a vida dele.
Recentemente precisei fazer um sistema onde ocorria a leitura de dados de vários XLS's para gravar num outro XLS e este por sua vez, poderia estar aberto no momento da gravação.
Depois de pesquisar, escrever muido código e testar bastante, cheguei a conclusão de que a melhor forma era utilizar o Active Data Objects.
Segue um exemplo de código para leitura de dados de um arquivo XLS a partir de outro.
O melhor de tudo é que podemos utilizar a sintaxe SQL nos comandos, com algumas poucas adaptações é claro, que fará com que os desenvolvedores de banco de dados se sintam bem à vontade.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ssql As String
Sub teste()
    'cria uma conexão ADO
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    'Na string de conexão, o caracter "aspas duplas" é substituído pelo
    'correspondente em ASCII -> Chr(34)
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Planilha.xls" & _
    ";Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes" & Chr(34)

    'abre o recordset pelo nome da planilha
    'Obs.:
    '- No comando SELECT o nome da planilha vai entre chaves e 
    'com um "$" no final do nome
    
    Set rst = cnn.Execute("Select * from [plan1$]")
    
    MsgBox rst(0)
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing

End Sub

Importação de arquivos CSV via ADO

Depois que aprendi a trabalhar com o ADO(Active Data Objects), vi que trata-se de um recurso muito flexível e principalmente, fácil de utilizar.
Postarei em breve vários exemplos de utilização do ADO no VBA.
Esta é a dica para importar arquivos texto com os campos separados por vírgula (CSV - Comma Separated Values). Basta copiar o código e ajustar o caminho/nome do arquivo csv:

Option Explicit

Private Sub importa()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim adcomm As New ADODB.Command
    Dim caminho As String
    Dim linha As Integer
    Dim coluna As Integer
   
    caminho = "D:\paulo\Projetos VB\ADO Arquivo Texto\"
   
    'Conexão para arquivo texto sem cabeçalho:
    'connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    '& caminho & ";Extended Properties='text;HDR=NO;FMT=Delimited'"
   
    'Conexão para arquivo texto com cabeçalho e colunas
    cnn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
    & caminho & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"
       
    rst.Open "Select * From TesteDeDados.txt", cnn, adOpenStatic, adLockReadOnly, adCmdText
   
    linha = 1
    coluna = 1
    Do While Not rst.EOF
        Cells(linha, coluna).Value = rst(0)
        Cells(linha, coluna + 1).Value = rst(1)
        rst.MoveNext
        linha = linha + 1
    Loop
   
    cnn.Close
    Set cnn = Nothing


End Sub

Integração entre os aplicativos do Office

Semana passada, precisei desenvolver um programa para enviar e-mails para mais ou menos 350 destinatários, anexando a cada um deles um arquivo XLS.
Até aí, nada demais, muita gente já fez isso e achei que não seria difícil para mim também.
Depois de algumas horas escrevendo o código no Excel, deparei com alguns problemas já conhecidos da automação, como por exemplo, o fato de não poder ter outra instância do Excel aberta durante a execução do código.
Quem já fez, sabe que "set objeto_Excel = nothing" não "mata" o processo no taskmanager...
Enviar e-mails pelo Access geraria outros problemas (imagino eu...) que logo descartei essa opção.
Enfim, resolvi combinar os aplicativos e partir para a solução mais simples:
- Geração das planilhas que iriam anexas nos e-mails: usei o Access para exportar os dados no formato XLS;
- Usei o VBA no Outlook para obter os e-mails dos destinatários numa tabela do Access via ADO.
Moleza...
350 e-mails enviados em 5 minutos (o nosso servidor Exchange é muito rápido e colaborou).

Consultem o help do VBA no Outlook para ver como é simples.
Qualquer dúvida, entrem em contato.

abs!

;-)

Montagem de cabo cross-over

Problemas de inicialização - Windows XP

Esta dica é do grande amigo Sames Davis e vou deixar registrado para não ficar procurando toda hora no Google...



A inicialização do Windows necessita da MBR que é a sigla para Master Boot Record, ou Registro Mestre de Boot.
O MBR fica no setor 0 do HD e armazena as descrições das partições e informa o BIOS durante a inicialização ( Boot ).

Sem MBR, nada de partições do disco rígido e se ela estiver corrompida, o Windows não irá funcionar.

Algumas causas em um caso de corrupção da MBR:

* Vírus;
* Desligamento direto no estabilizador;
* Pico de energia;
* Falta de energia;
* HD danificado

Segue um pequeno passo a passo de como tentar corrigir:
1. Dê boot com o cd do Windows;
2. Tecle R ( reparação ) para entrar no console de recuperação;
3. Se for solicitado em qual sistema deseja se conectar, escolha a opção do Windows;
4. Irá aparecer uma tela preta ( DOS ) , digite os seguintes comandos para corrigir
5. Digite FIXBOOT e confirme com Y
6. Digite FIXMBR e confirme com Y
7. Digite EXIT para reiniciar o micro
8. Se o problema ainda continuar, entre novamente no console de recuperação
9. Desta vez, além do FIXBOOT e FIXMBR, digite CHKDSK C: /P /R
10. Espere +/- 20 minutos para que seja feita a correção ( esse tempo é para HD de 80GB )
11. Caso o problema ainda persista, tente salvar os dados usando um cd do Linux ou Hiren's CD
12. Caso não seja possível acessar o HD, a solução será reinstalar o Windows.
Sames Davis

Dica de livro

Tempos atrás, eu emprestei este livro de um amigo para dar uma olhada e achei que era muito complexo para um principiante como eu.
Hoje com um pouco mais de experiência, vejo o livro com outros olhos e recomendo para quem quer se aprofundar no VBA do Excel e também para tê-lo como fonte de consulta.

Listar arquivos de uma pasta

Já era tempo de começar a postar dicas de VBA...
"Abrindo" a nova fase, segue um código que eu uso para listar e catalogar meus mp3's.
Código bem simples e com alguns comentários para facilitar o entendimento:

Sub lista_arquivos()
    Dim pasta As String
    Dim linha As Integer
    Dim arquivo As String
    linha = 1
        
    'Pega o caminho completo da pasta
    pasta = InputBox("Digite o caminho da pasta")

    'Cabeçalho
    Cells.ClearContents
    Cells(linha, 1) = "Nome do Arquivo"
    Cells(linha, 2) = "Tamanho"
    Cells(linha, 3) = "Data/Hora"
    Range("A1:C1").Font.Bold = True
    linha = linha + 1

    'Lista o primeiro arquivo da pasta
    'arquivo = Dir(pasta, 7)
    arquivo = Dir(pasta, 7)
    Cells(linha, 1) = arquivo
    Cells(linha, 2) = FileLen(pasta & arquivo)
    Cells(linha, 3) = FileDateTime(pasta & arquivo)

    'Lista os arquivos restantes
    Do While arquivo <> ""
        arquivo = Dir
        If arquivo <> "" Then
            linha = linha + 1
            Cells(linha, 1) = arquivo
            Cells(linha, 2) = FileLen(pasta & arquivo)
            Cells(linha, 3) = FileDateTime(pasta & arquivo)
        End If
    Loop
End Sub

Substituir o asterisco numa planilha

Esta pergunta foi feita num dos grupos de discussão que participo e vou deixar registrado aqui.

Ao substituir um determinado caracter, basta clicar em Editar > Substituir, preencher os campos na caixa de diálogo que se abre e em seguida executar.

O "problema" é quando se precisa substituir um caracter "curinga" como o asterisco. Nesses casos, o Excel entende que é para substituir tudo (*) e o resultado não sai como desejado.

Para essas situações particulares, basta colocar um til (~) antes do asterisco.

Veja na figura.

:-)

VBA - Como encontrar a última linha preenchida nuna coluna?

Esta é mais uma daquelas que tem mil e uma maneiras de fazer, vou mostrar apenas uma delas.
Quem programa em VBA, principalmente nos casos de inserção de dados, sempre precisa saber onde está o último dado da coluna para gravar o novo valor na célula logo abaixo.
Como descobrir qual é a última célula preenchida de uma coluna?
Veja a função abaixo:

Function ultima_linha()
Dim linha As Long
linha = 2
Do While linha <> 0
linha = linha + 1
Loop
ultima_linha = linha
MsgBox ultima_linha
End Function

"Trocando em miúdos"...
Enquanto o tamanho do texto da célula for maior que zero (o que significa que a célula está preenchida com algum valor), incremente 1 no contador (que se chama "linha") e execute o comparativo na próxima linha.
Como inicia em 2, no momento em que o tamanho do texto da célula for igual a zero, o procedimento é encerrado e a linha em branco é a que o contador indicar.

:-)

Comparando planilhas

É comum comparar planilhas cujos cabeçalhos de linha ou coluna são iguais, mas os dados referem-se a períodos diferentes.

Uma forma rápida de se fazer isso, é abrir as duas planilhas e usar CTRL + TAB se estiverem em pastas diferentes ou CTRL + PgDown/Up se estiverem na mesma pasta.

Para facilitar ainda mais, o Excel oferece o recurso de comparar lado a lado no menu "Janela".

A opção "rolagem sincronizada" torna a tarefa ainda mais fácil e rápida.

Por onde já passei e trabalhei...

Números ordinais - cultura inútil ou não???

Estava pensando, quando chegasse à publicação da 200ª dica, como escrever ou ler "200ª"?
Segue abaixo:

1º = primeiro
2º = segundo
3º = terceiro
4º = quarto
5º = quinto
6º = sexto
7º = sétimo
8º = oitavo
9º = nono
10º = décimo
20º = vigésimo
30º = trigésimo
40º = quadragésimo
50º = qüinquagésimo
60º = sexagésimo
70º = septuagésimo
80º = octogésimo
90º = nonagésimo
100º = centésimo
200º = ducentésimo
300º = trecentésimo
400º = quadringentésimo
500º = qüingentésimo
600º = seiscentésimo ou sexcentésimo
700º = septingentésimo
800º = octingentésimo
900º = nongentésimo ou noningentésimo
1000º = milésimo

de 2000 em diante, lê-se o dois milésimo ou o segundo milésimo e assim por diante.

Assim, seu eu estiver vivo ainda na 3367ª dica, poderei dizer tranquilamente:

"Terceiro milésimo, trecentésima sexagésima sétima dica!"...

Inútil?
Que tal criar uma função para retornar o ordianal a partir do cardinal?
Mais inútil ainda?
Alguma utilidade deve ter!

;-)

Soma rápida

Não sei já se postei esta dica, mas se não... ai vai:

Para obter rapidamente a soma de um intervalo, basta selecioná-lo e conferir o valor na barra de status.

Outras opções estão disponíveis, clique com o botão direito sobre mesma barra de status para conferir o que está disponível.

O bom é que, mesmo aplicando o Auto Filtro, os valores são calculados somente sobre os dados que estão visíveis.

;-)

Definindo o caminho (VB/VBA)

Para evitar a alteração do caminho de um arquivo dentro de um código toda vez que o aplicativo muda de pasta, basta usar um recurso muito útil do VB ou do VBA: o Path.
No Excel: ThisWorkbook.Path
No Access: CurrentProject.Path
No Word: ThisDocument.Path
No VB6: App.Path

Assim, pode-se mudar o aplicativo de pasta sem ter de alterar o código em função dessa mudança.

;-)

Copiar rapidamente planilhas dentro da mesma pasta

Antes de mais nada, vamos esclarecer uma coisa: uma PASTA contém várias PLANILHAS. A maioria das pessoas utiliza o termo PLANILHA para se referenciar à uma PASTA. Isto era verdadeiro na época das primeiras planilhas que trabalhavam uma planilha por arquivo. Como atualmente todos os programas já manipulam tranqüilamente várias planilhas no mesmo arquivo, é bom ter o conceito bem fixado na mente para não fazer confusão na hora de trabalhar.Bem, vamos então à dica.

Para copiar uma planilha dentro da mesma pasta, o procedimento usual é clicar com o botão direito em cima da planilha, selecionar "Mover ou Copiar", selecionar o local onde ela vai ficar, marcar a opção "Criar uma cópia" e depois clicar em OK.

A operação é simples quando se precisa copiar uma ou duas planilhas, mas à medida em que a quantidade de planilhas vai aumentando, a demora e o aumento dos cliques no mouse aumentam proprocionalmente.

Aí é que entra a tecla CTRL (Control) para facilitar a vida do usuário.

Basta mantê-la pressionada enquanto se arrasta a planilha de lugar como se fosse movê-la e uma cópia será criada. Depois é só renomear. Muito útil quando se controla coisas do tipo "uma planilha para cada dia do mês" ou "uma planilha para cada mês do ano".

Volto a repetir, quanto mais agilidade o usuário tiver em operações simples, mais tempo ele terá para focar a análise dos dados, o que na minha opinião é muito mais importante.

;-)

Cursos multimídia de Excel

Para quem gosta de aprender em casa, com tranquilidade e nas horas vagas, algumas sugestões:

http://www.totaltraining.com/prod/microsoft/excel2007_ess.asp

http://movielibrary.lynda.com/html/modListing.asp?pid=144

http://www.001shop.com.br/lojas/editoraneo.com.br/produtos.asp?produto=69&categoria=10&inf=10&a1=Office&a2=%3A%2E+Office+2000

http://www.anasoft.com.br/product_info.php?cPath=25&products_id=44

Os dois primeiros em inglês e os dois seguintes em português, todos boas opções para o Excel e também para outros aplicativos.

;-)