VBA – Criando uma cópia somente dados de uma planilha no Excel

Mais uma que saiu do nosso fórum.

O pessoal precisou de uma macro no VBA para criar uma cópia dos dados da planilha (Worksheet) em uma nova, mas somente os dados, sem fórmulas. Como alguns devem saber, isso é simples usando o recurso Colar Especial->Valores. Mas como o mundo é exigente, precisamos fazer isso a “rodo”, e é nesse caso que as macros ajudam.

A macro abaixo faz uma cópia da planilha atual, copiando somente seus dados:

Sub CopiaSomenteConteudo()
    Dim NewSheet As Worksheet, CurrentSheet As Worksheet
    'pega a planilha atual
    Set CurrentSheet = ActiveSheet
    'cria uma nova planilha
    Set NewSheet = ThisWorkbook.Worksheets.Add
    NewSheet.Name = CurrentSheet.Name & "2"
    'copia todas as células da planilha ativa
    CurrentSheet.Cells.Copy
    'cola só os valores na nova planilha
    NewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'zera os objetos
    Set NewSheet = Nothing
    Set CurrenSheet = Nothing
End Sub

Simples, rápido, eficaz. Isso é Excel + VBA.

Bom proveito!

Excel – Separando Nome e SobreNome

Um problema mais recorrente do que se deseja, e de solução até simples com as funções do Excel. Vamos imaginar a seguinte lista de nomes que você tenha recebido:

Excel Nome SobreNome

Ótimo. A missão então é separar o nome e o sobrenome das células na coluna A. Para isso, precisamos de algumas fórmulas. Para o nome, precisaremos da ESQUERDA e a PROCURAR.

A função ESQUERDA obtém o texto à esquerda de um determinado texto com base no número informado. São dois argumentos para esta função, o sendo o primeiro o texto e o segundo a quantidade de caracteres. Por exemplo, se usarmos a função da seguinte forma:

=ESQUERDA(“Excel”;2)

O resultado será “Ex”, pois pedimos os dois caracteres à esquerda do texto “Excel”, ou seja, as duas primeiras letras.

A segunda função, a PROCURAR, retorna o número que aponta para a ocorrência de um texto que procuramos. Ela recebe 3 argumentos, mas vamos nos preocupar apenas com 2. O primeiro é o texto que procuramos, o segundo é o texto onde procuramos. O seguinte exemplo:

=PROCURAR(“ce”;”Excel”)

Retorna 3, ou seja, pedimos a função que procure o texto “ce” dentro do texto “Excel” e me diga onde ele começa, se encontra-lo. No caso, como o texto começa na 3ª letra, o mesmo é o resultado.

Tendo essas duas funções em mãos, colocando a seguinte função na célula B2 da nossa planilha de exemplo:

=ESQUERDA(A2;PROCURAR(” “;A2))

Temos o seguinte resultado:

Excel Nome SobreNome

O que dissemos ao Excel nesta fórmula foi, procure no texto da célula A2 (fórmula PROCURAR) o caractere espaço em branco (” “). O resultado disso, é passado a função ESQUERDA, que usa esse número para pegar todo o texto a partir da esquerda até onde a função PROCURAR encontrou o 1º espaço em branco, ou seja, o primeiro nome.

Perfeito. Faça o autocompletar até a última célula para ver a fórmula funcionando para os outros nomes.

Agora, o SobreNome.

Neste caso, precisaremos da mesma coisa da primeira, porém, ao invés de usar a função ESQUERDA, usaremos a função DIREITA, que faz exatamente o mesmo, porém, procura o texto a partir da extremidade direita, ou seja, do fim do texto. Vamos pedir uma ajuda a função NÚM.CARACT, que conta o número de caracteres de um texto.  Neste caso, deixarei a função falar por si:

=DIREITA(A2;NÚM.CARACT(A2) – NÚM.CARACT(B2))

Estamos dizendo ao Excel, retorne para mim os caracteres a direita do texto, cuja quantidade é o número de caracteres do nome completo, menos o número de caracteres do primeiro nome, ou seja, o que sobra, o sobrenome:

Excel Nome SobreNome

É só autocompletar.

Bom proveito!

VBA – Macro para exportar dados em XML no Excel

A partir da versão 2002 (XP), o Excel, bem como todos os programas do Office ganharam uma séria de artimanhas para manipular, importar, exportar e trabalhar no padrão XML.

Isso é bom e poderoso, mas tentando fazer uma exportação simples de uma planilha em um arquivo XML, naveguei pelas opções que o Excel oferecia e no final, acabei optando por fazer a exportação por VBA, já que precisava de algo rápido do “limpo”.

Não usei classes ou bibliotecas específicas para o trabalho. A criação do XML foi na mão mesmo. Abaixo segue o código que faz o trabalho:

Sub ExportToXml()
    Dim linha As Long, coluna As Long, colunas As Long
    linha = 2
    colunas = ActiveSheet.UsedRange.Columns.Count
    Set fs = CreateObject("Scripting.FileSystemObject")
 
    Set a = fs.CreateTextFile(ThisWorkbook.FullName & ".xml", True)
    'cria as primeiras linhas
    a.WriteLine ("<?xml version=""1.0"" encoding=""UTF-8""?>")
    a.WriteLine ("<" & ActiveSheet.Name & "s>")
 
    With ActiveSheet
        Do While Not IsEmpty(.Cells(linha, 1))
            a.WriteLine (Chr(9) & "<" & ActiveSheet.Name & ">")
 
            For coluna = 1 To colunas Step 1
                a.Write (Chr(9) & Chr(9) & _
                "<" & .Cells(1, coluna).Value & ">" & _
                RTrim(.Cells(linha, coluna).Value) & _
                "</" & .Cells(1, coluna).Value & ">" & Chr(13))
            Next
 
            a.WriteLine (Chr(9) & "</" & ActiveSheet.Name & ">")
            linha = linha + 1
        Loop
    End With
 
    'finaliza o arquivo
    a.WriteLine ("</" & ActiveSheet.Name & "s>")
    a.Close
 
    MsgBox "Finito!"
End Sub

Em detalhes, a macro navega a partir da segunda linha (a primeira deve ter os cabeçalhos ok?) e constrói o XML, sendo que os nós considerarão o nome da planilha, pluralizando-a, por exemplo, se o nome da planilha for Usuario, ele criará o nó Usuarios e cada elemento/linha como Usuario. O arquivo gerado tem o mesmo nome da planilha, mas com a extensão XML. De quebra, o XML vai indentado.

Uma mão na roda para exportação de dados limpos. Faça o teste e, bom proveito.

Excel – Opções de inicialização

O Microsof Excel oferece, além da sua forma tradicional de inicialização, algumas opções para ajudar a acelerar seu trabalho, dependendo da sua rotina e a da sua equipe.

É possível através de configurações de linha de comando, altera como o Excel é iniciado, por exemplo, abrindo um determinado arquivo, somente leitura, ou não abrir coisa alguma. A parte boa é que não preciso gravar como isso é feito. Basta colocar a linha de comando em um atalho padrão do Windows e alterar conforme a necessidade.

Vamos a um exemplo para tornar mais claro. Supondo que você não queira que o Excel abra sempre uma planilha em branco toda vez que você o abre. Ao invés disso, vamos configurar para que sempre que o Excel for aberto por este atalho, ele abra vazio.

  • Crie um novo atalho na área de trabalho (botão direito do mouse, Novo->Atalho)
  • Na janela que abrir (Criar Atalho), coloque o caminho do Excel instalado no seu computador. Normalmente o caminho “C:\Arquivos de programas\Microsoft Office\Office12\EXCEL.EXE” a seguir serve para qualquer instalação do Excel 2007 (versão 12):
  • Clique em Avançar e dê o nome que quiser ao seu atalho. No meu caso, coloque Excel Vazio.

Pronto, temos um atalho padrão do Excel em nossa área de trabalho. Para fazer com que o Excel abra vazio, edite o atalho (clique com o botão direito sobre o atalho e clique em Propriedades). Na valor do Destino que é exatamente o atalho que configuramos e adicione ao final do caminho, o valor “/e”, colocando um espaço entre os textos, com mostra a figura:

Clique em OK para confirmar a alteração. Agora, abra o Excel a partir deste atalho e veja o que acontece. O Excel é aberto, porém sem nenhuma planilha aberta.

Outras opções de inicialização são que podem adicionadas são:

  • caminho do arquivo para abrir o Excel diretamente em um determinado arquivo
  • /r caminho do arquivo para abrir um arquivo específico em modo somente leitura
  • /p caminho da pasta para abrir em um diretório de trabalho (working folder) específico
  • /s para abrir em modo seguro

São mais algumas opções que o Excel oferece para acelerar seus trabalhos.

Bom proveito!

Referências

http://blogs.techrepublic.com.com/msoffice/?p=676

http://office.microsoft.com/en-gb/excel-help/command-line-switches-for-excel-HA010158030.aspx